Activating an unsaved workbook

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
I use a report generator application whose output is an Excel file. After the report is generated, the file isn't saved. How can I get a macro to refer to this file every time I use it? I'd like to use the macro I've created and saved in my personal macro workbook for this.

Also, the report generator (sadly) isn't configurable to use a certain filename, or even to save the file after it has been created, so I can't get to personal.xls from the Tools, Macro, Macros, Run command. I've tried variations on Activata and Window.

Anyone have any ideas? If you need more info, please ask. Thanks very much.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

vconfused

Well-known Member
Joined
Jun 11, 2004
Messages
547
Does the report generator open Excel? If so, does it say Book1 in the Title bar at the top of the Excel screen? If so, can you add Windows("Book1").Activate to the top of your code?
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
It will create a workbook named Book?.xls where ? is a number. If Excel is open when you run it, the next higher number that hasn't been used that session is used. If Excel is closed, the program will open Excel and the file will be named Book1.xls.

However, the only way I can get to the code (that's now PERSONAL.XLS) is if I open it myself, from the workbook that gets created by the program. If there's an instance of Excel already running, PERSONAL.XLS won't be available to the most recent workbook. Make sense?

I think I'll have to look into making an add-in. Thanks for responding, though.
 

vconfused

Well-known Member
Joined
Jun 11, 2004
Messages
547
After the Report Generator runs, it creates a new file and leaves it open (but doesn't save it). When you go to Tools->Macro->Macros, if you click on the "Macros in" drop-down box. Do you not have Personal as an option?

I do not see how the personal.xls file is not available to the latest open file. If I have a personal.xls created, I can open as many Excel files as I want and if I go to Tools->Macro->Macros, Personal is always an option for me.

Where am I going wrong?
 

Todd Bardoni

Well-known Member
Joined
Aug 29, 2002
Messages
3,042

ADVERTISEMENT

Is it opening another insatnce of Excel? Meaning, do you have Excel as an application open twice? This is hard to understand since it seems like Personal. xls should be open and available.
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
The report generator opens its own instance of Excel. Whether this is the first instance or not, I cannot access personal.xls from it. I can access it if I open another instance, but the workbook I need the macro to operate on isn't named as yet.

I'd like to get to the point where I can run the report generator, then run my macro from that instance of Excel (which formats the report and emails it) without having to open another workbook. Have I confused enough people yet? Please let me know if you need further clarification.

Also...unfortunately, the report generator is an exe file that I cannot alter at this point. (I wish!)
 

Todd Bardoni

Well-known Member
Joined
Aug 29, 2002
Messages
3,042

ADVERTISEMENT

I have no idea if this helps, but...

The workbook doesn't need to be saved first. As soon as it opens you can put the name in a variable:-

Code:
Workbooks.Add
myworkbook = Activeworkbook.Name
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Hi Taz,

I have this problem too. It occurs when you open another complete instance of Excel. Not too sure why it happens, but the workaround I've found is to code the workbook to open (as Todd shows) and not an instance (not sure if you are using late or early binding).
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
That would probably work if I could run the report generator from my macro. That I don't know how to do. Right now, I run that program myself, open personal.xls using File, Open since it's not accessible (for whatever reason), then run my macro.

If anyone has suggestions to simplify this process, great! Thanks!
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
If you don't see any of your macros from your Personal.xls file when you press Alt + F8, then your file may not be in your XLSTART folder. Or you are getting the seperate instance of Excel - which is completely seperate from all others.
 

Forum statistics

Threads
1,147,623
Messages
5,742,208
Members
423,712
Latest member
edzubur

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top