First time Outlook VBA - Prob right-a-way

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jim May - 30 Mar 2008 17:06 GMT

Why am I getting error described at line 9 below?

Sub TestExport()
Dim xlApp As Excel.Application
Dim oWb As Excel.Workbook
Dim oWs As Excel.Worksheet
Dim sWn As String
Set xlApp = New Excel.Application
sWn = "C:\Documents and Settings\Jim May\My
Documents\Project_Discovery\Outlook-Download\TestOutlookDownload.xls"
Set oWb = xlApp.Workbooks(sWn) ' getting r/t error 9 S/C out of range
Set oWs = xlApp.Sheets(1)
oWs.Cells(1, 1).Value = Application.ActiveExplorer.Selection(1).Body
End Sub

Reply to this Message

Sue Mosher [MVP-Outlook] - 30 Mar 2008 20:24 GMT

Because the method to open an existing workbook is Workbooks.Open.

Signature

Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54

> Why am I getting error described at line 9 below?
>
[quoted text clipped - 10 lines]
> oWs.Cells(1, 1).Value = Application.ActiveExplorer.Selection(1).Body
> End Sub

Reply to this Message

Jim May - 31 Mar 2008 01:26 GMT

Thanks Sue,
I changed my code line to the below and with my Excel App Closed Ran the code,
but nothing happened -- Excel didn't even open -- I previously had it open
and also the workbook TestOutlookDownload.xls

Set oWb = xlApp.Workbooks.Open(sWn)

Thanks for any other suggestions.

Jim

> Because the method to open an existing workbook is Workbooks.Open.
>
[quoted text clipped - 12 lines]
> > oWs.Cells(1, 1).Value = Application.ActiveExplorer.Selection(1).Body
> > End Sub

Reply to this Message

Sue Mosher [MVP-Outlook] - 31 Mar 2008 13:31 GMT

Nothing in your code would cause Excel to display the worksheet. If you want to display the workbook, you can set the Excel.Application object's Visible property to True and call the Workbook.Activate method.

Also, you may want to post your Excel-related questions in an Excel group.

Signature

Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54

> Thanks Sue,
> I changed my code line to the below and with my Excel App Closed Ran the code,
[quoted text clipped - 8 lines]
>
>> Because the method to open an existing workbook is Workbooks.Open.

>> > Why am I getting error described at line 9 below?
>> >
[quoted text clipped - 10 lines]
>> > oWs.Cells(1, 1).Value = Application.ActiveExplorer.Selection(1).Body
>> > End Sub

Reply to this Message

Rate this thread: