Access Open Report in Excel and add further formatting

Hi

I need to open an Access report in Excel and then add further formatting to it using VBA code. Is this possible?
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Matt NicholasBusiness AnalystCommented:
Hi Murray

Yes, you can export an MS Access Report following the steps below:

Open the Report within MS Access
Locate the External Data tab and choose Excel
Designate the location and Excel file type, ensure that you tick the "Export Only Selected Records" box if you do not wish to see the full dataset
Format the data as you see fit (in your case with VBA)
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
In order to format, you'll need to use VBA to get a handle on your exported file. You can export the file in numerous ways. If you want to do it manually, then Matt has given you the basic steps to do so. If you want everything in VBA, then you can use TransferSpreadsheet:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "YourTableOrQuery", "ExcelFilePath"

See here for more information: https://msdn.microsoft.com/en-us/vba/access-vba/articles/docmd-transferspreadsheet-method-access

Once you do that, you'd want to connect to the file you just created:

Dim exl As Object
dim wbk as Object

Set exl = CreateObject("Excel.Application")
Set wbk = exl.Workbooks.Open("The same path you used in transferspreadsheet")

From there, you'd use the various formatting methods in Excel to do what you need.

Note this uses Late Binding, in order to avoid any reference issues on deployment. During development, you might want to use Early Binding in order to take advantage of Intellisense. To do that, make a reference to the Excel library on your machine, and change your variable dims to this:

Dim exl As Excel.Application
Dim wbk As Excel.Workbook

You don't need CreateObject, and you may need to preface those with the New keyword.

Just before deployment, remove that Excel reference, and change back to the CreateObject method. Note that if you use an constants that are coming from the Excel reference, you'll have to recreate those in your Access code, or use the actual numeric value for that constant.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.