Specify open file with Excel 2007

X

xp

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?

Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open
oExcel.Visible = True

Thanks for your help!
 

My Computer

T

Tom Lavedas

On May 19, 10:05 am, xp <[email protected]> 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?
>
> Set oExcel = CreateObject("Excel.Application")
> oExcel.Workbooks.Open
> oExcel.Visible = True
>
> Thanks for your help!
I don't have Office 2007 installed, but since no one else seems to be
chiming in, I'll risk it.

I must assume there are two automation objects registered on your
machine(s) when both 2003 and 2007 are installed. That might mean
that the 2007 object can be accessed by its distinct version number,
something like this ...

Set oExcel = CreateObject("Excel.Application.12")
' etc

I don't know if it will work, but it's easy enough to try. BTW, you
can look in the registry to see if there are two objects - one with .
11 and another with .12.
_____________________
Tom Lavedas
 

My Computer

X

xp

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 <[email protected]> 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?
> >
> > Set oExcel = CreateObject("Excel.Application")
> > oExcel.Workbooks.Open
> > oExcel.Visible = True
> >
> > Thanks for your help!
>
> I don't have Office 2007 installed, but since no one else seems to be
> chiming in, I'll risk it.
>
> I must assume there are two automation objects registered on your
> machine(s) when both 2003 and 2007 are installed. That might mean
> that the 2007 object can be accessed by its distinct version number,
> something like this ...
>
> Set oExcel = CreateObject("Excel.Application.12")
> ' etc
>
> I don't know if it will work, but it's easy enough to try. BTW, you
> can look in the registry to see if there are two objects - one with .
> 11 and another with .12.
> _____________________
> Tom Lavedas
> .
>
 

My Computer

T

Tom Lavedas

On May 19, 6:45 pm, xp <[email protected]> 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 <[email protected]> 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
 

My Computer

E

ekrengel

On May 20, 11:21 am, Tom Lavedas <[email protected]> wrote:

> On May 19, 6:45 pm, xp <[email protected]> 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 <[email protected]> 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 thescript
> > > > 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".
 

My Computer

X

xp

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 <[email protected]> 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 <[email protected]> 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
> .
>
 

My Computer

X

xp

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 <[email protected]> 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 <[email protected]> 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
> .
>
 

My Computer

T

Tom Lavedas

On May 21, 10:46 am, xp <[email protected]> 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)
 

My Computer

Top