asked on

Export from Access to Excel in VBA with some formatting

Hello All

I have to export data from an Access table to an Excel spreadsheet. I did a simple DoCmd.TransferSpreadsheet acExport but now the user wants some formatting.

When I do the exporting now it exports just fine, but the columns are not wide enough in some locations. Personally I would think that he could simply doubleclick to widen them but he doesn't want the bother.

I remember I did this once before a slightly different way where I set up an excel spreadsheet like a template and just filled in a range (Like A1:A15). The problem with this is that the amount of rows in this spreadsheet could fluctuate between 1 and around 50.

Additionally He would like a total produced and some text to show up under the individual lines.

Ideally by pressing a button in the Access database it would create the xls file completely formatted.

Is any of this possible?

Norie

All of it is possible.

If you want to stick with a button in Access you could use you existing DoCmd.Transferspreadsheet to export the actual data and then open the exported workbook to apply the formatting/totals etc you want.

Can you post the code you have now and details of the totals etc you want?

alevin16

ASKER

Hello Norie

Here is the code I have now:

stdocname = "IPG Sent Excel"
DoCmd.TransferSpreadsheet acExport, , stdocname, txtPSFileLocation.Value & txtPSFileName.Value & ".xlsx", False

The IPG Sent Excel is just a simple select query that pulls records from the table based on a group ID. LIke I mentioned before, there are usually between 1 and 50 records.

I created a sample of what the user would like to see in Excel (I just typed it in manually)
Sample.xlsx

Norie

How many fields are in the query 'IPG Sent Excel'?

Ralf Klatt

Hi,

I've just tried to figure out the scope of your issue. (Me) Haven't been working since about decade ... I'd just like to mention that ("in the old days") me and others have always preferred to prepare data in a database (ACCESS ... SQLSRV even *.txt) ... and then leverage it with some pieces of code from "written" applications (VB, C#) ... or, leverage it from Office-VBA ... straight called from (any!) DB to Excel.

I was prepared, ready and willing to open your sample, did it, and was just able to be seeing the top ... warned by my systems security! Why don't you just send a sample data set and your developed code so that it could be reviewed?

Best regards,
Raisor

Jeffrey Coachman

Without a sample of your Access table, and your exported file, and the exact formatting required, ...we are at a disadvantage.

I have to export data from an Access table to an Excel spreadsheet. I did a simple DoCmd.TransferSpreadsheet acExport but now the user wants some formatting.

Then why not consider doing the formatting in Access?

JeffCoachman

alevin16

ASKER

Hello All

Sorry for the delay, we had a crisis here with a server crash (thankfully not mine :D) so I pitched in to help. I will create a quick sample database and hopefully that will work.

Andy

alevin16

ASKER

I created a sample database. It has the three tables that the query uses. I put the code into Form1's button (but I disabled the actual docmd so that it does not try to print)

I have also attached a sample of the output he would like to see (ignore the border lines, that is not important). The difference between what I have attached and what the system creates is basically the column formatting and the totaling.

Thanks!
Sample-One.accdb
Sample-One-output.xlsx

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.