VBA Runtime 9 error

[Dinomut101]

Hello everyone,

I am rather new to programming and especially VBA. As a kind of side-project at work I am attempting to run a macro to pull information from Outlook 2010 into Excel 2010. I have browsed the internet looking for some information and I have been able to pick and pull from other peoples information, my issue is that I keep getting a run-time '9' error when I am attempting to get this working. Any help or pointers would be greatly appreciated.

Option Explicit
Sub CopyToExcel()
Dim olItem As Outlook.MailItem
Dim xlApp As Object
Dim xlWB As Object
Dim xlSheet As Object
Dim vText, vText2, vText3, vText4, vText5 As Variant
Dim sText As String
Dim rCount As Long
Dim bXStarted As Boolean
Dim enviro As String
Dim strPath As String
Dim Reg1 As Object
Dim M1 As Object
Dim M As Object

enviro = CStr(Environ("USERPROFILE"))
strPath = enviro & "\Documents\test2.xlsx"
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Application.StatusBar = "Please wait while Excel source is opened ... "
Set xlApp = CreateObject("Excel.Application")
bXStarted = True
End If
On Error GoTo 0
Set xlWB = xlApp.Workbooks.Open(strPath)
Set xlSheet = xlWB.Sheets("Test") 'run-time error 9 here

Set olItem = Application.ActiveExplorer().Selection(1)


rCount = xlSheet.Range("B" & xlSheet.Rows.Count).End(-4162).Row
rCount = rCount + 1

sText = olItem.Body

Set Reg1 = CreateObject("VBScript.RegExp")

With Reg1
.Pattern = "Start\s*[:]+\s*(\w*)\s*"
End With
If Reg1.test(sText) Then



Set M1 = Reg1.Execute(sText)
For Each M In M1
vText = Trim(M.SubMatches(1))
vText2 = Trim(M.SubMatches(2))
vText3 = Trim(M.SubMatches(3))
vText4 = Trim(M.SubMatches(4))
vText5 = Trim(M.SubMatches(5))
Next
End If

xlSheet.Range("B" & rCount) = vText
xlSheet.Range("c" & rCount) = vText2
xlSheet.Range("d" & rCount) = vText3
xlSheet.Range("e" & rCount) = vText4
xlSheet.Range("f" & rCount) = vText5

xlWB.Close 1
If bXStarted Then
xlApp.Quit
End If
Set M = Nothing
Set M1 = Nothing
Set Reg1 = Nothing
Set xlApp = Nothing
Set xlWB = Nothing
Set xlSheet = Nothing
End Sub

 

[Jonmo1]

Welcome to the board.

Subscript out of range is one of the more straight forward errors in VBA.
It pretty much means that the object referenced doesn't exist.

With that line
Set xlSheet = xlWB.Sheets("Test") 'run-time error 9 here
It's looking for a sheet named "Test" in the xlWB book.
The error means there is no such sheet in that book.

Check for spaces in the sheet name " Test" or "Test "

 

[Norie]

Just curious, which application are you running this code in?

 

[Dinomut101]

Just curious, which application are you running this code in?

I am just using the VBA editor that is built into Outlook 2010

 

[Dinomut101]

Check for spaces in the sheet name " Test" or "Test "

You were absolutely correct. Thank you very much, it did have an extra space after the sheet name.

 

[Jonmo1]

Glad to help, thanks for the feedback.

 

[Dinomut101]

That cleared next time I tried to run the script in Outlook, but now I am getting a separate error that doesn't make since that it is just popping up after changing the name of the Sheet.

    With Reg1        .Pattern = "(Start\s*[:]+\s*(\w*)\s*)"
    End With
    If Reg1.test(sText) Then
      


        Set M1 = Reg1.Execute(sText)
        For Each M In M1
           vText = Trim(M.SubMatches(1))
           vText2 = Trim(M.SubMatches(2)) 'run-time error '5' Invalid procedure call or argument
           vText3 = Trim(M.SubMatches(3))
           vText4 = Trim(M.SubMatches(4))
           vText5 = Trim(M.SubMatches(5))
        Next
    End If