Source Code for Formatting an Excel spreadsheet when Exporting a Query

[Collins]

To address this:



The part that gets data is:

'SQL statement to retrieve data from database
SQL = "SELECT PartNo, PartName, Price, SalePrice, " & _
"(Price - SalePrice) / Price AS Discount " & _
"FROM Parts " & _
"ORDER BY PartNo "

So you can replace that SQL with your own. In other words, your fields in the SELECT clause and your table or query instead of Parts. At the very simplest:

SQL = "SELECT * FROM YourTableOrQuery"

which would return all fields and all records.

I replaced all the SQL from the source code and replaced it with SQL = "SELECT * QueryExportData"
When I ran it I received an error " Error Number: 3075=Syntax error (missing operator) in query expression '* QueryExportData'. "

Thoughts??? I'm sure it's me...

[June7]

Need FROM keyword.

SELECT * FROM QueryExportData

[Collins]

Need FROM keyword.

SELECT * FROM QueryExportData

That got it. It just exported to a spreadsheet.

Let me tinker with the formatting part of the code and make sure I don't have any other questions.

Thanks a ton guys!!!

[pbaldy]

A trick I've often used is to record a macro in Excel (which is actually VBA) doing whatever I'm looking to automate. That gives you the shell of the code you need in Access. You always need to add the Excel object to the beginning, but like I said it gives you the guts of the code.

[Collins]

A trick I've often used is to record a macro in Excel (which is actually VBA) doing whatever I'm looking to automate. That gives you the shell of the code you need in Access. You always need to add the Excel object to the beginning, but like I said it gives you the guts of the code.

Now Macros I have can do... So you're saying record the formatting in access as a macro then take the code that was written to do that and copy it over to the VBA editor in Access? What additional code is needed to get access to read the formatting code after my "SELECT * FROM QueryExportData" statement?

[pbaldy]

No, I'm saying record a macro in Excel then adapt the code for use in Access. If I didn't know how to bold and underline a cell, I'd record a macro doing that and then examine the code that Excel created.

[Collins]

No, I'm saying record a macro in Excel then adapt the code for use in Access. If I didn't know how to bold and underline a cell, I'd record a macro doing that and then examine the code that Excel created.

Oh gotcha...