Thread: importing the newest file in a folder as text

Hi hotwheels

Leith has code which you can modify form xlms to csv

http://www.excelforum.com/excel-prog...-a-folder.html

hi hotwheels
sound like the csv file is a xls file with csv extension
Has the csv file been save correctly?

Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore Moderators' or Administrators' requests - note that this includes requests by senior members as well, if you are unclear about their request or instruction then send a private message to them asking for help. Do not post a reply to a thread where a moderator has requested an action that has not been complied with e.g Title change or Code tags...etc

please address Roys request in your previous thread

hi hotwheels

your can change the directory and sheet # to suit

Option Explicit
Sub ptest()
Dim xDir As String, csvFile As String, LastestFile As String
Dim ModDate As Date, LastDate As Date
Dim FileObj As Object, wsXLS As Object, wbCSV As Object

Set wsXLS = ThisWorkbook.Sheets("Sheet1") '
Set FileObj = CreateObject("Scripting.FileSystemObject")
xDir = "C:\Documents and Settings\ect....."
csvFile = Dir(xDir & "\*.csv")
Do While csvFile <> ""
     ModDate = FileObj.GetFile(xDir & "\" & csvFile).DateLastModified
     If LastDate < ModDate Then
          LastestFile = csvFile
          LastDate = ModDate
     End If
     csvFile = Dir()
Loop
Debug.Print LastestFile & " " & LastDate
  Set wbCSV = Workbooks.Open(xDir & "\" & LastestFile)
        Range("A1").CurrentRegion.Copy wsXLS.Cells(1, wsXLS.Columns.Count).End(xlToLeft).Offset(0, 1)
        wbCSV.Close
Set wsXLS = nothing
Set FileObj = nothing
Set wbCSV = nothing
End Sub

You can see from Pike's code how he gets the filename to open. Then, his line of code shows he is opening the found filename with this:

Set wbCSV = Workbooks.Open(xDir & "\" & LastestFile)

So you should be able to take that same reference:

xDir & "\" & LastestFile

And use that in your GetData code. Give it a try. Merge your method of file extraction with his method of finding the filename.

Like so:

Option Explicit

Sub GetDataLatestFile()
'
' getdatalatestfile Macro
'
' Keyboard Shortcut: Ctrl+Shift+W
'
Dim xDir As String, csvFile As String, LatestFile As String
Dim ModDate As Date, LastDate As Date
Dim FileObj As Object, wsXLS As Object, wbCSV As Object

Set wsXLS = ActiveWorkbook.Sheets("Sheet1")
Set FileObj = CreateObject("Scripting.FileSystemObject")
xDir = "C:\Users\Admin\Downloads"
xDir = "C:\2010"
csvFile = Dir(xDir & "\*.csv")
Do While csvFile <> ""
     ModDate = FileObj.GetFile(xDir & "\" & csvFile).DateLastModified
     If LastDate < ModDate Then
          LatestFile = csvFile
          LastDate = ModDate
     End If
     csvFile = Dir()
Loop
    
If LatestFile = "" Then
    MsgBox "No file found"
    Exit Sub
End If

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & xDir & "\" & LatestFile, Destination:=Range("$A$1"))
        .Name = LatestFile
        .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 = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

End Sub

Actually the line of code I accidentally left in:

xDir = "C:\2010

....can be removed. That was for testing on my system.