Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 7 votes, 5.00 average. Display Modes
Old 05-31-2002, 06:44 PM   #1
Ziggy1
Newly Registered User
 
Join Date: Feb 2002
Location: Ontario, Canada
Posts: 462
Thanks: 2
Thanked 1 Time in 1 Post
Ziggy1 is on a distinguished road
run excel macro from access (report)button

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

Ziggy1 is offline   Reply With Quote
Old 06-01-2002, 09:08 AM   #2
scottfarcus
Registered User
 
Join Date: Oct 2001
Posts: 182
Thanks: 0
Thanked 0 Times in 0 Posts
scottfarcus
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).]
__________________
Scott
Feel free to email!

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


scottfarcus is offline   Reply With Quote
Old 06-01-2002, 04:46 PM   #3
Ziggy1
Newly Registered User
 
Join Date: Feb 2002
Location: Ontario, Canada
Posts: 462
Thanks: 2
Thanked 1 Time in 1 Post
Ziggy1 is on a distinguished road
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

Ziggy1 is offline   Reply With Quote
Old 03-03-2004, 08:16 AM   #4
deekras
Registered User
 
Join Date: Jun 2000
Location: brooklyn
Posts: 169
Thanks: 0
Thanked 0 Times in 0 Posts
deekras
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 is offline   Reply With Quote
Old 03-03-2004, 08:23 PM   #5
Ziggy1
Newly Registered User
 
Join Date: Feb 2002
Location: Ontario, Canada
Posts: 462
Thanks: 2
Thanked 1 Time in 1 Post
Ziggy1 is on a distinguished road
Quote:
Originally Posted by deekras
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

Ziggy1 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 12:15 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World