.Refresh BackgroundQuery:=False

[Basta1980]

Hi,

I get a runtime error 1004 when executing below macro. VBA points to the
..Refresh BackgroundQuery:=False statement. Anybody got a clue what I'm doing
wrong (again)?!

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 24/09/2008 by dresses
'
Fname = Application.GetOpenFilename()
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT; & Fname" _
, Destination:=Range("A3"))
.Name = _

"_pkgcus01_lvs_projs_csm_rep_layout_Sep_23_csm.CRPST_CSV_910030737_14748.20080923_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False

End With
End Sub

 

[Joel]

You error means that there is something wrong with one of the previous
statments. The query doesn't get execute until the refresh statement so any
thing could be wrong

The line below is wrong. FName is a variable a cannot be inside the double
quote

from
"TEXT; & Fname" _
to
"TEXT;" & Fname _

I cleaned up your code below to make it look better.

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 24/09/2008 by dresses
'
Fname = Application.GetOpenFilename()
'
With ActiveSheet.QueryTables.Add( _
Connection:="TEXT;" & Fname, _
Destination:=Range("A3"))

.Name = "csm_rep_layout_Sep_23"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFileTabDelimiter = True
.TextFileCommaDelimiter = True
.Refresh BackgroundQuery:=False

End With

 

[Basta1980]

Hi Joel,

Thank you very much for your help!!!

You error means that there is something wrong with one of the previous
statments. The query doesn't get execute until the refresh statement so any
thing could be wrong

The line below is wrong. FName is a variable a cannot be inside the double
quote

from
"TEXT; & Fname" _
to
"TEXT;" & Fname _

I cleaned up your code below to make it look better.

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 24/09/2008 by dresses
'
Fname = Application.GetOpenFilename()
'
With ActiveSheet.QueryTables.Add( _
Connection:="TEXT;" & Fname, _
Destination:=Range("A3"))

.Name = "csm_rep_layout_Sep_23"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFileTabDelimiter = True
.TextFileCommaDelimiter = True
.Refresh BackgroundQuery:=False

End With