Importing multiple CSV files into multiple worksheets in Excel using VBA

Question

I am working to create a VBA/macro that imports 2 CSV files from a specific folder into 2 worksheets in an Excel template that I have created.

To be more specific, these files are created and saved as new workbooks on a daily basis (two new files being added into the folder everyday) so my problem is how to code my macro to always import the 2 latest files?

Please see below the code from which I manually select and import the latest files using macro. However, re-running the macro does not work as it shows "run-time error '5' - invalid procedure call or argument". Your help would be much appreciated.

Sub Macro1()
'
' Macro1 Macro
' IMPORT CSV FILES
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;P:\APS\Reports_From_PDP\AP_PDP_VehicleLoad_Report_AM 19-01-2018 3-15-03 AM.csv" _
        , Destination:=Range("$A$1"))
        .CommandType = 0
        .Name = "AP_PDP_VehicleLoad_Report_AM 19-01-2018 3-15-03 AM"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Sheets.Add After:=ActiveSheet
    Application.CutCopyMode = False
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;P:\APS\Reports_From_PDP\AP_PDP_VehicleLoad_Report_PM 19-01-2018 7-15-02 PM.csv" _
        , Destination:=Range("$A$1"))
        .CommandType = 0
        .Name = "AP_PDP_VehicleLoad_Report_PM 19-01-2018 7-15-02 PM"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Sheets("Sheet1").Select
    Columns("A:N").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A1").Select
    Sheets("Sheet2").Select
    Columns("A:N").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A1").Select
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "PM"
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "AM"
    Sheets("AM").Select
End Sub

please move your code into the body of your question by editing your original question. — Alan
– Alan, Commented Feb 5, 2018 at 1:24
Hi Alan, I have added the code back to the original query. sorry for that. — Marvin Nguyen
– Marvin Nguyen, Commented Feb 5, 2018 at 2:45
Much better. I don't have an answer for you, I was just reviewing your post as a new poster. — Alan
– Alan, Commented Feb 5, 2018 at 3:14

AcsErno · Accepted Answer · 2018-02-23 07:53:40Z

1

You can find the latest file(s) this way:

EDIT: Dir return only the filename, so you need to append the path, too.

EDIT2: As per user request a few Debug.Print is inserted.

Sub main()
    Dim s1 as String, s2 as String

    s1 = LastFile("P:\APS\Reports_From_PDP\AP_PDP_VehicleLoad_Report_AM")
    Debug.Print "Last file1: " & s1
    s2 = LastFile("P:\APS\Reports_From_PDP\AP_PDP_VehicleLoad_Report_PM")
    Debug.Print "Last file2: " & s2
End Sub
Function LastFile(sName as String) as String
    Dim dLatest as Date
    Dim dFound as Date      ' date of one matching filename
    Dim sLatest as string   ' the latest file or ""
    Dim sFound as string    ' one matching filename
    Dim sPath as string

    dLatest = 0
    sLatest = vbnullstring
    sPath = Left$(sName,  InStrRev(sName, "\"))

    sFound = Dir(sName & "*.csv")
    Do While sFound <> vbnullstring
         Debug.Print "Found: " & sFound
         dFound = FileDateTime(sPath & sFound)
         If dFound > dLatest Then 
             dLatest = dFound
             sLatest = sFound
         Endif
         sFound = Dir
    Loop
    LastFile = sLatest
End Function

edited Feb 23, 2018 at 7:53

answered Feb 5, 2018 at 12:28

AcsErno

1,6451 gold badge9 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Marvin Nguyen Over a year ago

Hi AcsErno, many thanks for your time resolving my query. I have tried to insert this code in the macro and removed the formatting part just to see if it can pick up the latest files first. However it didn't quite work as there was an error of "Run-time error '53': File not found" . When I clicked on "Debug" button then the Module highlighted the following code "dFound = FileDateTime (sFound). Please kindly assist.

AcsErno Over a year ago

True, see my EDIT.

Marvin Nguyen Over a year ago

hi AcsErno, thanks so much for your help. However, this time nothing happens after I have created a new module and hit F5 (expecting this new code to import data perfectly... :( not really sure what the actual issue is...

AcsErno Over a year ago

What happens when you Debug.Print s1 ? Empty means no file is found at the specified path.

Marvin Nguyen Over a year ago

hi AcsErno, my apologies for the late reply. To be honest I am a newbie in this VBA/Macro field so not really sure how input "Debug.Print s1" in the above coding...

|

Collectives™ on Stack Overflow

Importing multiple CSV files into multiple worksheets in Excel using VBA

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related