.Refresh BackgroundQuery:=False

B

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
 
J

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
 
F

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
 
B

Basta1980

Hi Joel,

Thank you very much for your help!!!

Joel said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top