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 |
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) |