VBA-importing CSV file

[Anna314]

Hi

I would like to create macro that imports the newest CSV file. The file name is changing every day and is located in Z:\ drive (no folder here). This folder holds two different file types with same name (.FLG and .CSV)
So far I’ve got to the error stage ’Run time error 1004’ file could not be found and it stops on:

[Workbooks.Open strFilename]

Last week when I checked immediate window it showed correct CSV file name but it wouldn't import. So I don’t understand why there was an error that file could not be found.

Today when I was running macro I discovered other issue. When last week immediate window was showing .CSV file, today it shows .FLG file!

So I have two problems:
*why today it shows .FLG not .CSV file (both were last week in that drive too ).
*how to fix the 1004 error.

MACRO LOOKS LIKE THIS:

Sub Import()

Dim r As Range
Set r = Range("A" & Cells(Rows.Count, "A").End(xlUp).Row + 1)
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Z:\.csv", Destination:=r)
.Name = "Master"
.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 = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True

End With
End Sub


Sub GetMostRecentFile()

Dim FileSys As FileSystemObject
Dim objFile As File
Dim myFolder
Dim strFilename As String
Dim dteFile As Date

'set path for files - change for your folder
Const myDir As String = "Z:"

'set up filesys objects
Set FileSys = New FileSystemObject
Set myFolder = FileSys.GetFolder(myDir)


'loop through each file and get date last modified. If largest date then store Filename
dteFile = DateSerial(1900, 1, 1)
For Each objFile In myFolder.Files
If objFile.DateLastModified > dteFile Then
dteFile = objFile.DateLastModified
strFilename = objFile.Name
End If
Next objFile
Workbooks.Open strFilename

Set FileSys = Nothing
Set myFolder = Nothing
End Sub



Hopefully it makes sense.
Anna

 

[AlphaFrog]

When posting your code to the forum, please surround it with CODE tags. See my signature block below.

Added a test for finding the most recent .csv file instead of any file.
Added the full path and file name of the most recent file instead of just the file name.

[COLOR=darkblue]Sub[/COLOR] GetMostRecentFile()
    
    [COLOR=darkblue]Dim[/COLOR] FileSys   [COLOR=darkblue]As[/COLOR] FileSystemObject
    [COLOR=darkblue]Dim[/COLOR] objFile   [COLOR=darkblue]As[/COLOR] File
    [COLOR=darkblue]Dim[/COLOR] myFolder
    [COLOR=darkblue]Dim[/COLOR] strFilename [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] dteFile   [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Date[/COLOR]
    
    [COLOR=green]'set path for files - change for your folder[/COLOR]
    [COLOR=darkblue]Const[/COLOR] myDir   [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR] = "Z:"
    
    [COLOR=green]'set up filesys objects[/COLOR]
    [COLOR=darkblue]Set[/COLOR] FileSys = [COLOR=darkblue]New[/COLOR] FileSystemObject
    [COLOR=darkblue]Set[/COLOR] myFolder = FileSys.GetFolder(myDir)
    
    
    [COLOR=green]'loop through each file and get date last modified. If largest date then store Filename[/COLOR]
    dteFile = DateSerial(1900, 1, 1)
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] objFile [COLOR=darkblue]In[/COLOR] myFolder.Files
[B]        [COLOR=darkblue]If[/COLOR] LCase(objFile.Name) [COLOR=darkblue]Like[/COLOR] "*.csv" [COLOR=darkblue]Then[/COLOR][/B]
            [COLOR=darkblue]If[/COLOR] objFile.DateLastModified > dteFile [COLOR=darkblue]Then[/COLOR]
                dteFile = objFile.DateLastModified
                strFilename = [B]objFile.Path[/B]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
[B]        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR][/B]
    [COLOR=darkblue]Next[/COLOR] objFile
    Workbooks.Open strFilename
    
    [COLOR=darkblue]Set[/COLOR] FileSys = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] myFolder = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[Anna314]

When posting your code to the forum, please surround it with CODE tags. See my signature block below.

Added a test for finding the most recent .csv file instead of any file.
Added the full path and file name of the most recent file instead of just the file name.

[COLOR=darkblue]Sub[/COLOR] GetMostRecentFile()
    
    [COLOR=darkblue]Dim[/COLOR] FileSys   [COLOR=darkblue]As[/COLOR] FileSystemObject
    [COLOR=darkblue]Dim[/COLOR] objFile   [COLOR=darkblue]As[/COLOR] File
    [COLOR=darkblue]Dim[/COLOR] myFolder
    [COLOR=darkblue]Dim[/COLOR] strFilename [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] dteFile   [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Date[/COLOR]
    
    [COLOR=green]'set path for files - change for your folder[/COLOR]
    [COLOR=darkblue]Const[/COLOR] myDir   [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR] = "Z:"
    
    [COLOR=green]'set up filesys objects[/COLOR]
    [COLOR=darkblue]Set[/COLOR] FileSys = [COLOR=darkblue]New[/COLOR] FileSystemObject
    [COLOR=darkblue]Set[/COLOR] myFolder = FileSys.GetFolder(myDir)
    
    
    [COLOR=green]'loop through each file and get date last modified. If largest date then store Filename[/COLOR]
    dteFile = DateSerial(1900, 1, 1)
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] objFile [COLOR=darkblue]In[/COLOR] myFolder.Files
[B]        [COLOR=darkblue]If[/COLOR] LCase(objFile.Name) [COLOR=darkblue]Like[/COLOR] "*.csv" [COLOR=darkblue]Then[/COLOR][/B]
            [COLOR=darkblue]If[/COLOR] objFile.DateLastModified > dteFile [COLOR=darkblue]Then[/COLOR]
                dteFile = objFile.DateLastModified
                strFilename = [B]objFile.Path[/B]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
[B]        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR][/B]
    [COLOR=darkblue]Next[/COLOR] objFile
    Workbooks.Open strFilename
    
    [COLOR=darkblue]Set[/COLOR] FileSys = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] myFolder = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

This is incredible.
Thank you so much.

Regarding CODE TAGS you mean to add this

 [/B]in the beginning and end  of macro?