Open Excel Workbook and run macro stored in Personal.xls

[Nokose]

I am working on email an excel report out. I need to format the report before it is sent. I have created a macro in my personal workbook. I would like access to be able to open the report use the macro form the personal book and then save it. SO far I am stumped. Here is what I have so far:

Dim appXL As Excel.Application
Dim wbk As Excel.Workbook

Set appXL = New Excel.Application

Set wbk = appXL.Workbooks.Open("s:\hris\badge report\Daily Badge Number Check.xls")

appXL.Run wbk.PERSONAL.XLS & "!BadgeNumber"
wbk.Close True
appXL.Quit

Any help would be appreciated.

 

[xenou]

1. You don't want to assume personal.xls is open (but it might be....not sure if the startup would ignore opening personal.xls if programmatically opened)...

2. Also, need to change the syntax to run the macro...

Dim appXL As Excel.Application
Dim wbk As Excel.Workbook

Set appXL = New Excel.Application

Set wbk = appXL.Workbooks.Open("s:\hris\badge report\Daily Badge Number Check.xls")

[COLOR="SeaGreen"]'//Make sure personal.xls is open[/COLOR]
[COLOR="Blue"]On Error Resume Next
varTemp = Workbooks("PERSONAL.xls").Name
If Err Then
Workbooks.Open ("C:\folder\folder\PERSONAL.xls")
End if
On Error Goto 0[/COLOR]

appXL.Run ("PERSONAL.xls!BadgeNumber")
wbk.Close True

appXL.Quit

Not tested....
Edit: And of course substitute the real full path to your Personal.xls - not my folder\folder stuff

 

[Nokose]

The script is workig and calling the macro. Thank you.

Now it is leading to another issue. I have tried to find it in Excel but am unable to. Here is the issue.

When it runs this macro and it tries to close and save it I get a box saying it is an older format and if I want to save and overwrite it. Of course I do. Then it comes up with another box asking if I want to save the clipboard. Anyone know how to get these two prompts to clear out automatically? Any help is appreciated.

 

[xenou]

The first message...not sure...seems like it should go away after you say yes...try to get the workbooks all in the same format is my advice (if this is possible)

The second, you probably have copied something in your macro that you run and the data is on the clipboard. Try putting this line:

Application.CutCopyMode = False

At the end of the macro "BadgeNumber"

 

[Nokose]

I will try the False. The report format comes from Access. Access creates the excel book froma query before it is formatted. What is the code for the most recent format form Access?

 

[Nokose]

I have got it all to work. The code below opened excel ran a macro stored in the Personal WOrkbook and then shut it down overwrtiing the file and disregarding any other warnings that pop up. Hope this helps someone else.

Private Sub Form_Open(Cancel As Integer)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
DoCmd.OutputTo acOutputQuery, "Badge Change", "MicrosoftExcelBiff8(*.xls)", "s:\hris\badge report\Daily Badge Number Check.xls"<o></o>
<o></o>
Dim appXL As Excel.Application<o></o>
Dim wbk As Excel.Workbook<o></o>
<o></o>
Set appXL = New Excel.Application<o></o>
<o></o>
Set wbk = appXL.Workbooks.Open("s:\hris\badge report\Daily Badge Number Check.xls")<o></o>
<o></o>
'//Make sure personal.xls is open<o></o>
On Error Resume Next<o></o>
varTemp = Workbooks("PERSONAL.xls").Name<o></o>
If Err Then<o></o>
Workbooks.Open ("C:\Documents and Settings\tibbja\Application Data\Microsoft\Excel\XLSTART\PERSONAL.xls")<o></o>
End If<o></o>
On Error GoTo 0<o></o>
<o></o>
appXL.Run ("PERSONAL.xls!BadgeReport")<o></o>
Excel.Application.DisplayAlerts = False<o></o>
<o></o>
wbk.Close True<o></o>
<o></o>
appXL.Quit

<o></o>

 

[xenou]

Thanks for sharing your experience.

Keep Excelling!