Code Assist

[visualcomplicated]

Hey All,

I am trying to get a macro set up to pull a specific .html file from a public drive, and put the data into an excel spreadsheet as a .txt file would. I used the text import wizard then tried to change the last part (date) of the file name to an asterisk so that it would pull the file no matter what the date was and I would not have to change it daily.

The asterisk was originally "02222007_0" which is the date of the file.

Does that makes sense?

Sub Macro2()
' Macro recorded 3/15/2007 by Matthew
Sheets("data").Select
Dim FileName As Variant
FileName = Dir("C:\DC071\DC071_UNIXPRINT_*.HTML")
 
        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FileName, Destination:=Range("A1"))
        .FileName =
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlWindows
        .TextFileStartRow = 24
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(34, 10, 11)
 
    End With
    Sheets("data").Select
    Range("B4").Select
    With Selection.Interior
        .ColorIndex = 1
        .Pattern = xlSolid
    End With
    Range("D11").Select
End Sub

[Killer42]

I do seem to recall that Dir( ) returns only the file name, without the path. So perhaps you are trying to open the correct filename from the wrong folder?