I need Help Exporting data to Excel

[graviz]

I'm trying to export two queries to two different Excel tabs. Sounds pretty simple right? They are existing tabs in the Excel workbook and when I try it creates a new tab with the title and a 1 at the end of it. I'm using this code below:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Output_SP_30_Day_Cancels", "C:\newtest.xls", True, "Comp%_and_Cancels_By_Month"

I've tried putting a "!" at the end of the name ""Comp%_and_Cancels_By_Month!" but it still doesn't work.

Any ideas?

Thanks!

 

[MSAccessRookie]

I'm trying to export two queries to two different Excel tabs. Sounds pretty simple right? They are existing tabs in the Excel workbook and when I try it creates a new tab with the title and a 1 at the end of it. I'm using this code below:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Output_SP_30_Day_Cancels", "C:\newtest.xls", True, "Comp%_and_Cancels_By_Month"

I've tried putting a "!" at the end of the name ""Comp%_and_Cancels_By_Month!" but it still doesn't work.

Any ideas?

Thanks!

I have a similar report that takes an Access Table that has data for multiple states, and creates a Single Excel Spreadsheet with multiple Tabs, one for each state. I use the same syntax that you do, and I use the parameter in question to pass the name for the new Tab, by setting it to the state code. Is this what you mean?

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, 
        "CreateStateTable", strStateOutFile, True, StatestoCreate!State

In the example above:

• CreateStateTable creates a table of State codes from the data that qualifies for the report.
• strStateOutFile is the name fo the output file, including directory.
• StatestoCreate is a RecordSet that is based on a Query of the State Table.
• State is an element of the RecordSet which is to be used as the name for the new tab.

I hope this is the kind of information that you are looking for.

 

[gemma-the-husky]

i think the bang has to go to the front doesnt it?

!mysheet

 

[graviz]

So "state" is the name of the tab I would like to create. What is the "StatestoCreate"? I see your description below however I'm somewhat new to VBA and I don't understand. What would it be in my example? Also do I need quotes for that part?

 

[MSAccessRookie]

So "state" is the name of the tab I would like to create. What is the "StatestoCreate"? I see your description below however I'm somewhat new to VBA and I don't understand. What would it be in my example? Also do I need quotes for that part?

• StatestoCreate is a RecordSet that is based on a Query of the State Table.
• strTempTableQuery is a String that contains the Query to obtain the list of states.

"Select Distinct State from TempTable Order By State;"
​

It is too often easy to to get too tangled up in the specifics of an example, but you must keep in mind that they might not always apply to you, and that you will probably need to adapt them. What you need to understand about this example is that the last parameter represents the name of the Tab in the SpreadSheet. How you determine its value will be up to you

 

[graviz]

• StatestoCreate is a RecordSet that is based on a Query of the State Table.
• strTempTableQuery is a String that contains the Query to obtain the list of states.

"Select Distinct State from TempTable Order By State;"
​

It is too often easy to to get too tangled up in the specifics of an example, but you must keep in mind that they might not always apply to you, and that you will probably need to adapt them. What you need to understand about this example is that the last parameter represents the name of the Tab in the SpreadSheet. How you determine its value will be up to you

So if the "StatestoCreate" just a field in a query? Is that what a RecordSet is? If I would like everything in the query would I just leavve that part out? Also do I need quotes around it or do I leave the quotes off?