Hi again,
If you've tried the suggestion in my previous post you may have found it
a bum steer. I'd forgotten that recent updates for Access 2002 and 2003
have removed the ability to write to spreadsheets via queries as shown.
(If you're using earlier versions you're OK.)
The general idea stays the same, with an inner and outer loop. The
difference is that instead of directly creating and executing a SQL
statement, one has to manipulate the SQL statement contained in a query
and then export the query. Something like this:
1) Create a query in the usual way. Make it a select query, but it
doesn't matter what fields it gets from what table: we're going to
control that from our code. Let's call it qryTempExport.
Dim BookName As String
Dim SheetName As String
Dim strSQL As String
Dim strWhere As String
Dim qdfTempExport As DAO.QueryDef
Const QRY_EXPORT = "qryTempExport"
'A string constant containing the basic SQL statement
'for the 600 queries we'll construct and export. The
'WHERE clause will be constructed in code each time.
Const BASE_SQL = "SELECT * FROM MyQuery WHERE "
Set qdfTempExport = CurrentDB.QueryDefs(QRY_EXPORT)
'Outer loop, once for each of c.50 workbooks
Bookname = blah blah
'Inner loop, once for each sheet in each workbook
Sheetname = blah blah
'Construct the meat of the WHERE clause that
'selects the records for this sheet of this workbook.
strWhere = blah blah
'Set the new SQL statement for the query
qdfTempExport.SQL = BASE_SQL & strWhere
'export it
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
QRY_EXPORT, BookName, True, Sheetname
'End inner loop
'End outer loop
Hi Corkster,
I'd probably do this by writing code that creates and executes 600 SQL
statements, each doing one of the things you want. Pseudo code:
Dim dbD As DAO.Database
Dim BookName As String 'Filespec of workbook to create
Dim SheetName As String 'Name of worksheet
Dim strSQL As String
Set dbD = CurrentDB()
'Outer loop, once for each of c.50 workbooks
Bookname = blah blah
'Inner loop, once for each sheet in each workbook
Sheetname = blah blah
'At this point, use Bookname and Sheetname to create the
'SQL statement for a Make Table query, and assign it to strSQL.
'Presumably you need a different WHERE clause for each query.
'The syntax to write directly to a workbook is like this:
strSQL = "SELECT * INTO " _
& "[Excel 8.0;HDR=Yes;" & BookName & ";].[" & SheetName " _
& "] FROM XXX WHERE blah blah blah;"
dbD.Execute strSQL, dbFailOnError
'End inner loop
'End outer loop
XXX in the SQL statement can of course be either a table or a (stored)
query.
I am working on a project that requires approx 600 queries to be
exported into Excel.
I have coded it using the DoCmd.TransferSpreadsheet command. However,
there are 600 queries that will be exported into approx 50 workbooks.
Would the DoCmd.TransferSpreadsheet command be my best option?
Also, would building 600 queries be my best option on getting the data
and exporting to Excel? Or would there be a better option coding in
VBA Form?
Couple more needs. Within these queries there are 5 columns (data
input by users) that need to total 100 percent. The users are going to
enter the data into the Excel spreadsheets we sent out and filling out
col 1, col 2, col 3, col 4, col 5. Is there away to code within Access
to send code into Excel if these columns don't total 100 then error
message?
Any ideas and help would be greatly appreciated
Corkster
