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

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

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

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

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

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

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