Solved

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

Posted on 2011-02-14
13
1,907 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:atprato
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 65

Expert Comment

by:RobSampson
ID: 34892929
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

0
 

Author Comment

by:atprato
ID: 34893238
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.......
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34893250
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.
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:atprato
ID: 34893462
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?
0
 

Author Comment

by:atprato
ID: 34893468
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.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34893500
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

0
 

Author Comment

by:atprato
ID: 34893602
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")?
0
 

Author Comment

by:atprato
ID: 34893703
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

0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34893796
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.
0
 

Author Comment

by:atprato
ID: 34893856
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?
cmd steps that worked
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 34893862
Right, I see.  In that case, GetObject would be your best bet.  Try it out as a scheduled task and see how you go.

Rob.
0
 

Author Closing Comment

by:atprato
ID: 34893911
GetObject worked like a charm, thanks for thinking this through with me.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34893925
No problem. Thanks for the grade.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question