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,857 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
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Make a Cell act like a Date 7 40
Excel Question 17 15
Sum 2 rows formula - months 6 15
How to keep previous peak or trough values for a range. 3 14
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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 …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

832 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