Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Refresh BackgroundQuery:=False
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.CRP ST_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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Refresh BackgroundQuery:=False
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 End Sub"Basta1980" wrote: 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.CRP ST_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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Refresh BackgroundQuery:=False
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 -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Refresh BackgroundQuery:=False
Hi Joel,
Thank you very much for your help!!! "Joel" wrote: 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 End Sub"Basta1980" wrote: 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.CRP ST_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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
.Refresh BackgroundQuery:=False | Excel Programming | |||
.Refresh BackgroundQuery:=False | Excel Programming | |||
.Refresh BackgroundQuery:=False | Excel Programming | |||
Problems with .Refresh BackgroundQuery:=False | Excel Worksheet Functions | |||
Excel does not close from VB!! (when i refresh Refresh query with BackgroundQuery:=False) | Excel Programming |