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

On Jan 19, 4:24 pm, JoeRob <Joe...> 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?

 

[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.

"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 <Joe...> 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?
> .
>

 

[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.

Sue Mosher

"JoeRob" <JoeRob> wrote in message

news:4EAF13D3-3EBF-4D5B-986A-AE33D57D5BAC@microsoft.com...

> 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 <Joe...> 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?

 

[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

On Jan 20, 12:14 pm, JoeRob <Joe...> wrote:
> 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]

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

On Jan 20, 1:04 pm, "Sue Mosher [MVP]" <sue...@gmail.com> wrote:
> You're trying to open an Excel worksheet using Outlook VBA code? You needto
> 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")

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

 

[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

"JP" <jp2112@earthlink.net> wrote in message

news:2b929508-0c5f-477f-9229-56f03c674f63@u41g2000yqe.googlegroups.com...

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

On Jan 20, 1:04 pm, "Sue Mosher [MVP]" <sue...@gmail.com> wrote:
> 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.

 

[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.

"JP" wrote:


> 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

> On Jan 20, 12:14 pm, JoeRob <Joe...> wrote:
> > 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]]

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.

Sue Mosher

"JoeRob" <JoeRob> wrote in message

news:38868A3A-E695-41D2-A58D-4A2B0EADE4A9@microsoft.com...
> 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.

> "JP" wrote:
>
> > 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
>

>> On Jan 20, 12:14 pm, JoeRob <Joe...> wrote:
> > > 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.

 

[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.

"JP" wrote:


> 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

> On Jan 20, 12:14 pm, JoeRob <Joe...> wrote:
> > 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]]

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?

Sue Mosher

"JoeRob" <JoeRob> wrote in message

news:9981D10D-7AF5-45F2-A377-1416D7EB08EB@microsoft.com...
> 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.

> "JP" wrote:
>
> > 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
>

>> On Jan 20, 12:14 pm, JoeRob <Joe...> wrote:
> > > 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.
> > >
>

>> .
> >

 

[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

______________________

ContactGenie - QuickPort/DataPort/Exporter/Toolkit/Duplicate Contact Mgr

"""

"JoeRob" <JoeRob> wrote in message

news:9981D10D-7AF5-45F2-A377-1416D7EB08EB@microsoft.com...
> 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.eee
> 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.

> "JP" wrote:
>
> > 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
>

>> On Jan 20, 12:14 pm, JoeRob <Joe...> wrote:
> > > 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]]

Fascinating stuff, Karl. Thanks.

Sue Mosher

"Karl Timmermans" <karl@claxton.com> wrote in message

news:O2Gju1tmKHA.1536@TK2MSFTNGP06.phx.gbl...
> 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
> > ______________________
>

> ContactGenie - QuickPort/DataPort/Exporter/Toolkit/Duplicate Contact Mgr
> """

>

> "JoeRob" <JoeRob> wrote in message
> news:9981D10D-7AF5-45F2-A377-1416D7EB08EB@microsoft.com...
> > 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.eee
> > 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.
>

>
>> "JP" wrote:
> >
> >> 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
> >
>>> On Jan 20, 12:14 pm, JoeRob <Joe...> wrote:
> >> > 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.
> >>>
>>> .
> >>

>

 

[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.

"Sue Mosher [MVP]" wrote:


> Fascinating stuff, Karl. Thanks.

> > Sue Mosher
> > >

> "Karl Timmermans" <karl@claxton.com> wrote in message
> news:O2Gju1tmKHA.1536@TK2MSFTNGP06.phx.gbl...
> > 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
> > > > ______________________
> >

> > ContactGenie - QuickPort/DataPort/Exporter/Toolkit/Duplicate Contact Mgr
> > """
> >

> > "JoeRob" <JoeRob> wrote in message
> > news:9981D10D-7AF5-45F2-A377-1416D7EB08EB@microsoft.com...
> >> 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.eee
> >> 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.
> >
> >
> >> "JP" wrote:
> >
> >>> 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
> >>
> >>> On Jan 20, 12:14 pm, JoeRob <Joe...> wrote:
> >>> > 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.
> >>> >>
> >>> .
> >>>
> >

> .
>

 

[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

"Sue Mosher [MVP]" wrote:


> 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?

> > Sue Mosher
> > >

> "JoeRob" <JoeRob> wrote in message
> news:9981D10D-7AF5-45F2-A377-1416D7EB08EB@microsoft.com...
> > 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.
> > "JP" wrote:
> >
> >> 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
> >
> >> On Jan 20, 12:14 pm, JoeRob <Joe...> wrote:
> >> > 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.
> >> >
> >> .
> >>

> .
>