Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2907
  • Last Modified:

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
0
alam747
Asked:
alam747
  • 5
  • 4
  • 3
2 Solutions
 
Eric ShermanAccountant/DeveloperCommented:
<<<<<<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
0
 
Robberbaron (robr)Commented:
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.
0
 
Robberbaron (robr)Commented:
so you may have to export to 8 workbooks, then combine into a master workbook with 8 sheets.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Eric ShermanAccountant/DeveloperCommented:
Try this ... Including the Sheet Name! with the Range A:A should export the results starting with column A of the existing worksheet.

 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "test_report_01", xlfile, True, "test01!A:AZ"

 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "test_report_02", xlfile, True, "test02!A:AZ"


Etc.

Etc.




ET
0
 
Eric ShermanAccountant/DeveloperCommented:
Rob ...

See my post above.  If you include the SheetName! with a valid Column Range A:AZ like ("test01!A:AZ") then the export will write the data to an existing worksheet in the workbook.

ET
0
 
Robberbaron (robr)Commented:
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.
0
 
alam747Author Commented:
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
0
 
Eric ShermanAccountant/DeveloperCommented:
Use the exclamation point (!) instead of the dollar sign ($) to define the worksheet range as shown below:

"Test01!A:AE"



ET
0
 
alam747Author Commented:
I used ! not the dollar sign but the message shows same as I mentioned.
0
 
Eric ShermanAccountant/DeveloperCommented:
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
0
 
alam747Author Commented:
No, while exporting workbook in not open.
0
 
alam747Author Commented:
Thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now