.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 |
.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 |
.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 |
.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 |
All times are GMT +1. The time now is 05:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com