Importing newest *.csv file into excel

D

DeekMan

New Member
Joined
Apr 20, 2019
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Good afternoon,

I have this code from a recorded macro, that imports the specific file into the open worksheet. (highlighted path and file)

Code: 
Sub importnewfile()

ActiveWorkbook.Queries.Add Name:="Derby_Station", _
        Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""[COLOR=#ff0000]C:\test\CSV\Derby_Station_Today_20190401024130.csv[/COLOR]""),[Delimiter="","", Columns=16, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Change Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type " & _
        "text}, {""Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type text}, {""Column11"", type text}, {""Column12"", type text}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Removed Top Rows"" = Table.Skip(" & _
        "#""Change Type"",4)" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Removed Top Rows"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Derby_Station"";Extended Properties=""" _
        , """"), Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "SELECT * FROM [Derby_Station]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Derby_Station"
        .Refresh BackgroundQuery:=False
    End With
    ActiveSheet.Name = "Derby_Station"

end sub

I would like to import the newest *.csv file from the specified folder, (C:\test\CSV\) rather than the specific file, is this possible please?

any help is appricated

DeeK
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
.

This macro will identify the latest .CSV file in the directory. Hopefully this will get you started.


Code: 
Option Explicit


Sub OpenLatestFile()


    'Declare the variables
    Dim MyPath As String
    Dim MyFile As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim LMD As Date
    
    'Specify the path to the folder
    MyPath = "[COLOR=#FF0000]C:\test\CSV\[/COLOR]"
    
    'Make sure that the path ends in a backslash
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    
    'Get the first Excel file from the folder
    MyFile = Dir(MyPath & "*.csv", vbNormal)
    
    'If no files were found, exit the sub
    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If
    
    'Loop through each Excel file in the folder
    Do While Len(MyFile) > 0
    
        'Assign the date/time of the current file to a variable
        LMD = FileDateTime(MyPath & MyFile)
        
        'If the date/time of the current file is greater than the latest
        'recorded date, assign its filename and date/time to variables
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
        
        'Get the next Excel file from the folder
        MyFile = Dir
        
    Loop
    
    'Open the latest file
   ' Workbooks.Open MyPath & LatestFile
    MsgBox "Most Recent File is :  " & LatestFile & vbNewLine & vbNewLine & "Last Saved on : " & LMD
End Sub
 
Upvote 0
Logit,

The Code you supplied works as you said it would, thank you. I can see the commented out 'Workbooks.Open MyPath & LatestFile I have tried this and the last fife opens.

and thats where my limit is reached, I have added what you have sent to the code I already have, and added the bit in red instead of the specific path and file, but whilst the first part still runs nicley, I get a run-time error 13, type mismatch. when I debug the whole of the initial block of code is highlighted. I am using excel 2019.

Code: 
ActiveWorkbook.Queries.Add Name:="Derby_Station", _
        Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""MyPath & LatestFile & " \ " *.csv""),[Delimiter="","", Columns=16, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Change Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type " & _
        "text}, {""Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type text}, {""Column11"", type text}, {""Column12"", type text}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Removed Top Rows"" = Table.Skip(" & _
        "#""Change Type"",4)" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Removed Top Rows"""



Code: 
Option Explicit


Sub OpenLatestFile11()


    'Declare the variables
    Dim MyPath As String
    Dim MyFile As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim LMD As Date
    
    'Specify the path to the folder
    MyPath = "C:\Users\me\Desktop\Derby_Simps\CSV_Files\"
    
    'Make sure that the path ends in a backslash
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    
    'Get the first Excel file from the folder
    MyFile = Dir(MyPath & "*.csv", vbNormal)
    
    'If no files were found, exit the sub
    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If
    
    'Loop through each Excel file in the folder
    Do While Len(MyFile) > 0
    
        'Assign the date/time of the current file to a variable
        LMD = FileDateTime(MyPath & MyFile)
        
        'If the date/time of the current file is greater than the latest
        'recorded date, assign its filename and date/time to variables
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
        
        'Get the next Excel file from the folder
        MyFile = Dir
        
    Loop
    
    'Open the latest file
   'Workbooks.Open MyPath & LatestFile
    MsgBox "Most Recent File is :  " & LatestFile & vbNewLine & vbNewLine & "Last Saved on : " & LMD

ActiveWorkbook.Queries.Add Name:="Derby_Station", _
        Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""[COLOR=#ff0000]MyPath & " \ "[/COLOR]*.csv""),[Delimiter="","", Columns=16, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Change Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type " & _
        "text}, {""Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type text}, {""Column11"", type text}, {""Column12"", type text}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Removed Top Rows"" = Table.Skip(" & _
        "#""Change Type"",4)" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Removed Top Rows"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Derby_Station"";Extended Properties=""" _
        , """"), Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "SELECT * FROM [Derby_Station]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Derby_Station"
        .Refresh BackgroundQuery:=False
    End With
    ActiveSheet.Name = "Derby_Station"

End Sub
 
Upvote 0
You would need MyPath & MyFile

Check to see if the extension is included with MyFile, else add it.

HTH
 
Upvote 0
.
Untested here but ...

Try deleting this part from the highlighted red : " \ " .

I believe the backslash has already been accounted for previously in the code.
 
Upvote 0
Hi,

The first part of the sub runs perfectly, but I am still struggleing with the second part. it is not importing the newest file. i have added the MyFile as suggested, I have tried adding &"", but in all situations i get the run-time error 1004. [Data.Format.error] the supplied file path must be a valid absolute path. I have also noticed that in the query tab on the worksheet, it is showing the same message. to me it is looking like VBA understands what MyPath & MyFile means but the query doesn't.

thanks in anticipation.

Code: 
Option Explicit


Sub OpenLatestFile11()


    'Declare the variables
    Dim MyPath As String
    Dim MyFile As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim LMD As Date
    
    'Specify the path to the folder
    MyPath = "C:\Users\me\Desktop\Derby_Simps\CSV_Files\"
    
    'Make sure that the path ends in a backslash
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    
    'Get the first Excel file from the folder
    MyFile = Dir(MyPath & "*.csv", vbNormal)
    
    'If no files were found, exit the sub
    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If
    
    'Loop through each Excel file in the folder
    Do While Len(MyFile) > 0
    
        'Assign the date/time of the current file to a variable
        LMD = FileDateTime(MyPath & MyFile)
        
        'If the date/time of the current file is greater than the latest
        'recorded date, assign its filename and date/time to variables
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
        
        'Get the next Excel file from the folder
        MyFile = Dir
        
    Loop
    
    'Open the latest file
   'Workbooks.Open MyPath & LatestFile
    MsgBox "Most Recent File is :  " & LatestFile & vbNewLine & vbNewLine & "Last Saved on : " & LMD

ActiveWorkbook.Queries.Add Name:="Derby_Station", _
        Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents[COLOR=#ff0000](""MyPath & MyFile"")[/COLOR],[Delimiter="","", Columns=16, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Change Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type " & _
        "text}, {""Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type text}, {""Column11"", type text}, {""Column12"", type text}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Removed Top Rows"" = Table.Skip(" & _
        "#""Change Type"",4)" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Removed Top Rows"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Derby_Station"";Extended Properties=""" _
        , """"), Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "SELECT * FROM [Derby_Station]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Derby_Station"
        .Refresh BackgroundQuery:=False
    End With
    ActiveSheet.Name = "Derby_Station"

End Sub
 
Upvote 0
What does the MSGBOX show you?

Also you cannot just use "" and a variable, you need to concatenate them
 
Last edited:
Upvote 0
Good Morning,

The message box gives you the details and name of the newest file in the given folder. I have tried all sorts of combinations. of MyFile & MyPath &" " & LastesFile but to no avail.

Am I trying to do the impossible?
 
Upvote 0
Post back what it shows please.
Plus take note of what I said with the "

No you are not trying to do the impossible. The hardest thing is probably getting the parameters correct with all the double quotes, once you get the path and filename correct.?
 
Last edited:
Upvote 0
Can you give me the steps you took to get that recorded macro.
I imported a csv file and my code looks like this

Code: 
Option Explicit


Sub Macro2()
'
' Macro2 Macro
'


'
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\Temp\google.csv", _
        Destination:=Range("$A$1"))
        .Name = "google"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2, 1, 4, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Import and format txt file into open workbook.
Replies
4
Views
623
djdbg1
D
B
  • Question
VBA Web Query
Replies
2
Views
464
buballo
B
J
  • Question
Using VBA Power Query to import PDF tables
Replies
2
Views
3K
RoryA
RoryA
J
  • Question
Created Macro That Formats Sales Data through power query and keep getting errors when I try and run it
Replies
1
Views
513
Fluff
Fluff
Q
  • Question
How do I pass a string variable to the web.contents argument of Power Query (Get & Transform)?
Replies
3
Views
635
Mario_R
M

Forum statistics

Threads
1,214,875
Messages
6,122,046
Members
449,063
Latest member
ak94

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top