.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

 

[Francois via OfficeKB.com]

I think that you will need to do something like this:-

Sheets("Data").Select
Range("C7").QueryTable.Refresh BackgroundQuery:=False

Ammend Sheet name & Range to your actual needs

 

[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