Export or Import to Excel and Format

[Guest]

What would be the easiest way to either import from Excel (an Access report)
or Export from Access a report to Excel?

Once the report is either exported or imported into Excel. I need it to be
formatted and set up with the correct tabs. Getting it into Excel with the
correct format is my main goal right now so I will start from there. I have a
button on my switchboard when a user clicks it I want it to either export or
import the updated data into an UPDATED REPORT into Excel.

I have done exporting but it does not update and always shows the same data
and the formatting is always a mess. Any help would be appreciated.

 

[Guest]

I forgot to mention that the Tabs I would like to set up are from the
subforms of the report and not the main form. When I export to excel it takes
the main form report first then puts the subreports down towards the bottom
right of all the data on the workbook. I need to take the subreprts and put
into seprate tabs. If I only have one sureport I only need one tab etc.
Thanks again

 

[Guest]

Reports don't really export to Excel very well. Every one I have ever seen
is really ugly. In addition, you will not be able to specify tabs when
exporting a report to Excel.

The better approach would be to create queries that will provide the data
you need. You will want one query for each tab. The use the
TransferSpreadsheet method to export the queries and use the Range argument
of the TransferSpreadsheet to specify the tab name.

As to any additional formatting, you will either have to do that in Excel or
you can use Automation to open the spreadsheet as an Excel Object in Access
and use VBA to set the formatting of each sheet in the workbook.

 

[Guest]

Thank you great start. By the way on my main form (first tab) it will not
allow me to change the tab. It puts the name of my query as the first tab. If
I try and add it into the Range section it just adds another tab with the
same data into the workbook. Any ideas? Thanks again

 

[Guest]

Is sounds like you are exporting into an existing Excel file. That is the
behaviour you would expect.