Specify open file with Excel 2007
in [VbScript]
|
From: xp on 21 May 2010 10:46 Thanks again Tom, I will give this a try... I've never seen: & "/automation -Embedding" Appended like that...is it documented somewhere? "Tom Lavedas" wrote: > On May 19, 6:45 pm, xp <x...(a)discussions.microsoft.com> wrote: > > Hi Tom, > > > > Thanks for the idea; however, I tried this and it didn't work...any other > > ideas? > > > > "Tom Lavedas" wrote: > > > On May 19, 10:05 am, xp <x...(a)discussions.microsoft.com> wrote: > > > > I have both Office 2003 and 2007 dual installed on my PC. > > > > I need to point my script to open certain files using 2007, but it defaults > > > > to 2003. > > > > Can someone please help me out with this? How can I specify for the script > > > > to use 2007 when opening an Excel file? > > OK, the next best thing I can think of is to try forcing Excel2007 to > open and then load your file into it, something like this ... > > ' Next line needs to point to the location for your environment > sXLPath = """C:\Program Files\Microsoft Office\Office12\Excel.exe"" "_ > & "/automation -Embedding" > sFilePath = "C:\Someplace\YourFileName.xls" > set oWSH = CreateObject("WScript.Shell") > nRes = oWSH.Run(sXLPath, 1, False) > if nRes <> 0 then > wsh.echo "Error opening Excel", nRes > wsh.quit > end if > On Error Resume Next > set oXL = GetObject("","Excel.Application") > if err.number <> 0 then > wsh.echo "Error opening Excel", hex(err.number), err.description > wsh.quit > end if > On Error Goto 0 > oXL.visible = True > > set oWkBk = oXL.WorkBooks.open(sFilePath) > ' The rest of your script ... > oWkBk.Sheets(1).cells(1,1).value = "Done" > > All of the documentation says that leaving the first argument empty > (i.e. GetObject( ,"Classname")) is supposed to attach to the running > object. However, on my XP machine, this approach is throwing a > runtime syntax error. So, I added an empty string, but I am assuming > that since Excel is a Single Instance application, the returned > automation object must be the 2007 object. As I said, I don't have it > to try. If I am correct, the Open() should then load the file into > the correct (2007) version of XL. It does work for the 2003 version I > was forced to test with. > > Hope it works. > _____________________ > Tom Lavedas > . >
From: xp on 21 May 2010 11:00 When I open files manually (by double-click in Windows Explorer) it opens 2007 files in 2007; it also opens 2003 files in 2003. But when I open using VBScript it always opens in 2003. I tried using folder options as you suggested and that did not fix the issue. "ekrengel" wrote: > On May 20, 11:21 am, Tom Lavedas <tglba...(a)verizon.net> wrote: > > On May 19, 6:45 pm, xp <x...(a)discussions.microsoft.com> wrote: > > > > > Hi Tom, > > > > > Thanks for the idea; however, I tried this and it didn't work...any other > > > ideas? > > > > > "Tom Lavedas" wrote: > > > > On May 19, 10:05 am, xp <x...(a)discussions.microsoft.com> wrote: > > > > > I have both Office 2003 and 2007 dual installed on my PC. > > > > > I need to point my script to open certain files using 2007, but it defaults > > > > > to 2003. > > > > > Can someone please help me out with this? How can I specify for the script > > > > > to use 2007 when opening an Excel file? > > > > OK, the next best thing I can think of is to try forcing Excel2007 to > > open and then load your file into it, something like this ... > > > > ' Next line needs to point to the location for your environment > > sXLPath = """C:\Program Files\Microsoft Office\Office12\Excel.exe"" "_ > > & "/automation -Embedding" > > sFilePath = "C:\Someplace\YourFileName.xls" > > set oWSH = CreateObject("WScript.Shell") > > nRes = oWSH.Run(sXLPath, 1, False) > > if nRes <> 0 then > > wsh.echo "Error opening Excel", nRes > > wsh.quit > > end if > > On Error Resume Next > > set oXL = GetObject("","Excel.Application") > > if err.number <> 0 then > > wsh.echo "Error opening Excel", hex(err.number), err.description > > wsh.quit > > end if > > On Error Goto 0 > > oXL.visible = True > > > > set oWkBk = oXL.WorkBooks.open(sFilePath) > > ' The rest of your script ... > > oWkBk.Sheets(1).cells(1,1).value = "Done" > > > > All of the documentation says that leaving the first argument empty > > (i.e. GetObject( ,"Classname")) is supposed to attach to the running > > object. However, on my XP machine, this approach is throwing a > > runtime syntax error. So, I added an empty string, but I am assuming > > that since Excel is a Single Instance application, the returned > > automation object must be the 2007 object. As I said, I don't have it > > to try. If I am correct, the Open() should then load the file into > > the correct (2007) version of XL. It does work for the 2003 version I > > was forced to test with. > > > > Hope it works. > > _____________________ > > Tom Lavedas > > When you open an excel file normally, does it open using excel 2003? > If so, you probably just need to change your default file types in > "folder options". > . >
From: xp on 21 May 2010 11:03 FYI Tom, your suggestion worked perfectly. I had tried to do what you did, and I could get XL2007 to open, but the script failed to get a reference to the app, a syntax error on my part probably. Anyway, thanks so much for the help! Also, I see that these Discussion Groups are going away, do you know the path to an equivalent forum? "Tom Lavedas" wrote: > On May 19, 6:45 pm, xp <x...(a)discussions.microsoft.com> wrote: > > Hi Tom, > > > > Thanks for the idea; however, I tried this and it didn't work...any other > > ideas? > > > > "Tom Lavedas" wrote: > > > On May 19, 10:05 am, xp <x...(a)discussions.microsoft.com> wrote: > > > > I have both Office 2003 and 2007 dual installed on my PC. > > > > I need to point my script to open certain files using 2007, but it defaults > > > > to 2003. > > > > Can someone please help me out with this? How can I specify for the script > > > > to use 2007 when opening an Excel file? > > OK, the next best thing I can think of is to try forcing Excel2007 to > open and then load your file into it, something like this ... > > ' Next line needs to point to the location for your environment > sXLPath = """C:\Program Files\Microsoft Office\Office12\Excel.exe"" "_ > & "/automation -Embedding" > sFilePath = "C:\Someplace\YourFileName.xls" > set oWSH = CreateObject("WScript.Shell") > nRes = oWSH.Run(sXLPath, 1, False) > if nRes <> 0 then > wsh.echo "Error opening Excel", nRes > wsh.quit > end if > On Error Resume Next > set oXL = GetObject("","Excel.Application") > if err.number <> 0 then > wsh.echo "Error opening Excel", hex(err.number), err.description > wsh.quit > end if > On Error Goto 0 > oXL.visible = True > > set oWkBk = oXL.WorkBooks.open(sFilePath) > ' The rest of your script ... > oWkBk.Sheets(1).cells(1,1).value = "Done" > > All of the documentation says that leaving the first argument empty > (i.e. GetObject( ,"Classname")) is supposed to attach to the running > object. However, on my XP machine, this approach is throwing a > runtime syntax error. So, I added an empty string, but I am assuming > that since Excel is a Single Instance application, the returned > automation object must be the 2007 object. As I said, I don't have it > to try. If I am correct, the Open() should then load the file into > the correct (2007) version of XL. It does work for the 2003 version I > was forced to test with. > > Hope it works. > _____________________ > Tom Lavedas > . >
From: Tom Lavedas on 24 May 2010 09:07
On May 21, 10:46 am, xp <x...(a)discussions.microsoft.com> wrote: > Thanks again Tom, I will give this a try... > > I've never seen: & "/automation -Embedding" > > Appended like that...is it documented somewhere? > I found it by examining the command line that launched the Excel.Application object. I had to get it via a WMI query. The registry indicated the /automation switch, but the -Embedding was only present when I examined the process' commandline property via the WMI query. Without the two switches, a second blank workbook was opened, though the script still gained access to the correct workbook. The problem came in releasing Excel, where the extra blank workbook was orphaned and took extra code to close. Once I found the extra switch, the process worked more as I expected - opening only the intended file. _____________________ Tom Lavedas PS. Regarding this newsgroup's reported end, I don't know of a direct replacement, though the TechNet "The Official Scripting Guys Forum!" does include coverage of VBS topics. (http:// social.technet.microsoft.com/Forums/en-US/ITCG) |