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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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

J
  • Solved
How to clean scrape a website for financial data?
2
Replies
17
Views
665
Edgar_
Edgar_
S
  • Solved
Web Query in VBA Loop through tables on page to choose get tags from table instead of text
2
Replies
13
Views
608
Domenic
D
D
  • Question Question
Import and format txt file into open workbook.
Replies
4
Views
1K
djdbg1
D
H
  • Solved
Query not pulling right info when running in VBA
2
Replies
13
Views
396
Holley
H
B
  • Question Question
VBA Web Query
Replies
2
Views
1K
buballo
B

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,632
Latest member
jladair

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