I recorded a macro to pull text file data into a template. I wanted to make it apply to any file I was pulling in, so I replaced the date information in the file name referenced in the code with the wildcard * so that (I assumed) it would just pull in the first file it found in the folder. When I run it, the template file is pulled up - but I get the message:
Run-time error '1004':
The file could not be accessed. Try one of the following:
- Make sure the specified folder exists.
- Make sure the folder that contains the file is not read-only.
- Make sure the file name does not contain any of the following characters: < > ? [ ] : | or *
- Make sure the file/path name doesn't contain more than 218
When I go to the Debug, the bolded line below is what is highlighted as the problem, but I'm not savvy enough to troubleshoot this.
Sub TSU_IVR_Reports()
'
' TSU_IVR_Reports Macro
' Pulls in data from TSU text files and saves as a new file
'
'
ChDir _
"C:\Documents and Settings\donajose\Desktop\TSU Cycle time\TSU IVR report import"
Workbooks.Open Filename:= _
"C:\Documents and Settings\donajose\Desktop\TSU Cycle time\TSU IVR report import\TSU IVR report template.xlsm"
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\donajose\Desktop\TSU Cycle time\TSU IVR report import\2012*.txt" _
, Destination:=Range("$A$5"))
.Name = "20120506_1"
.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 = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False End With
Columns("A:Q").Select
Range("A3").Activate
Columns("A:Q").EntireColumn.AutoFit
Selection.ColumnWidth = 20.29
Columns("A:Q").EntireColumn.AutoFit
Range("A2:Q2").Select
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\donajose\Desktop\TSU Cycle time\TSU IVR report import\TSU IVR 2012*.xlsm" _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub
When I ran the macro as recorded, it reproduced the correct results (see attached). Ultimately I need to run this for a large number of files, but don't need it to loop (yet).
Thanks for any help provided!
Bookmarks