Iphone

I have been tasked with creating a report in MS Access and creating an identical report in MS Excel.

I had no trouble creating/formatting the MS Access report but "exporting" it to Excel proved problematic.

I learned that it's not possible to do a quick-and-dirty export so I decided I would use an Excel macro to take the horribly out-of-whack spreadsheet that resulted from the MS Access report and output it in a more presentable format.

So, at present these are the steps in this process:

1. Run report (rpt_loans) in MS Access
2. Tools --> Office Links --> Analyze it with Microsoft Excel
3. Excel file opens (rpt_loans.xls)
4. I need to open the Excel file (called "loansrpt_macro.xls") in which I wrote the formatting Macro
5. alt+tab from loansrpt_macro.xls to rpt_loans.xls
6. hit alt+F8, make sure "All Open Workbooks" option is chosen from the "Macros in:" drop down menu
7. click "Run"

As you can see this is a bit of an involved process. It's not at all difficult for me or any minimally computer savvy person to do but because many different people in my company need to access this report in both Access and Excel format, I'm looking for a way to make the process simpler and more automated.

Any suggestions as to how I can have this done in a quicker and easier way? *Ideally*, I would like to create a button in Access that actually does steps 1 through 7 above, it runs the Access report, exports it to Excel and runs the loansrpt_macro.xls Macro on the exported file, resulting in a clean and prepared Excel spreadsheet ready to be used.

Anybody have any idea if this is possible and, if so, how I can do it?

Iphone

You can try something like the following. This assumes that your file loansrpt_macro.xls resides in a work directory, in which the reports will be created also.

Note that you can change your Excel file to an xla, so that you can then run macros from it without specifying the name of the file each time, but that isn't very important. Note also that you can save your excel file in hidden mode ("window / hide") so that it won't be visible to the users.

Make sure you add a reference to the excel library (tools / references) and start with this Access module:

-------------------------------------------------------------------------------------
Option Compare Database
Option Explicit

Const cWorkDir = "C:\documents and settings\broohaha9\desktop\"
Const cTools = "loansrpt_macro.xls"

Function LoadTools(pappXL As Excel.Application) As Boolean
'
' Attempt to run a dummy macro from my excel tools workbook.
' On failure, attempt to open the workbook and try again.
'
On Error Resume Next
pappXL.Run cTools & "!DummyMacro"
If Err Then
Err.Clear
pappXL.Workbooks.Open cWorkDir & cTools
pappXL.Run cTools & "!DummyMacro"
End If

LoadTools = (Err.Number = 0)
If Err Then Err.Clear

End Function

Sub ExcelReport()
'
' Open report, export to Excel and run a formatting macro.
'
Dim xlwkbReport As Excel.Workbook

' change to work folder:
ChDir cWorkDir
' open report, export, and close
DoCmd.OpenReport "rpt_loans", acViewPreview
RunCommand acCmdOutputToExcel
DoCmd.Close acReport, "rpt_loans"

' get handle to workbook and run formatting macro
Set xlwkbReport = GetObject(cWorkDir & "rpt_loans.xls")
If Not LoadTools(xlwkbReport.Application) Then Exit Sub
xlwkbReport.Application.Run cTools & "!FormattingMacro"

End Sub
-------------------------------------------------------------------------------------

Your Excel module (in loansrpt_macro.xls) would be build according to this:

-------------------------------------------------------------------------------------
Option Explicit

Sub FormattingMacro()

Windows("rptForExcel.xls").Activate
' recorded formatting instructions from here...

End Sub

Sub DummyMacro()
' do nothing...
End Sub
-------------------------------------------------------------------------------------

I believe the above covers your points 1 through 7.
Happy programming!