Cannot Utilize a Macro When Excel Is Opened By Another Program

[Jim Chesher]

I have created a macro in the Personal.xls workbook that is located in my
XLStart folder that works as I desire whenever I open a workbook. I have a
second program that manipulates various Access databases and will
automatically export an Excel spreadsheet of data by pressing one key. My
problem is that when this second program automatically open a spreadsheet
with the data in it I can not execute the macro for some reason. I am using
Microsoft Office 2003. Any help is appreciated.

 

[Jim Chesher]

Doesn't anyone have any idea or info on this issue?

 

[Gord Dibben]

What is the "other program" that manipulates various Access databases?

To where does the Excel spreadsheet of data get "exported" when you press one
key?

Is a spreadsheet a workbook or a worksheet?

You speak of both spreadsheets and workbooks so I can't tell which is which.

How does the second program automatically open a spreadsheet(workbook?)?

Which macro can you not execute?


Gord Dibben MS Excel MVP

 

[Jim Chesher]

Gord, Thanks for the reply. I will try to answer your questions in order
asked.
The other program is a business related software call Maintenance Support
Tool (MST) that was privately developed.

The data from MST exports to sheet #1 of a new workbook that just opens on
my desktop.

As stated above it is actually sheet #1 of a workbook.

I did not write the other program so I do not know how it opens up the
spreadsheet.

Here is a cut and paste of the macro. All it really does is selects row 1
and applies Bold, Underline and Center. Then selects all and formats all
columns for auto-fit.
Sub Ashland()
'
' Ashland Macro
' Macro recorded 1/20/2008 by James W. Chesher
'
' Keyboard Shortcut: Ctrl+a
'
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
Cells.Select
Selection.Columns.AutoFit
Rows("1:1").Select
Selection.Font.Bold = True
Selection.Font.Underline = xlUnderlineStyleSingle
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub

Jim Chesher

 

[Gord Dibben]

This macro is in your Personal.xls workbook which you know is open?

Perhaps due to the method that MST creates a new workbook and opens Excel,
Personal.xls is not opening?

Which workbook is active when you try to run the macro?

Which worksheet is active?

The macro as written will act upon the activeworkbook and activesheet.


Gord

 

[Jim Chesher]

It doesn't seem to make any difference if I have the Personal.xls open or
not when MST creates the workbook. I agree that the workbook that MST opens
in not opening Personal.xls. When I open a blank workbook and I go to macro
I can see the macro (called Ashland). In the dropdown box Macros In I can
select All Open Workbooks, This Workbook, Personal.xls or Book 1. When I
create the workbook from MST and go to macro I can not see the Ashland
macro plus the only options I see in the Macros In dropdown box are: All
Open Workbooks, This Workbook and Book 1. Personal.xls is not available. I
am stumped .

 

[Gord Dibben]

Jim

Looks like MTS may be starting Excel in "safe mode" which would stop
Personal.xls from opening.

Hit Alt + F11 to open the Visual Basic Editor.

Alt + r to open the Project Explorer.

What projects/workbooks are open?

Is Personal.xls one of them?

Also, in the Excel window hit Help>About MS Excel>Disabled Items.

Personal.xls in there?


Gord

 

[Jim Chesher]

Gord,
There are no projects/workbooks open after hitting Alt+F11 and then Alt+r
and then selecting Run Macro. There are no disabled item.

Jim

 

[Gord Dibben]

I don't know what is going on but if MTS creates a workbook there should be at
least one workbook/project in the VBE

i.e. the workbook just created.

BTW...........why are you selecting Run Macro after hitting Alt + r


Gord

 

[Jim Chesher]

After I hit Alt +F11 and open the VBE and then press Alt+r I do not get
Project Explorer. All I get is a dropdown menu with the following choices;
Run Macro, Break, Reset or Design Mode. Hitting Alt+r is the same as
selecting Run from the top menu. If I save the file that MST created as,
say, Book1 and then close it and reopen it then the macro works fine. So I
guess I will just have to either manual do the stuff the macro does or save
and reopen the files to apply the macro. Thanks for your help.

 

[Gord Dibben]

Please accept apologies with a sheepish grin and downcast eyes.

CTRL + r to open Project Explorer.


Gord

 

[Jim Chesher]

That makes a difference. Now when I create a workbook via MST and then hit
Atl + F10 and then CTL + r I get VBA Project Tree that has the following:
-VBA Project (Book 1)
-Microsoft Excel Objects
Sheet 1 (Sheet 1)
Sheet 2 (Sheet 2)
Sheet 3 (Sheet 3)
This workbook

There is no Personal.xls

 

[Gord Dibben]

Is Personal.xls in your XLSTART folder for sure?

If so, MST must be starting Excel in safe modewhich by-passes XLSTART folder.

I would follow up with my IT people or whoever is respnsible for MST


Gord

 

[Jim Chesher]

Yes the Personal.xls in the XLSTART folder. I will check with the MST folks
about it starting Excel in the safe mode. Thanks for you assistance.