Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Import Cell Reference
I wish to include cell reference as parameters to a Data Import module that
I have recorded but not sure of the syntax. What I have recorded works great but when I want to modify the dates I have to change the hard code. My dates within the macro are currently between 31/05/2004 and todays date. Say if a cell reference exists for 31/5/2004 in Sheet(Sales) Range C5 - how could I incorporate that below so that my Data Import could update to whatever date is in Sale C5 Thanks Sub PullSales() Sheets("Database2").Select With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DBQ=E:\JIM1_be.mdb;DefaultDir=E:\;Driver={Mi crosoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;MaxBufferSize=2048;MaxSca" _ ), Array( _ "nRows=8;PageTimeout=5;SafeTransactions=0;Threads= 3;UID=admin;UserCommitSync =Yes;" _ )), Destination:=Range("A1")) .CommandText = Array( _ "SELECT tblStoreTotals.StoreNo, tblStoreTotals.SalesDate, tblStoreTotals.TaxableSales" & Chr(13) & "" & Chr(10) & "FROM `E:\OKR1_be`.tblStoreTotals tblStoreTotals" & Chr(13) & "" & Chr(10) & "WHERE (tblStoreTotals.SalesDate={ts '2004-05-31 00:00:00'})" & Chr(13) & "" & Chr(10) & "ORD" _ , "ER BY tblStoreTotals.StoreNo, tblStoreTotals.SalesDate") .Name = "Query from Pull Sales" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Import Cell Reference
This is part of your code:
"WHERE (tblStoreTotals.SalesDate={ts '2004-05-31 00:00:00'})" & Chr(13) & Create a variable to accept the date your need, say ToDate,then rplae with this: "WHERE (tblStoreTotals.SalesDate={ts '"&(ToDate)&"'})" & Chr(13) & Hope this works for you. "John" wrote: I wish to include cell reference as parameters to a Data Import module that I have recorded but not sure of the syntax. What I have recorded works great but when I want to modify the dates I have to change the hard code. My dates within the macro are currently between 31/05/2004 and todays date. Say if a cell reference exists for 31/5/2004 in Sheet(Sales) Range C5 - how could I incorporate that below so that my Data Import could update to whatever date is in Sale C5 Thanks Sub PullSales() Sheets("Database2").Select With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DBQ=E:\JIM1_be.mdb;DefaultDir=E:\;Driver={Mi crosoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;MaxBufferSize=2048;MaxSca" _ ), Array( _ "nRows=8;PageTimeout=5;SafeTransactions=0;Threads= 3;UID=admin;UserCommitSync =Yes;" _ )), Destination:=Range("A1")) .CommandText = Array( _ "SELECT tblStoreTotals.StoreNo, tblStoreTotals.SalesDate, tblStoreTotals.TaxableSales" & Chr(13) & "" & Chr(10) & "FROM `E:\OKR1_be`.tblStoreTotals tblStoreTotals" & Chr(13) & "" & Chr(10) & "WHERE (tblStoreTotals.SalesDate={ts '2004-05-31 00:00:00'})" & Chr(13) & "" & Chr(10) & "ORD" _ , "ER BY tblStoreTotals.StoreNo, tblStoreTotals.SalesDate") .Name = "Query from Pull Sales" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Import Cell Reference
David thanks for your reply, did what you said and replaced with the code
below, now it stopping at the line ..Refresh BackgroundQuery:=False and returns a SQL syntax error Run Time 1004 I have deleted all old Query Tables, but it just won't run. I've tried changing FALSE to TRUE above but still the same Sub PullSales() Dim FromDate As Variant FromDate = Workbooks("Pull Gross Sales for 2005 Financial Year.xls").Worksheets("Sheet2").Range("A1").Value Sheets("Database2").Select With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DBQ=E:\JIM1_be.mdb;DefaultDir=E:\;Driver={Mi crosoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;MaxBufferSize=2048;MaxSca" _ ), Array( _ "nRows=8;PageTimeout=5;SafeTransactions=0;Threads= 3;UID=admin;UserCommitSync =Yes;" _ )), Destination:=Range("A1")) .CommandText = Array( _ "SELECT tblStoreTotals.StoreNo, tblStoreTotals.SalesDate, tblStoreTotals.TaxableSales" & Chr(13) & "" & Chr(10) & "FROM `E:\JIM1_be`.tblStoreTotals tblStoreTotals" & Chr(13) & "" & Chr(10) & "WHERE (tblStoreTotals.SalesDate={ts '" & (FromDate) & "'})" & Chr(13) & "" & Chr(10) & "ORD" _ , "ER BY tblStoreTotals.StoreNo, tblStoreTotals.SalesDate") .Name = "Query from Pull Sales" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Import Cell Reference
Not sure what the error is doing, but I am sure the input format for the
variable is important. When I use variables to capture a date, they have always been the most difficult to get and the format was usually the most difficult thing to make "right." "John" wrote: I wish to include cell reference as parameters to a Data Import module that I have recorded but not sure of the syntax. What I have recorded works great but when I want to modify the dates I have to change the hard code. My dates within the macro are currently between 31/05/2004 and todays date. Say if a cell reference exists for 31/5/2004 in Sheet(Sales) Range C5 - how could I incorporate that below so that my Data Import could update to whatever date is in Sale C5 Thanks Sub PullSales() Sheets("Database2").Select With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DBQ=E:\JIM1_be.mdb;DefaultDir=E:\;Driver={Mi crosoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;MaxBufferSize=2048;MaxSca" _ ), Array( _ "nRows=8;PageTimeout=5;SafeTransactions=0;Threads= 3;UID=admin;UserCommitSync =Yes;" _ )), Destination:=Range("A1")) .CommandText = Array( _ "SELECT tblStoreTotals.StoreNo, tblStoreTotals.SalesDate, tblStoreTotals.TaxableSales" & Chr(13) & "" & Chr(10) & "FROM `E:\OKR1_be`.tblStoreTotals tblStoreTotals" & Chr(13) & "" & Chr(10) & "WHERE (tblStoreTotals.SalesDate={ts '2004-05-31 00:00:00'})" & Chr(13) & "" & Chr(10) & "ORD" _ , "ER BY tblStoreTotals.StoreNo, tblStoreTotals.SalesDate") .Name = "Query from Pull Sales" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Import Cell Reference
The Query is looking for a string, so Dim FromDate As String. Also keep an
eye on how that string is arranged because I noticed that your hard coded date was '2004-05-31 00:00:00' and your cell value was 31/5/2004. Also do not put your variable inside ( )'s, just use & FromDate & Good Luck Mike F "John" wrote in message ... David thanks for your reply, did what you said and replaced with the code below, now it stopping at the line .Refresh BackgroundQuery:=False and returns a SQL syntax error Run Time 1004 I have deleted all old Query Tables, but it just won't run. I've tried changing FALSE to TRUE above but still the same Sub PullSales() Dim FromDate As Variant FromDate = Workbooks("Pull Gross Sales for 2005 Financial Year.xls").Worksheets("Sheet2").Range("A1").Value Sheets("Database2").Select With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DBQ=E:\JIM1_be.mdb;DefaultDir=E:\;Driver={Mi crosoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;MaxBufferSize=2048;MaxSca" _ ), Array( _ "nRows=8;PageTimeout=5;SafeTransactions=0;Threads= 3;UID=admin;UserCommitSync =Yes;" _ )), Destination:=Range("A1")) .CommandText = Array( _ "SELECT tblStoreTotals.StoreNo, tblStoreTotals.SalesDate, tblStoreTotals.TaxableSales" & Chr(13) & "" & Chr(10) & "FROM `E:\JIM1_be`.tblStoreTotals tblStoreTotals" & Chr(13) & "" & Chr(10) & "WHERE (tblStoreTotals.SalesDate={ts '" & (FromDate) & "'})" & Chr(13) & "" & Chr(10) & "ORD" _ , "ER BY tblStoreTotals.StoreNo, tblStoreTotals.SalesDate") .Name = "Query from Pull Sales" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Import Cell Reference
Still no luck, I added the following code below that I picked up from the
web, but still debugs on the line ..Refresh BackgroundQuery:=False FromDate = Format(Date - 1, "yyyy-mm-dd hh:mm:ss") |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Import Cell Reference
Do you actually need to write code to achieve this? I, as well, need
data from a database on a regular basis and each run needs to be controlled with variable selection criteria. The way I do this is actually really simple: in Excel create a database query, edit the query in MS Query by adding selection criteria: instead of giving actual values (e.g. =500, 2004-05-12, or whatever) write [Give me a value]. When you press enter, a dialog box appears asking "Give me a value". So fill in the box and press enter. Then, just before you return to Excel from MS Query, you have the last small dialog asking where to put the query result data. This dialog also has a button to define parameter (remember: "Give me a value") input for your query. In the following dialog you can specify which worksheet cell contains the value for your parameter criteria. Also you can tick a box to always run the query when the cell value changes. Neat. And works like a charm. Juha ------------------ John kirjoitti: I wish to include cell reference as parameters to a Data Import module that I have recorded but not sure of the syntax. What I have recorded works great but when I want to modify the dates I have to change the hard code. My dates within the macro are currently between 31/05/2004 and todays date. Say if a cell reference exists for 31/5/2004 in Sheet(Sales) Range C5 - how could I incorporate that below so that my Data Import could update to whatever date is in Sale C5 Thanks Sub PullSales() Sheets("Database2").Select With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DBQ=E:\JIM1_be.mdb;DefaultDir=E:\;Driver={Mi crosoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;MaxBufferSize=2048;MaxSca" _ ), Array( _ "nRows=8;PageTimeout=5;SafeTransactions=0;Threads= 3;UID=admin;UserCommitSync =Yes;" _ )), Destination:=Range("A1")) .CommandText = Array( _ "SELECT tblStoreTotals.StoreNo, tblStoreTotals.SalesDate, tblStoreTotals.TaxableSales" & Chr(13) & "" & Chr(10) & "FROM `E:\OKR1_be`.tblStoreTotals tblStoreTotals" & Chr(13) & "" & Chr(10) & "WHERE (tblStoreTotals.SalesDate={ts '2004-05-31 00:00:00'})" & Chr(13) & "" & Chr(10) & "ORD" _ , "ER BY tblStoreTotals.StoreNo, tblStoreTotals.SalesDate") .Name = "Query from Pull Sales" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change import cell reference | Excel Worksheet Functions | |||
Import data from 1 cell on 1 worksheet to othr cell(s) in another | Excel Worksheet Functions | |||
how do i reference a cell with url for web query import? | Excel Worksheet Functions | |||
Help to import data from reference workbook | Excel Discussion (Misc queries) | |||
Import Data into same cell | Excel Discussion (Misc queries) |