| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign a Date Variable within VBA Q
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Assign a Date Variable within VBA Q
John,
Try formatting the date FromDate = Format(Workbooks("Pull Gross Sales for 2005 Financial Year.xls") _ ..Worksheets("Sheet2").Range("A1").Value),"yyyy-mm-dd") -- HTH RP (remove nothere from the email address if mailing direct) "John" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Assign a Date Variable within VBA Q
Hi John,
Did you get this to work? "John" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Assign a Date Variable within VBA Q
Afraid not David, still stuck at the Refresh BackgroundQuery:=False line
Though I haven't tried Bob's suggestion, can't do until I'm back in the office on Tuesday "David" wrote in message ... Hi John, Did you get this to work? "John" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Assign a Date Variable within VBA Q
Hi John,
Just thought you might like to know there is a very similar post to yours. Maybe he was able to solve the dilema. To find it, Date: 12/18/2004, Title: "Data Import Cell Reference", Time: 2:51 AM, Name: John. This also seems to be a format problem, but posibly John found a work around or something. "John" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Assign a Date Variable within VBA Q
Did you notice that that John and this John are using the same email
address? "John" Weird, heh? -- Regards, Tom Ogilvy "David" wrote in message ... Hi John, Just thought you might like to know there is a very similar post to yours. Maybe he was able to solve the dilema. To find it, Date: 12/18/2004, Title: "Data Import Cell Reference", Time: 2:51 AM, Name: John. This also seems to be a format problem, but posibly John found a work around or something. "John" wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Assign a Date Variable within VBA Q
This must be why they pay you all that big money Tom.
"Tom Ogilvy" wrote: Did you notice that that John and this John are using the same email address? "John" Weird, heh? -- Regards, Tom Ogilvy "David" wrote in message ... Hi John, Just thought you might like to know there is a very similar post to yours. Maybe he was able to solve the dilema. To find it, Date: 12/18/2004, Title: "Data Import Cell Reference", Time: 2:51 AM, Name: John. This also seems to be a format problem, but posibly John found a work around or something. "John" wrote: 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Assign a Date Variable within VBA Q
Bob tried your format code but its getting a compile error "Expected end of
statement" error at the "," "Bob Phillips" wrote in message ... John, Try formatting the date FromDate = Format(Workbooks("Pull Gross Sales for 2005 Financial Year.xls").Worksheets("Sheet2").Range("A1").Value) ,"yyyy-mm-dd") -- HTH RP (remove nothere from the email address if mailing direct) "John" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Assign a Date Variable within VBA Q
Get rid of the closing parenthesis after Value.
-- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "John" wrote in message ... Bob tried your format code but its getting a compile error "Expected end of statement" error at the "," "Bob Phillips" wrote in message ... John, Try formatting the date FromDate = Format(Workbooks("Pull Gross Sales for 2005 Financial Year.xls").Worksheets("Sheet2").Range("A1").Value) ,"yyyy-mm-dd") -- HTH RP (remove nothere from the email address if mailing direct) "John" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Assign a Date Variable within VBA Q
Bingo , that worked
Thanks to all the Guy's who helped with this problem, it will allow me to apply a variable to a whole series of ideas I have and safe time Thanks again "Chip Pearson" wrote in message ... Get rid of the closing parenthesis after Value. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "John" wrote in message ... Bob tried your format code but its getting a compile error "Expected end of statement" error at the "," "Bob Phillips" wrote in message ... John, Try formatting the date FromDate = Format(Workbooks("Pull Gross Sales for 2005 Financial Year.xls").Worksheets("Sheet2").Range("A1").Value) ,"yyyy-mm-dd") -- HTH RP (remove nothere from the email address if mailing direct) "John" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Assign value to a variable name | Excel Discussion (Misc queries) | |||
| Assign value to variable | Excel Discussion (Misc queries) | |||
| Variable VB to assign new sheet name | Excel Worksheet Functions | |||
| assign variable | Excel Programming | |||
| VBA routine to assign first name only to a variable | Excel Programming | |||
Linear Mode
