Specify the tab name when exporting to Excel

[lsommers]

Hi, I have a question about exporting an Access query to Excel using VBA.
When I run the code, the Excel workbook that is created defaults to the name of the query.

I use naming conventions for my queries so the tab of the Excel spreadsheet is named "qryProviderAuditExport". I would like to name it "Provider Report". Is there a way to do this.
It is one spreadsheet that is created when the code is run and there is only one tab to worry about. Here is my code so far:

Dim file_name As String
file_name = CirrentProject.Path & "\Submitter_Audit_Report.xls"
DoCmd.OutputTo acOutputQuery, "qryProviderAuditExport", acFormatXLS, file_name, True

If possible, I would like to do this during the export without having the code open the Excel spreadsheet and doing it after the fact.

Thanks!

 

[redalert]

I create a table using the required name, do the export from the table and then delete the table.

You may get a Worksheet name with an underscore "_" instead of a space.

I have'nt yet found a good way of creating a Workbook with a different name to the Worksheet.

One way would be just to rename the Workbook.

 

[namliam]

If you want to export data from access to excel, you can use the Docmd.Transferspreadsheet command.

In the range portion of the command you can specify the sheet name you want to fill....

docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,"YourTable" , "C:\YourExcel.xls", true,"SomeSheetName"

 

[lsommers]

I tried the Docmd.Transferspreadsheet command and it removes all the formatting from the spreadsheet. So, the field names are not gray or bold and all the percents are decimals with no percent sign. If I could use this method and keep the formatting then I would try this.

I will try the create table then delete table method offered by redalert. I don't want to rename my query because of the naming conventions that I use. I'm just nerdy that way.

Thanks for the suggestions.

 

[fcarboni]

Hi Isommers,
I think that you've two ways: rename your query or rename the name sheet/sheets.
If your way is the 2th, you've only to put this rows after your code, in the same sub:

Dim xlApp As Object
Dim xlSheet As Object
Dim file_name As String
Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(file_name)
With xlSheet
.Sheets("[COLOR=red]qryProviderAuditExport[/COLOR]").Name = "[COLOR=red]Provider Report[/COLOR]"
End With
xlSheet.Save
xlSheet.Close

Fabrizio

 

[deisouss]

Actually the answer above it's incorrect, as specified in MS documentation Range :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.

One solution that it's easier than running the code to open the excel file and rename the sheet, just rename the table before exporting like this:

DoCmd.Rename "Sheet1", acTable, "Current_table_name"

Then after exporting it, reverse it

DoCmd.Rename "Current_table_name", acTable, "Sheet1"

If you are using a Query then you can use acQuery instead of acTable

 

[Uncle Gizmo]

I helped an OP export MS Access data into MS Excel sheets, and name the sheets. I blogged about it on my website here:-

Make Excel Sheets From Access Table - Nifty Access

Excel Sheets From Access Table If you've ever faced the challenge of exporting data from Microsoft Access into Excel, you’re not alone! One of the most common requests is to organize data by categories, like exporting car makes into separate Excel sheets or even separate Excel files. I’ve...

www.niftyaccess.com

I used some excellent code from Tab developments. You can find a link to the code in My Blog. There is also a video demonstrating the process.

 

[Uncle Gizmo]

YouTube Here:-

Export Query into Separate Excel Sheets​

This is well worth watching! It's Fascinating!

Make Hundreds of Excel Sheets From MS Access Table​

 

[GPGeorge]

I tried the Docmd.Transferspreadsheet command and it removes all the formatting from the spreadsheet. So, the field names are not gray or bold and all the percents are decimals with no percent sign. If I could use this method and keep the formatting then I would try this.

I will try the create table then delete table method offered by redalert. I don't want to rename my query because of the naming conventions that I use. I'm just nerdy that way.

Thanks for the suggestions.

When I needed to export data into Excel and have it arrive with a predetermined, specific format, I created an Excel template with two worksheets:

A Display sheet
A Landing sheet.

The data was exported into ranges in the landing sheet.

The formatted cells in the Display sheet were linked to the corresponding ranges in the Landing sheet.
The data was DISPLAYED as needed, and the landing page was hidden.

We created thousands of reports using that method over the years. The template had to be tweaked two or three times over the years as requirements changed, but it worked perfectly for months at a time with no human intervention.

 

[theDBguy]

Actually the answer above it's incorrect, as specified in MS documentation Range :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.

One solution that it's easier than running the code to open the excel file and rename the sheet, just rename the table before exporting like this:

DoCmd.Rename "Sheet1", acTable, "Current_table_name"

Then after exporting it, reverse it

DoCmd.Rename "Current_table_name", acTable, "Sheet1"

If you are using a Query then you can use acQuery instead of acTable

@deisouss, Welcome to AWF!

Just FYI, you were responding to an almost 10-year old thread.

 

[deisouss]

@deisouss, Welcome to AWF!

Just FYI, you were responding to an almost 10-year old thread.

Yes, I know, the problem still exists now, 2022 and you cannot specify the sheet name, I was doing research to get solutions, I found this thread, I came up with this solution that worked for me and I figured I would share it for others, that's all

 

[theDBguy]

the problem still exists now, 2022 and you cannot specify the sheet name

Actually, you can - as already described above. Have you tried using the Range argument? Don't listen to what the Help file say - just try it.

 

[deisouss]

Actually, you can - as already described above. Have you tried using the Range argument? Don't listen to what the Help file say - just try it.

I had tried it before and it was giving me an error, I used the long form Range:="Sheet 1" and it worked, but it brought me back to another issue that I had while trying different ways to do it, if the sheet name has a space, it will change the space with an underscore.

Try Range:="Sheet 1", you will get Sheet_1, do you know a work around for this?

 

[theDBguy]

I had tried it before and it was giving me an error, I used the long form Range:="Sheet 1" and it worked, but it brought me back to another issue that I had while trying different ways to do it, if the sheet name has a space, it will change the space with an underscore.

Try Range:="Sheet 1", you will get Sheet_1, do you know a work around for this?

My guess is that's an Excel thing, so I can't blame Access for it. Which means, anything you do from the Access side will probably be ignored by Excel when it gets the request. The only workaround I can think of right now is to automate Excel to rename the Sheet after the export. If I find anything else, I'll let you know.