Macro Issue - Run time error 1004

[Guest]

My VBA skills are limited to using recorded macros, not writing them- so when
something won't execute... well, I'm lost.

I painstakingly recorded my actions importing 38 small text files, including
a clear contents command so that the workbook can be re-used. I have done
this successfully in the past. The macro worked after creation once, but not
after a save/close workbook. It now stalls at ' .Refresh
BackgroundQuery:=False' of the first import as displayed below, with 'run
time error 1004 - Application Defined or object - defined error

Sub DataImport()
'
' DataImport Macro
' Clear Old Data, Import New Data
'

'
Sheets("ChevyL").Select
Rows("10:55").Select
Selection.ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;\\addc01\TS My Documents\gcomproom\output\PGPCB01.999",
Destination:= _
Range("A10"))
.Name = "PGPCB01"
.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)
.TextFileFixedColumnWidths = Array(5, 21, 9, 9)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("ChevyT").Select
Rows("10:55").Select
Selection.ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _

.....and the process continues for 37 additional files.

Anyone have a quick fix?

TIA,

matt7102

 

[Jim Cone]

M,

My knowledge in this area is pretty limited, however I would try
commenting out the offending line and seeing if the code will run...
' .Refresh BackgroundQuery:=False

Jim Cone
San Francisco, USA
Free add-ins at my website ...
http://www.realezsites.com/bus/primitivesoftware



"Matt7102" <[email protected]>
wrote in message
My VBA skills are limited to using recorded macros, not writing them- so when
something won't execute... well, I'm lost.
I painstakingly recorded my actions importing 38 small text files, including
a clear contents command so that the workbook can be re-used. I have done
this successfully in the past. The macro worked after creation once, but not
after a save/close workbook. It now stalls at ' .Refresh
BackgroundQuery:=False' of the first import as displayed below, with 'run
time error 1004 - Application Defined or object - defined error

Sub DataImport()
'
' DataImport Macro
' Clear Old Data, Import New Data
'

'
Sheets("ChevyL").Select
Rows("10:55").Select
Selection.ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;\\addc01\TS My Documents\gcomproom\output\PGPCB01.999",
Destination:= _
Range("A10"))
.Name = "PGPCB01"
.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)
.TextFileFixedColumnWidths = Array(5, 21, 9, 9)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("ChevyT").Select
Rows("10:55").Select
Selection.ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _

.....and the process continues for 37 additional files.
Anyone have a quick fix?
TIA,
matt7102