Exporting Access Data into Excel spreadsheet

[Kayleigh]

Hi,

I have a complex database system which currently outputs the data in a number of very detailed forms. I would like to have a button on the form to export this to an Excel spreadsheet whilst substituting the complex details with specific codes and apply formatting for certain situations. What function do I use to do this?

Also is it possible to automatically add comments to certain cells?


Thank you
Krayna

 

[theDBguy]

Hi. To do all that (formatting, comments, etc.), you may have to use Excel Automation.

I don't know of any one function that will do all that. If you simply want to export the data to Excel, you can take a look at the TransferSpreadsheet method.

 

[Kayleigh]

Can you direct me to specific guidance on Excel Automation?

 

[theDBguy]

Can you direct me to specific guidance on Excel Automation?

You could start with this one.

How to automate Microsoft Excel from Visual Basic - Office

Demonstrates how to create and manipulate Excel by using Automation from Visual Basic.

docs.microsoft.com

 

[Kayleigh]

Great. Will this work directly in Access VB or would the syntax vary?

 

[The_Doc_Man]

I'm going to add my voice in agreement with theDBguy. To do simple transfers, you use TransferSpreadSheet. To do formatted transfers you have to go through an Excel Application Object. To add comments into the cells, you MUST use the App object.

 

[theDBguy]

Great. Will this work directly in Access VB or would the syntax vary?

Hmm, good question. Looking at the sample code in that page, I don't see why it won't work in VBA. But if it doesn't, we can point you to other examples on the Net. Cheers!

 

[The_Doc_Man]

Great. Will this work directly in Access VB or would the syntax vary?

As it happens, Access VBA isn't Access VBA. It is OFFICE VBA, and all members of MS Office that have the ability at all will use the same syntax. The only thing that differs between Access VBA and Excel VBA and Word VBA is the syntax of implied references and when you can use them. It is mostly a matter of having the correct references. By default, Access and Excel have different library references. Once you get those straight, it is a reasonably homogeneous environment.

 

[Uncle Gizmo]

As it happens, Access VBA isn't Access VBA

A recent change, I note that Microsoft help now refers to VBA as VB ....

 

[Uncle Gizmo]

Can you direct me to specific guidance on Excel Automation?

I have used this code from Bob:-
https://btabdevelopment.com/export-a-table-or-query-to-excel/

To move data from MS Access to Excel.

I have also made some nice products with it, in particular, this one:-

"Show/Hide & Export to Excel"

Show-Hide-Export to Excel - Nifty Access

Show/Hide & Export to Excel What started out as an answer to a question on Expert Exchange, took on a life of its own and became a pop-up form which allows you to select the columns you want to display in a datasheet. Now, this is not at all a groundbreaking technology! It's actually already

www.niftyaccess.com

Which I'm quite pleased with.


Download Here:-

Nifty Show/Hide & Export to Excel - MS Access

The download contains a working example of a pop-up form which allows you to show and hide various columns in a datasheet. It also has the ability to export data from the datasheet to Excel. Once you have selected the columns you want to export you have the option of applying a filter and sort...

gum.co

If you want a free copy of the code, contact me for details on how to get free copy....

 

[Uncle Gizmo]

Here's another example of what can be accomplished with Bob's Code:-

This 1:41 min video is fascinating to watch!!!!

 

[Kayleigh]

Thanks for all your suggestions!
@UncleGizmo - I finally got round to looking at your code here. It looks so good - I purchased and downloaded it!
However I am having trouble implementing in my DB... Main problem being that it very difficult to have datasheet as a subform (split form) since it is being called and filtered from a previous form. Is there any way I can have the macro on same form as datasheet?
Another query: I was inspired by @UncleGizmo's demo clip above - so how is each sheet renamed - I was thinking of exporting based on current filter, with option of output all options - each on new sheet.
Final point - code should produce new Excel file and offer user where to save.

Many thanks!
Ps. Have signed up to your newsletters

 

[Uncle Gizmo]

However I am having trouble implementing in my DB... Main problem being that it very difficult to have datasheet as a subform (split form) since it is being called and filtered from a previous form. Is there any way I can have the macro on same form as datasheet?

I've had a look at your database and it needs a lot of reworking to get it to something that can be useful to you in MS Access. Now don't get me wrong, you are going in the right direction, however you have quite a bit of duplication and there are some techniques that you don't know about, which you could employ to make your form much simpler.

Instead of having the lookups as queries you could just have a combobox on your form which would provide the same functionality. This would simplify the SQL statement for the form.

And the code that is operated by the SQL statements and the option group, this could be made into one single function. However it's not a problem how you've got it, it's just an improvement that would provide some useful gains in your a database design, especially if you intend adding further option control selections in the future.


I would suggest you posted the different aspects of your database on the forum and ask advice on how to change the structure into A more suitable structure.

 

[Jjaeger14]

I see you've gotten enough suggestions on VBA automation of Excel to keep you going. But might I suggest another, much simpler approach? Create an Excel Template file. Start your code by copy/pasting your excel template. For example: filecopy "c:\exceltemplate.xlsx", c:\exceltemplate - " & format(date,"mmddyy") & ".xlsx". Then proceed with either the standard docmd.transferspreadsheet ... command into the excel file you just created. The advantage? You can perform all of your desired formatting including fancy conditional coloring of excel cells all within Excel where you are most likely more familiar than the excel automation formatting commands.

But if you are brave and love learning, then by all means go the excel automation route. I've certainly done that many times myself.

Jack

 

[Kayleigh]

Great idea @Jjaeger14 !

@Uncle Gizmo - can you be more specific where I can improve my database?
Would really like to do it right
Appreciate all constructive criticism!

 

[Jjaeger14]

UncleGizmo's comment: "Instead of having the lookups as queries you could just have a combobox on your form" means in the query above, you don't need either the outer join to 'qrylkpStaffName' nor join to 'lkptblStaffyType. Both of these complicate your form unnecessarily and could cause problems later on. Whatever information you need in these tables can be handled with a combo box on your form. for qrylkpStaffName information, Set the control source of the combo box to 'fldStaffID' and the rowsource to qrylkpstaffname. make it a 2 column combo box and set the column widths to 0,2. Your combo box will then display the staffname on the form, but store the 'fldstaffid' value in qrySCR!fldstaffid. Use the same technique on lkptblStaffType. One of my goals in developing databases is to have a single table for each form. I avoid multitable recordsources

 

[Kayleigh]

Simple enough! I can see now it really wasn't necessary to add extra lookup tables.
Thank you

 

[Jjaeger14]

Simple enough! I can see now it really wasn't necessary to add extra lookup tables.
Thank you

Actually, a database like this would typically keep the lookup tables in order to populate the combo boxes with a unique list of values, thus restricting data entry to the list (data validation). But maybe for your purposes this isn't necessary.

Jack

 

[Kayleigh]

Thanks for sharing your DB - very generous and I'm sure will come in useful
Can I mention that the data in yours is quite static but I have a lot of interrelated data which changes acc to user input - the following object must pick up this data and utilise immediately. This is where I am having most trouble coding...