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.ex e"
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???
"C:\Program Files\SAP\Business Explorer\BI\BExAnalyzer.ex
If I have this Bex Excel open and I double click this file:
C:\Documents and Settings\SCUAP\Desktop\bex
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"
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.
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
does it load up?
Rob.
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?
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.
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
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" )?
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")
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.
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.
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
GetObject worked like a charm, thanks for thinking this through with me.
No problem. Thanks for the grade.
If you want the instance visible, change the zero on the last line to a one.
Regards,
Rob.
Open in new window