• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2025
  • Last Modified:

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

0
atprato
Asked:
atprato
  • 7
  • 6
1 Solution
 
RobSampsonCommented:
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
 
atpratoAuthor Commented:
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
 
RobSampsonCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
atpratoAuthor Commented:
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
 
atpratoAuthor Commented:
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
 
RobSampsonCommented:
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
 
atpratoAuthor Commented:
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
 
atpratoAuthor Commented:
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
 
RobSampsonCommented:
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
 
atpratoAuthor Commented:
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
 
RobSampsonCommented:
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
 
atpratoAuthor Commented:
GetObject worked like a charm, thanks for thinking this through with me.
0
 
RobSampsonCommented:
No problem. Thanks for the grade.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now