kw66722
asked on
creating excel file from access with docmd
I am creating an excel spreadsheet from access where I build the query in the code behind using docmd.
Dim excelquery As String
Dim excelName As String
excelquery = "SELECT a,b, c FROM tableSql where a = 123"
excelName = "Excel_" & Month(Date) & Day(Date) & Year(Date) & ".xls"
DoCmd.OutputTo acOutputQuery, excelquery, acFormatXLS, excelName, True
When I run it I get a message that the access database could not find the object "Select ..."
Is there another way to do this?
Dim excelquery As String
Dim excelName As String
excelquery = "SELECT a,b, c FROM tableSql where a = 123"
excelName = "Excel_" & Month(Date) & Day(Date) & Year(Date) & ".xls"
DoCmd.OutputTo acOutputQuery, excelquery, acFormatXLS, excelName, True
When I run it I get a message that the access database could not find the object "Select ..."
Is there another way to do this?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
first create a query then save it.
is this query you are building will be the same but different criteria?
is this query you are building will be the same but different criteria?
ASKER
They query changes based on multiple fields on the form. There are many different combinations for the where statement.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<Do you think I should delete the table when done?>
Yes you can, it is up to you.
but, like what i posted above, you can reuse the query by changing its definition.
Yes you can, it is up to you.
but, like what i posted above, you can reuse the query by changing its definition.
btw, what access version are you using?
to enable DAO.Querydef, you have to add to your References the Microsoft DAO 3.6 Object Library
to enable DAO.Querydef, you have to add to your References the Microsoft DAO 3.6 Object Library
ASKER
I am using access 2013. I thought about doing that but I wanted something that I could keep copying and not worrying about libraries not be available.
hmm, the DAO library should be available with version 2013.
ASKER
I am selecting mine because it is part of the solution.
ASKER