GetObject - Outlook/Excel 2007

[JoeRob]

The GetObject function, as in “Set MyXL = GetObject("C:\MYTest.xls")†doesn’t
work for a Excel 2007 worksheets. That is code copied directly from
Microsoft Help, doesn’t work. However, the same code works fine if the
worksheet is saved as a Excel 97-2003 worksheet. Is there something wrong in
the Getobject statement, or is this a glitch in Outlook 2007?

 

[JP]

Don't you mean

Set MyXL = GetObject("C:\MYTest.xlsm")

or

Set MyXL = GetObject("C:\MYTest.xlsb")

or

Set MyXL = GetObject("C:\MYTest.xlsx")

?

--JP

 

[JoeRob]

The example I found was "MYTest.xls". As I suspected there are other
extensions for excel. But, what are they, were do you find them. They don’t
show up in the path. I'm inexperienced and find finding thing like
extensions, class names, etc very confusing.

 

[Sue Mosher [MVP]]

You're trying to open an Excel worksheet using Outlook VBA code? You need to
instantiate an Excel.Application object first, then use its methods, e.g.:

On Error Resume Next
Set MyXL = GetObject(, "Excel.Application")
If MyXL Is Nothing Then
Set MyXL = CreateObject("Excel.Application')
End If
Set MyWorkbook = MyXL.Workbooks.Open("C:\MYTest.xls")

See http://www.outlookcode.com/article.aspx?ID=23 for links to many more
examples of working with Excel in scenarios that also involve Outlook.

 

[JP]

Check out http://technet.microsoft.com/en-us/library/cc179191.aspx, it
gives you a lot of (all?) the file extensions for Office 2007.

But you should be able to just see the file extensions in Windows
Explorer..?

--JP

 

[JP]

Sue,

You can use GetObject to load a file directly.

The code "GetObject("C:\MYTest.xls")" should instantiate Excel and
open the specified workbook.

--JP

 

[Sue Mosher [MVP]]

I can see how that would be useful in some cases, but I like my approach,
because it offers the opportunity to keep track of whether Excel was already
running or was started by the code and thus determine what cleanup to do
when the workbook is no longer needed.
--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54


Sue,

You can use GetObject to load a file directly.

The code "GetObject("C:\MYTest.xls")" should instantiate Excel and
open the specified workbook.

--JP

 

[JoeRob]

Sue,
Thank you for your response. I didn't include all the code in my question.
The complete code can be found in Outlook Help by searching for GetObject. I
think that code contains all the fine points you recommend. And, I truly
appreciate your thought. Somehow though I think JP's response addresses the
problem. The file extension used in the code is "XLS" and the file used is
Axcel 2007, a different extension. I just have to figure out which one to
use. Anyway, thank again for your time and trouble.

 

[Sue Mosher [MVP]]

Excel 2007 is capable of opening an existing .xls file. I do it all the
time. What happens if you double-click the file? If it opens in Excel, that
tells you the file extension is registered properly and pointing to Excel.

 

[JoeRob]

Sue & JP
Thank you both for your assistance. I just had a few minutes to bash at
this problem. Sorry, Sue your code doesn't work for "Test.XLS" if Test is an
Excel 2007 file. JP you hit the nail on the head. By changing the extension
to "Test.XLSM" the code works.
Thank you both for your efforts.
PS: JP your wrote "But you should be able to just see the file extensions in
Windows Explorer..?" Is this something you turn on an off? I don't see file
extensions in my list when Windows Explorer.

 

[Sue Mosher [MVP]]

That means the file was named text.xlsm, not text.xls. You have to know the
exact name of the file before you can open it.

Yes, you can -- and should! -- turn on file extensions in Windows Explorer.
What version of Windows do you use?

 

[Karl Timmermans]

Just to add a little bit of trivia to this for reference purposes:

#1 - If <Test> has an .xls extension - means that somewhere along the line
it was likely saved with an "xlsx" extension (or any other of the '2007
extensions) and then "incorrectly" renamed to have an .xls extension (or
simply saved at the outset with the incorrect extension for the format
selected).

#2 - If you open a '2007 workbook that has been renamed/saved with an .xls
extension directly from within Excel - you will encounter at least one
intermediate message advising that the format of the file differs from the
extension and a response is required by the user - which likely explains
why "GetObject" failed in your scenario - nothing wrong with the GetObject
statement nor is there a glitch with O'2007 as per questions in original
post.

We have run across a couple of customer scenarios where .xls files have
been provided by a lead generation service via web download. These ".xls
files" were in fact '2007 files with more than 255 columns causing all
kinds of aggravation even just within Excel regardless of the extension
used. Rule of thumb - if Excel itself can't open a file without user
interaction of some kind or other - nothing else is going to work
seemlessly either.

Karl
--
____________________________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - QuickPort/DataPort/Exporter/Toolkit/Duplicate Contact Mgr
"Contact import/export/data management tools for Outlook '2000/2010"

http://www.contactgenie.com

 

[Sue Mosher [MVP]]

Fascinating stuff, Karl. Thanks.

 

[JoeRob]

Sue & JP
The two of you have been most helpful. Just for the record, the sequence of
event went somthing like this':
(1) Searched help for GetOject and copied code to Outlook Macro
(2) Opened Excel 2007 and created file Test. Saved file using Save As and
Name "Test"
(3) Tried to run code, code started Excel, but didn't open file Test
(4) Searched without success, but thought - what if this code is for an
older version on Excel.
(5) Opened Test and saved a Excel 97-2007
(6) Code now opens worksheet "Test", but I'm really confused, enter Sue & JP
(7) After much help, opened Test and saved as Excel 2007, and changed file
extension in code to XlMS, i.e. Test.XLMS.
(8) Code noow starts Excel and opens Test


Much thanks to both of you.

 

[JoeRob]

Thanks to you, I think I'm getting smarter. I thought file extensions as
part of the path went away with the XP operating system. In fact, I think I
stopped seeing file extensions with Windows 95. I thought this was just the
way Microsoft decided to display file and their path. I looked around in
Windows Explorer and found the check box to turn on file extensions. This
should help keep me from stumbling a little bit.
Thanks

 

[Killerjoe_UK]

You're trying to open an Excel worksheet using Outlook VBA code? You need to
instantiate an Excel.Application object first, then use its methods, e.g.:

On Error Resume Next
Set MyXL = GetObject(, "Excel.Application")
If MyXL Is Nothing Then
Set MyXL = CreateObject("Excel.Application')
End If
Set MyWorkbook = MyXL.Workbooks.Open("C:\MYTest.xls")

See http://www.outlookcode.com/article.aspx?ID=23 for links to many more
examples of working with Excel in scenarios that also involve Outlook.
--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54


"JoeRob" <[email protected]> wrote in message
news:[email protected]...
>
> The example I found was "MYTest.xls". As I suspected there are other
> extensions for excel. But, what are they, were do you find them. They
> don't
> show up in the path. I'm inexperienced and find finding thing like
> extensions, class names, etc very confusing.
> "JP" wrote:
>
>> Don't you mean
>>
>> Set MyXL = GetObject("C:\MYTest.xlsm")
>>
>> or
>>
>> Set MyXL = GetObject("C:\MYTest.xlsb")
>>
>> or
>>
>> Set MyXL = GetObject("C:\MYTest.xlsx")
>>
>> ?
>>
>> --JP
>>
>> On Jan 19, 4:24 pm, JoeRob <[email protected]> wrote:
>> > The GetObject function, as in "Set MyXL = GetObject("C:\MYTest.xls")"
>> > doesn't
>> > work for a Excel 2007 worksheets. That is code copied directly from
>> > Microsoft Help, doesn't work. However, the same code works fine if the
>> > worksheet is saved as a Excel 97-2003 worksheet. Is there something
>> > wrong in
>> > the Getobject statement, or is this a glitch in Outlook 2007?

Hi Sue,

I am really interested in what you have posted here as I am trying to achieve a formatted excel report using vba out of ms project and your proposal seems to work well.....I am fairly new to all of this but would love to chat more about the capabilities and amount of the excel object model can be accessed in this way.....

Rgds

Chris