run excel macro from access (report)button

Ziggy1

Registered User.
Local time
Today, 15:14
Joined
Feb 6, 2002
Messages
462
I have a button on a form in Access which previews a report. My problem is I have to refresh my data first via Excel Macro to update a linked table(s). I currently switch to excel to run macro, when complete I return to Access and preview the report.

How can I call the macro from my Personal.xls and when complete, return to access to complete the rest of the code to preview the report.

Access 97 code for report button:
======================================
Private Sub Command8_Click()
On Error GoTo Err_Command8_Click

Dim stDocName As String

stDocName = "FULL INTRANSIT Report"
DoCmd.OpenReport stDocName, acNormal

Exit_Command8_Click:
Exit Sub

Err_Command8_Click:
MsgBox Err.Description
Resume Exit_Command8_Click

End Sub
=========================================

I figure if it was placed at the beginning of my code for the button it would call up the macro and run it and then move on when complete. But this is the syntax I use in a workbook code that calls a macro.

This is the name of my Macro:
==================================
Application.Run "PERSONAL.XLS!Intransit_Header_Detail2"

please help, it would save me so much time and ensure my data is refreshed.

Thanks

Ziggy
 
Go into the code block for the command button.

Go to Tools-References.

Check the box next to the Microsoft Excel Object Library (10 for XP).

Enter the following code into the on click event for the command button:

Private Sub Command8_Click()
On Error GoTo Err_Command8_Click
Dim xlsApp As Excel.Application
Dim xlswkb As Excel.Workbook
Set xlsApp = CreateObject("Excel.Application")
Set xlswkb = GetObject("C:\Personal.XLS")

xlsApp.Application.Run "PERSONAL.XLS!Intransit_Header_Detail2"

xlsApp.Quit

Dim stDocName As String

stDocName = "FULL INTRANSIT Report"
DoCmd.OpenReport stDocName, acNormal

Exit_Command8_Click:
Exit Sub

Err_Command8_Click:
MsgBox Err.Description
Resume Exit_Command8_Click

End Sub

[This message has been edited by scottfarcus (edited 06-01-2002).]
 
Great! Thanks Scott, I will have to wait until I get back to work to see if I can get it to work on my database, I have Access 2000 at home so I will also try it here.

Thanks again

Ziggy
 
I am trying to do the same thing - but i run into a problem.

my macro in excel is called "PERSONAL.XLS!m"

i get an error message 'PERSONAL.XLS' could not be found. Check the spelling ..."

what am i doing wrong.

secondly, can i run an excel macro on a .txt file or do i have to change it to a .xls file first?
 
deekras said:
I am trying to do the same thing - but i run into a problem.

my macro in excel is called "PERSONAL.XLS!m"

i get an error message 'PERSONAL.XLS' could not be found. Check the spelling ..."

what am i doing wrong.

secondly, can i run an excel macro on a .txt file or do i have to change it to a .xls file first?


Hi,

Personal.xls already resides on your machine, did you try to name a new one?

"PERSONAL.XLS!m"


2.

if you record a macro going through the text import wizard it will record all the steps.

*Start recording before you select the text file
 

Users who are viewing this thread

Back
Top Bottom