run excel macro from access (report)button (1 Viewer)

Ziggy1

Registered User.
Local time
Today, 11:54
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
 

scottfarcus

Registered User.
Local time
Today, 11:54
Joined
Oct 15, 2001
Messages
182
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).]
 

Ziggy1

Registered User.
Local time
Today, 11:54
Joined
Feb 6, 2002
Messages
462
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
 

deekras

Registered User.
Local time
Today, 11:54
Joined
Jun 14, 2000
Messages
169
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?
 

Ziggy1

Registered User.
Local time
Today, 11:54
Joined
Feb 6, 2002
Messages
462
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

Top Bottom