2003/2007

Guess I do not understand what is going on in the background when I
execute the following (intended to process a number of Text files to
import into XL as I get VBA "1004" error when .Refresh
BackgroundQuery:=False :

Sub XLPolySAP()
'
'
Dim myRange As Range
Dim myFileName As String
myFileName = InputBox("Enter file name: ", "File to Process")
With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\C lients
\Excel Polymer\SAP Downloads" & myFileName & ".txt",
Destination:=Range("A1"))
'With ActiveSheet.QueryTables.Add(Connection:="TEXT;M:\S AP
Downloads\148530 US31 US32 May 2007.txt", Destination:=Range("A1"))
.Name = myFileName
.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(9, 1, 9, 1, 9, 1, 9, 1, 9, 1,
9, 1, 9, 1, 9, 1, 9, 3, 9, 1, 9, _
1, 9, 1, 9, 3, 9, 1, 9, 1, 9, 1, 9, 1, 9, 3, 9, 1, 9, 1, 9, 1,
9, 1, 9, 1, 9, 1, 9, 1, 9, 1, 9, 9)
.TextFileFixedColumnWidths = Array(1, 6, 1, 18, 1, 20, 1, 5,
1, 30, 1, 12, 1, 4, 1, 10, 1, _
10, 1, 8, 1, 10, 1, 4, 1, 10, 1, 4, 1, 13, 1, 10, 1, 10, 1,
10, 1, 10, 1, 10, 1, 10, 1, 12, 1, 12, 1, 4, 1, 17, _
1, 10, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Rows("1:6").Delete
Rows("2:2").Delete
Set myRange = Range(Cells(Cells(Rows.Count, "T").End(xlUp).Row -
2, "A").Address, Cells(Cells(Rows.Count, "T").End(xlUp).Row,
"Z").Address)
'************* Calculates Used Range then Filters out Blank cells
**************
myRange.AutoFilter Field:=1, Criteria1:="<", Operator:=xlAnd
Set myRange = ActiveSheet.Range("A1", Cells(Rows.Count,
"A").End(xlUp))
'************* Deletes Rows with blank cells in Column A
***********************
Application.DisplayAlerts = False
myRange.Delete
Application.DisplayAlerts = True
'Selection.AutoFilter
Range("A1").Select
End Sub


Is the BackgroundQuery saved somewhere?

Also, I intend to use this macro to process a number of files. How
do I get around the error?

TIA Dennis