asked on

export access query data to multiple specified worksheet

Hi Experts,

I am trying to export data from access query to specific sheets of an excel workbook.

Below is the VBA code I am using:

Kill "c:\test\test_report.xlsx" delete the old report
FileCopy "c:\test\work\test_report_work.xlsx", "c:\test\test_report.xlsx" ' Copy the work file
xlfile = "c:\test\test_report.xlsx"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "test_report_01", xlfile, True, "test01"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "test_report_02", xlfile, True, "test02"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "test_report_03", xlfile, True, "test03"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "test_report_04", xlfile, True, "test04"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "test_report_05", xlfile, True, "test05"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "test_report_06", xlfile, True, "test06"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "test_report_07", xlfile, True, "test07"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "test_report_08", xlfile, True, "test08"

test_report_work.xlsx workbook contain total of blank 8 sheet named as above(test01 ~test08)
while I execute it only copy data from the query to the same sheet first 4 sheets then add new sheets instead of copy the on the same sheets.

Please advise why its not copy data on the sheet same as first four sheet instead of adding new sheet on the test_report.xlsx workbook.

Your prompt response would be highly appreciated.

Thanks

Eric Sherman

<<<<<<test_report_work.xlsx workbook contain total of blank 8 sheet named as above(test01 ~test08) while I execute it only copy data from the query to the same sheet first 4 sheets then add new sheets instead of copy the on the same sheets.
Please advise why its not copy data on the sheet same as first four sheet instead of adding new sheet on the test_report.xlsx workbook.>>>

Probably because sheets 1 thru 4 did not exist. I think Access creates new worksheets inside your existing workbook if the worksheet already exist. Try deleting all 8 worksheets first then run your export to see if all 8 worksheets will be created.

ET

Robberbaron (robr)

from MSDN, your use of Transfer sheet is not correct
expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)

Range Optional Variant. A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

so it appears you cant export to a specific sheet within a workbook.

Robberbaron (robr)

so you may have to export to 8 workbooks, then combine into a master workbook with 8 sheets.

SOLUTION

Eric Sherman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Eric Sherman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robberbaron (robr)

mmm. never tried and cant now as dont have access anymore..

but further searching suggests that etsherman is correct (and MSDN is not...)
http://www.utteraccess.com/forum/lofiversion/index.php/t1989431.html; maybe its a relatively recent ie 2007/2010 update.

alam747

ASKER

Hi etsherman,

I tried the way advised and getting the follwing run time error:

"Table "Test01$A:AE" already exists"

would you please let me know if I missed anything.

Thanks

Eric Sherman

Use the exclamation point (!) instead of the dollar sign ($) to define the worksheet range as shown below:

"Test01!A:AE"

ET

alam747

ASKER

I used ! not the dollar sign but the message shows same as I mentioned.

Eric Sherman

This works each time for me ... I am using Office 2010 but should be the same for other versions of Office. I am exporting the Employee table to Employee.xlsx which includes the worksheet Emp_List.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "EMPLOYEE", "C:\Temp\Employee.xlsx", True, "Emp_List!A:AZ"

By any chance do you have the workbook open at the time you are running the export???

ET

alam747

ASKER

No, while exporting workbook in not open.

alam747

ASKER

Thanks