Good Morning,
I have a macro that I wrote and it had been working fine until the file name was changed to include the time. Below is the code and file name that worked previously.
Sub Upload()
'
Dim Message, Title, Default, MyValue
Dim i As Integer, vnames As Variant, wbk As Workbooks
Message = "Please enter the date for the files you want to access"
Title = "Date for files"
Default = Date - 1
MyValue = InputBox(Message, Title, Default)
z = Format(MyValue, "yyyymmdd")
Call ClearContents_Mark
FilePath = "T:\Admin\Fin\ProvFin\SHARED\Census Data\"
Sheets("NurseStatProv").Select
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;T:\Admin\Fin\ProvFin\SHARED\Census Data\" & z & ".NURSESTATPROV.txt" _
, Destination:=Range("A1"))
'20100720.NURSESTATPROV.txt
'.Name = "*."&"NURSESTATPROV"
.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 = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(1, 8, 10, 11, 11, 9, 9, 9, 11, 10, 11, 8)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("NurseStatPark").Select
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;T:\Admin\Fin\ProvFin\SHARED\Census Data\" & z & ".NURSESTATPARK.txt" _
, Destination:=Range("A1"))
This is part of the code that I have setup to read in the text file. This worked fine when the file name was 20110515.HOSPSERVPROV.txt, but since the name was changed to include the date
20110515_234845.HOSPSERVPROV.txt I am not able to get this macro to work. I have tried several different types of wildcards but I seem to be hitting a wall. (ie: *, %, ?)
I am hoping it is something simple that I am missing.
Thank you in advance,
Mark
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar