VBA Slow Copy Paste from Csv - Faster way to import? [Current Code Included]

[LNG2013]

Hello,

I have a VBA report I wrote. The report starts by Opening 2x CSV files Goal.csv (18MB) and Objective.csv (40MB) and copying their sheet contents to corresponding sheets in the report. The fastest now I can get the report to open is about 1 min. The open,copy, and paste process is taking up the longest length of the code - approximately 45 sec all together.

Is there a faster way to import this data into the report?

Code Below

Private Sub Workbook_Open()

'~~~ 1)
'~~~ The Following closes any worksheets that may have been left open.
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual ' 10-8-2019 Added to Speed up opening of code - This is the turn off 
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
    Select Case ws.Name
        Case "Objective5m", "Goal5m"
        Case Else
            ws.Delete
    End Select
Next ws
Application.DisplayAlerts = True


'~~~ 2)
'~~~ This code resets the autofilter


Sheets("Goal5m").Select
If Range("A2").Value <> "" Then
With ActiveSheet
    .AutoFilterMode = False
End With
    End If
    
    
Sheets("Objective5m").Select
If Range("A2").Value <> "" Then
With ActiveSheet
    .AutoFilterMode = False
End With
    End If

'~~~ 3)A
' ~~~ This is where the data for GOALS is pulled into the current workbook, which is used to generate the reports.
    Dim wbgoal As Workbook
    Set wbgoal = Workbooks.Open(Filename:=ThisWorkbook.Path & "\Data\Goal5m.csv")
    Application.Windows("Goal5m.csv").Activate 'wbgoal.Activate
    wbgoal.ActiveSheet.UsedRange.Copy
    ThisWorkbook.Activate
    Sheets("Goal5m").Select
    Cells.Select
    ActiveCell.PasteSpecial xlPasteAll
    Application.CutCopyMode = False
   Workbooks("Goal5m.csv").Close SaveChanges:=False
    
'Sheets("Objective5m").Select
Application.DisplayAlerts = True

' Added in 9/2019 - This code will delete all columns that contain a specific text in their header.
' In this case it is deleting any Columns that contain the header of *del
        ThisWorkbook.Activate
    Sheets("Goal5m").Select
    Dim A As Range
    
    Do
        Set A = Rows(1).Find(What:="*del*", LookIn:=xlValues, LookAt:=xlPart)
        If A Is Nothing Then Exit Do
        A.EntireColumn.Delete
    Loop

'~~~ 3)B
' ~~~ This is where the data for OBJECTIVES is pulled into the current workbook, which is used to generate the reports.

    Dim wbobj As Workbook
    Set wbobj = Workbooks.Open(Filename:=ThisWorkbook.Path & "\Data\Objective5m.csv")
    Application.Windows("Objective5m.csv").Activate 'wbobj.Activate
    wbobj.ActiveSheet.UsedRange.Copy
    ThisWorkbook.Activate
    Sheets("Objective5m").Select
    Cells.Select
    ActiveCell.PasteSpecial xlPasteAll
    Application.CutCopyMode = False
   Workbooks("Objective5m.csv").Close SaveChanges:=False
    
Sheets("Objective5m").Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationAutomatic ' 10-8-2019 Added to Speed up opening of code - This is the turn off

 

[baitmaster]

Yes, but I don't have the code handy to help you

Every interaction with your Excel file will slow you down, as will the acts of opening and closing source files. The more work we can do virtually the better, so we aim to interact with Excel only once, or twice in this case since there are multiple things to paste

What I would aim to do, which you will find much faster, is to use your source data as a database. This would entail creating an ADODB connection to a the source file (usually you would use a database as your source but you can use properly-constructed .xls*, .csv or even .txt). You then ask a basic SQL query, which allows you to select specific columns and apply any filters you want in order to shrink your data, and finally paste your resultant recordset directly into the worksheet

This will be much quicker, but probably requires you to learn some new concepts. It may even be possible to merge your data within the SQL query meaning you only have to paste one table of results


If you don't want to go down this route, you can at least speed things slightly by not deleting each unwanted column individually, but create a set of columns and the delete them all at once