Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing External Data using a variable
Hi, I am trying to query an access database using "Import External
Data". I originally recorded the macro, and used an arbitrary date to filter for the data in Access. Now I am trying to make it more dynamic by allowing the user to enter any date, and passing that variable to the macro. However, I have ran into the several problems. I thought it was because the format of the date was wrong, however when I change the format, it still gives me an error under: ..Refresh BackgroundQuery = False. I would greatly appreciate anyones help. Here is the code: CorrectFormatIs = Format(PriceDate, "yyyy-mm-dd") & " 00:00:00" Range("H1").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.ClearContents With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=MS Access Database;DBQ=S:\Mid-Atlantic\PricingBoard\Board\DailyBoard.mdb;Default Dir=S:\Mid-Atlantic\PricingBoard\Board;Driv" _ ), Array("erId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;")), _ Destination:=Range("H1")) .CommandText = Array( _ "SELECT `~TMPCLP221001`.CoName, `~TMPCLP221001`.PriceType, `~TMPCLP221001`.Size, `~TMPCLP221001`.ClosingProb, `~TMPCLP221001`.DatetoCust, `~TMPCLP221001`.Product" & Chr(13) & "" & Chr(10) & "FROM `S:\Mid-Atlantic\PricingBoard\Boa" _ , _ "rd\DailyBoard`.`~TMPCLP221001` `~TMPCLP221001`" & Chr(13) & "" & Chr(10) & "WHERE (`~TMPCLP221001`.DatetoCust= {ts 'CorrectFormatIs'})" & Chr(13) & "" & Chr(10) & "ORDER BY `~TMPCLP221001`.CoName" _ ) .Name = "Query from MS Access Database_1" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing External Data using a variable
Gregory,
I'm assuming that PriceDate was assigned a value before CorrectFormatIs = Format(PriceDate, "yyyy-mm-dd") & " 00:00:00" (the first line of the code you posted). Is PriceDate declared as a Date data type variable? Is the "DatetoCust" field in the Access DB a Date data type or a Text data type? At first glance, it appears that you don't have your concatenation for your WHERE statement correct. Try changing: "WHERE (`~TMPCLP221001`.DatetoCust= {ts'CorrectFormatIs'})" to "WHERE (`~TMPCLP221001`.DatetoCust= {ts'" & CorrectFormatIs & "'})" (I'm not familiar with the "{ts'...'}" part of your WHERE statement. It seems like it might be unnecessary, but it also makes me think that the DatetoCust field is a text field.) Also, if DatetoCust is a Date/time data type, you may need to surround your date with #'s (pound/number signs). So you might try changing your WHERE statement to: "WHERE (`~TMPCLP221001`.DatetoCust= {ts'#" & CorrectFormatIs & "#'})" or "WHERE (`~TMPCLP221001`.DatetoCust= #" & CorrectFormatIs & "#)" Don't quote me on this (I am not an expert), but I don't think that you need to format it, just as long as it is a date data type. Also, I THINK that you can eliminate all of the "Chr(13) & "" & Chr(10)", if you wanted to; they are unnecessary. The SQL syntax does not require statements to be on separate lines, it just looks for the key words: SELECT, FROM, WHERE, ORDER BY, GROUP BY, etc... (I'm almost certain that the space between Chr(13) and Chr (10) is unnecessary) I hope some, if not all of this, helps, Conan Kelly wrote in message oups.com... Hi, I am trying to query an access database using "Import External Data". I originally recorded the macro, and used an arbitrary date to filter for the data in Access. Now I am trying to make it more dynamic by allowing the user to enter any date, and passing that variable to the macro. However, I have ran into the several problems. I thought it was because the format of the date was wrong, however when I change the format, it still gives me an error under: .Refresh BackgroundQuery = False. I would greatly appreciate anyones help. Here is the code: CorrectFormatIs = Format(PriceDate, "yyyy-mm-dd") & " 00:00:00" Range("H1").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.ClearContents With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=MS Access Database;DBQ=S:\Mid-Atlantic\PricingBoard\Board\DailyBoard.mdb;Default Dir=S:\Mid-Atlantic\PricingBoard\Board;Driv" _ ), Array("erId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;")), _ Destination:=Range("H1")) .CommandText = Array( _ "SELECT `~TMPCLP221001`.CoName, `~TMPCLP221001`.PriceType, `~TMPCLP221001`.Size, `~TMPCLP221001`.ClosingProb, `~TMPCLP221001`.DatetoCust, `~TMPCLP221001`.Product" & Chr(13) & "" & Chr(10) & "FROM `S:\Mid-Atlantic\PricingBoard\Boa" _ , _ "rd\DailyBoard`.`~TMPCLP221001` `~TMPCLP221001`" & Chr(13) & "" & Chr(10) & "WHERE (`~TMPCLP221001`.DatetoCust= {ts 'CorrectFormatIs'})" & Chr(13) & "" & Chr(10) & "ORDER BY `~TMPCLP221001`.CoName" _ ) .Name = "Query from MS Access Database_1" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing external data | Excel Discussion (Misc queries) | |||
Importing External Data | Excel Discussion (Misc queries) | |||
Importing External Data | Excel Discussion (Misc queries) | |||
Importing External Data | Excel Discussion (Misc queries) | |||
Importing External Data | Excel Discussion (Misc queries) |