If you're experiencing slow performance with VBA when pasting data directly into a new Excel table and finding that saving the file first speeds things up, there are a few strategies you can employ to improve the speed without having to save the file first. Here are some tips to optimize the data export process in VBA:

1. Optimize VBA Code:
   Turn Off Screen Updating: Disabling screen updating can significantly improve performance. Use Application.ScreenUpdating = False at the start of your macro and set it back to True at the end.
   vba
   Copy code
   Application.ScreenUpdating = False
   ' Your code to paste data
   Application.ScreenUpdating = True
   Turn Off Automatic Calculations: If your Excel file contains formulas, set calculation to manual before running your macro and set it back to automatic afterward.
   vba
   Copy code
   Application.Calculation = xlCalculationManual
   ' Your code to paste data
   Application.Calculation = xlCalculationAutomatic
   Disable Events: Temporarily disable workbook and worksheet events to prevent unnecessary processing during data operations.
   vba
   Copy code
   Application.EnableEvents = False
   ' Your code to paste data
   Application.EnableEvents = True
2. Use Efficient Methods for Data Transfer:
   Array-Based Transfer: Instead of pasting data cell by cell, use an array to handle the data. This method is faster because it reduces the number of interactions between VBA and Excel.
   vba
   Copy code
   Dim dataArray As Variant
   Dim wsSource As Worksheet
   Dim wsDest As Worksheet

Set wsSource = ThisWorkbook.Sheets("SourceSheet")
Set wsDest = ThisWorkbook.Sheets("DestSheet")

' Load data into array
dataArray = wsSource.Range("A1:D1000").Value

' Paste data from array to destination
wsDest.Range("A1").Resize(UBound(dataArray, 1), UBound(dataArray, 2)).Value = dataArray
3. Use Excel's Built-In Features:
Query Tables or Power Query: If you're importing data from SQL, consider using Excel's Query Tables or Power Query. These tools are optimized for handling data imports efficiently and can avoid the overhead of VBA.
4. Work with Temporary Data:
Use a Temporary Sheet: Create a temporary worksheet to handle the data transfer and then move the data to the final destination. This can help in cases where direct data pasting causes performance issues.
vba
Copy code
Dim tempSheet As Worksheet

' Create a temporary worksheet
Set tempSheet = ThisWorkbook.Worksheets.Add

' Paste data into the temporary sheet
wsSource.Range("A1:D1000").Copy Destination:=tempSheet.Range("A1")

' Move data from the temporary sheet to the destination
tempSheet.Range("A1:D1000").Copy Destination:=wsDest.Range("A1")

' Delete the temporary sheet
Application.DisplayAlerts = False
tempSheet.Delete
Application.DisplayAlerts = True
5. Avoid Unnecessary Formatting:
Minimize Formatting: If you're applying formatting while copying data, try to avoid it or apply it after the data is pasted.
By applying these strategies, you should be able to improve the speed of data transfer without the need to save the file first. If you're still facing issues, consider providing more specific details about the VBA code and data you're working with for further assistance.

我用EPPLUS+C#（iis上跑），輸出上萬行資料約不到30秒（包括SQL查詢及整併）