J
John
I have a few questions the answers of which might explain why my code is not
working. First an outline of what I'm trying to do
Extract information from an MS Access database and pull in to Excel. I've
recorded the Macro and everything works great, except that the 'from' date
with regards to Sales information is hard coded and thus inflexible, in that
I wouldn't allow other users, who have to get this information, dabble with
the code itself
I've been told through this group that I should set a variable and then call
this through the relevant code line. When the information is extracted the
hard code for the relevant Sales date is in the following format "YYYY-MM-DD
hh:mm:ss". Now the cell which I want to reference my cell against visually
is formated DD/MM/YY
The error message I'm getting is a SQL debug on the line below, but from
reading other usergroups it could be a formatting problem
..Refresh BackgroundQuery:=False
My relevant code is then set as follows
Dim FromDate As String
FromDate = Workbooks("Pull Gross Sales for 2005 Financial
Year.xls").Worksheets("Sheet2").Range("A1").Value
"WHERE (tblStoreTotals.SalesDate>={ts '" & FromDate & "'})"
Now my questions
1. Does it matter that the source cell (Sheet2 A1) is typed in as DD/MM/YY
with no hh:mm:ss. I'm in Europe so thats how we type in dates
2. Do I have to format a linked cell to A1 say B1 as YYYY-MM-DD hh:mm:ss
3. Someone must have got a Data Import module working with a variable
reference, any examples would be appreciated
working. First an outline of what I'm trying to do
Extract information from an MS Access database and pull in to Excel. I've
recorded the Macro and everything works great, except that the 'from' date
with regards to Sales information is hard coded and thus inflexible, in that
I wouldn't allow other users, who have to get this information, dabble with
the code itself
I've been told through this group that I should set a variable and then call
this through the relevant code line. When the information is extracted the
hard code for the relevant Sales date is in the following format "YYYY-MM-DD
hh:mm:ss". Now the cell which I want to reference my cell against visually
is formated DD/MM/YY
The error message I'm getting is a SQL debug on the line below, but from
reading other usergroups it could be a formatting problem
..Refresh BackgroundQuery:=False
My relevant code is then set as follows
Dim FromDate As String
FromDate = Workbooks("Pull Gross Sales for 2005 Financial
Year.xls").Worksheets("Sheet2").Range("A1").Value
"WHERE (tblStoreTotals.SalesDate>={ts '" & FromDate & "'})"
Now my questions
1. Does it matter that the source cell (Sheet2 A1) is typed in as DD/MM/YY
with no hh:mm:ss. I'm in Europe so thats how we type in dates
2. Do I have to format a linked cell to A1 say B1 as YYYY-MM-DD hh:mm:ss
3. Someone must have got a Data Import module working with a variable
reference, any examples would be appreciated