First time Outlook VBA - Prob right-a-way

[Jim May]

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

 

[Sue Mosher [MVP-Outlook]]

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

 

[Jim May]

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

 

[Sue Mosher [MVP-Outlook]]

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.

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