Access World Forums

Access World Forums (https://www.access-programmers.co.uk/forums/index.php)
-   Modules & VBA (https://www.access-programmers.co.uk/forums/forumdisplay.php?f=12)
-   -   run excel macro from access (report)button (https://www.access-programmers.co.uk/forums/showthread.php?t=28700)

Ziggy1 05-31-2002 06:44 PM

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

scottfarcus 06-01-2002 09:08 AM

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 06-01-2002 04:46 PM

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 03-03-2004 08:16 AM

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 03-03-2004 08:23 PM

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


All times are GMT -8. The time now is 02:49 AM.

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