Activating an unsaved workbook

[Tazguy37]

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.

 

[vconfused]

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]

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]

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]

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]

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]

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:-

Workbooks.Add
myworkbook = Activeworkbook.Name

 

[Zack Barresse]

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]

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]

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.