By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,632 Members | 1,340 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,632 IT Pros & Developers. It's quick & easy.

Code Assist

P: 3
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?

Expand|Select|Wrap|Line Numbers
  1. Sub Macro2()
  2. ' Macro recorded 3/15/2007 by Matthew
  3. Sheets("data").Select
  4. Dim FileName As Variant
  5. FileName = Dir("C:\DC071\DC071_UNIXPRINT_*.HTML")
  6.  
  7.         With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FileName, Destination:=Range("A1"))
  8.         .FileName =
  9.         .FieldNames = True
  10.         .RowNumbers = False
  11.         .FillAdjacentFormulas = False
  12.         .PreserveFormatting = True
  13.         .RefreshOnFileOpen = False
  14.         .RefreshStyle = xlOverwriteCells
  15.         .SavePassword = False
  16.         .SaveData = True
  17.         .AdjustColumnWidth = True
  18.         .RefreshPeriod = 0
  19.         .TextFilePromptOnRefresh = False
  20.         .TextFilePlatform = xlWindows
  21.         .TextFileStartRow = 24
  22.         .TextFileParseType = xlFixedWidth
  23.         .TextFileTextQualifier = xlTextQualifierDoubleQuote
  24.         .TextFileConsecutiveDelimiter = False
  25.         .TextFileTabDelimiter = True
  26.         .TextFileSemicolonDelimiter = False
  27.         .TextFileCommaDelimiter = False
  28.         .TextFileSpaceDelimiter = False
  29.         .TextFileColumnDataTypes = Array(1, 1, 1, 1)
  30.         .TextFileFixedColumnWidths = Array(34, 10, 11)
  31.  
  32.     End With
  33.     Sheets("data").Select
  34.     Range("B4").Select
  35.     With Selection.Interior
  36.         .ColorIndex = 1
  37.         .Pattern = xlSolid
  38.     End With
  39.     Range("D11").Select
  40. End Sub
Mar 16 '07 #1
Share this Question
Share on Google+
1 Reply


Expert 5K+
P: 8,434
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?
Mar 19 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.