VBA "1004" error when .Refresh BackgroundQuery:=False
 

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

VBA "1004" error when .Refresh BackgroundQuery:=False
 

I got it!

Should have had a "\" after ..... SAP Downloads\"

Still would like to know where the "query" is

Thanks for all those who took time!!


On Jul 25, 8:58 am, Dennis wrote:
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

VBA "1004" error when .Refresh BackgroundQuery:=False
 

The query is read the text file breaking it into the fields based on the
widths in the TextFileFixedColumnWidths and store in fields typed as listed
in TextFileColumnDataTypes. With backgroundquery set false it won't proceed
until the file is read.

Interestingly, the first thing done after reading is to delete the first 6
rows which could be accoplished by setting TextFileStartRow = 7

Peter Richardson


"Dennis" wrote:

I got it!

Should have had a "\" after ..... SAP Downloads\"

Still would like to know where the "query" is

Thanks for all those who took time!!