Link to home
Start Free TrialLog in
Avatar of atprato
atprato

asked on

Open excel file with .VBS file, but not in native excel, open with Custom Excel with add ins

Ok, some workbooks I have to open with a specail instence of excel called the Bex Analyzer:
"C:\Program Files\SAP\Business Explorer\BI\BExAnalyzer.exe"

If I have this Bex Excel open and I double click this file:
C:\Documents and Settings\SCUAP\Desktop\bex test server.xls

It opens the file in the Bex instance of excel which is what I want.

But if I leave Bex Excel open and schedule an XP task to open the file, the task scheduler opens the file in it's own instance of regular Excel, this is no good, I need it to open the workbook in the Bex instance of Excel I already have running.

So my next thought is to have the task scheduler open a .vbs file that will duplicate exactly what happens when I double click the file with Bex Excel already running.  How to write the .vbs file?  I tried the code attached but it just vaporized the file I was trying to open???

OPTION EXPLICIT 

dim ws, file_to_open_path, Bex_Path 

Set ws = WScript.CreateObject("WScript.Shell") 

Bex_Path = "C:\Program Files\SAP\Business Explorer\BI" 

file_to_open_path = CHR(34) & "C:\Documents and Settings\SCUAP\Desktop\bex test server.xls" & CHR(34)

ws.Run CHR(34) & Bex_PATH & "\BExAnalyzer.exe" & CHR(34) & file_to_open_path, 0, "FALSE"

Open in new window

Avatar of RobSampson
RobSampson
Flag of Australia image

Hi, I think you just forgot the space between BExAnalyzer.exe and the file path.

If you want the instance visible, change the zero on the last line to a one.

Regards,

Rob.
OPTION EXPLICIT
dim ws, file_to_open_path, Bex_Path 
Set ws = WScript.CreateObject("WScript.Shell") 
Bex_Path = "C:\Program Files\SAP\Business Explorer\BI" 
file_to_open_path = CHR(34) & "C:\Documents and Settings\SCUAP\Desktop\bex test server.xls" & CHR(34)
ws.Run CHR(34) & Bex_PATH & "\BExAnalyzer.exe" & " " & CHR(34) & file_to_open_path, 0, False

Open in new window

Avatar of atprato
atprato

ASKER

Tried with the space and a 1, vaporized my test file again!  The file just disappeared in front of my eyes.  Not in the recycle bin or anything.......
Wow! That's odd!

If you use a command prompt, and type this:
c:
cd\
cd "program files\sap\business explorer\bi"
BExAnalyzer.exe "C:\Documents and Settings\SCUAP\Desktop\bex test server.xls"

does it load up?

Rob.
Avatar of atprato

ASKER

That vaporized it.  So I guess the good news is the code worked...... LOL!  Ok, maybe instead of trying to figure this out we switch gears?  I've got this custom excel application running so instead of using VBS that runs through the command prompt, what if we use the active excel application object and recreate the code create the steps for file > open from the excel app?  I don't know the code, but if you know what I'm saying, maybe that is a more fail safe approach?
Avatar of atprato

ASKER

To clarify, we will still use the .vbs file, just we change up the code to launch the file from the active custom excel app instead of launching it through the command prompt.  So it will be as if I went to excel and choose file > open > and then browsed to my desktop to open the file.
But you said you need to open the BExAnalyzer first?  Anyway, see what this does.....may BEx will load as an automatic addin....

Rob.
strFile = "C:\Documents and Settings\SCUAP\Desktop\bex test server.xls"
Set objExcel = CreateObject("Excel.Application")
Set objWB = objExcel.Workbooks.Open(strFile, False, False)
objExcel.Visible = True

Open in new window

Avatar of atprato

ASKER

Right, CreateObject gives me a new instance of regular excel.  I want to use the instance of Excel or Bex or whatever it is that I already have open.  How to do exactly what you just did, but instead of using a new instance of regular excel, we want to use the specail instance of excel I already have open.  Any way to do that?  How to say something like useobject ("activeExcel.Application")?
Avatar of atprato

ASKER

Ok, got it.  This uses the active Excel (Bex) application and opens the workbook from there.  No idea why the command prompt did not work?
Set objExcel = getObject(,"Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Documents and Settings\SCUAP\Desktop\bex test server.xls")

Open in new window

Not sure.  Perhaps BExAnalyzer isn't capable of accepting command lind arguments.  Can you run
BExAnalyzer.exe /?
from a command prompt and see if it gives you any options?

If you go with the GetObject approach, that will work, but you will probably have to run the task interactively.

Regards,

Rob.
Avatar of atprato

ASKER

I closed Bex and then did command prompt as in picture below and that launched bex the same way as if I had clicked it in my start > programs list.  Of course, for what we were doing above, bex was already opened, not sure if that mattered.  But even if I could go back and try doing it with Bex closed that would be no good.  I need Bex to already be opened because I had to manually log in after I opened it and this whole thing is for nothing if a I launch the file in Bex without Bex being logged in.  know what I mean?
User generated image
ASKER CERTIFIED SOLUTION
Avatar of RobSampson
RobSampson
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of atprato

ASKER

GetObject worked like a charm, thanks for thinking this through with me.
No problem. Thanks for the grade.