Opening Excel file errors the second time

[mkaeser]

I have the following code, which works the way I want it to when I click the button the first time. However, if I close out the excel and click the button again, it errors at the line "ActiveWorkbook.Worksheets("Ref").UnProtect", with the message "Run-time error '91', Object variable or With block variable not set". I can not figure this out, why in the world does it work the first time, but not the second???

Private Sub cmdReport_Click()

Dim path As String
Dim XL As Object
Dim oBook As Excel.Workbook
Dim pic As Excel.Shape
Set XL = CreateObject("Excel.Application")

If IsNull(Me.RawReport) Or Me.RawReport = "" Then
MsgBox "There currently are no Raw Data Forms for this project", , "No File"
Else
path = HyperlinkPart(Me.RawReport, acAddress)

With XL.Application
.Visible = True
.AskToUpdateLinks = False
.Workbooks.Open path
.AskToUpdateLinks = True
ActiveWorkbook.Worksheets("Ref").Unprotect **IT ERRORS HERE**
For Each pic In ActiveWorkbook.Worksheets("Ref").Shapes
If pic.Name = Forms!Main!txtCurrentUser Then
pic.Copy
Worksheets("TermsSig").Range("E12").Select
Worksheets("TermsSig").Paste Destination:=Worksheets("TermsSig").Range("E12")
With Worksheets("TermsSig").Range("E12")

Selection.ShapeRange.ScaleHeight 1.5020895209, _
msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 1.5020911212, _
msoFalse, msoScaleFromBottomRight
Selection.ShapeRange.IncrementTop -20.5
Selection.ShapeRange.IncrementLeft 7.75

End With
End If
Next pic

End With


End If
Set XL = Nothing

End Sub

 

[pbaldy]

In that (and many other) line you don't use the Excel object created previously. That can leave a hanging Excel process and may be the cause of your problem:

http://www.btabdevelopment.com/ts/excelinstance

 

[mkaeser]

Huh, I was under the assumption that since a lot of the code was between the "With XL.Application" code, it was assigning the correct variable to the rest of the code. I will try that suggestion, thank you much!

 

[pbaldy]

Only the lines beginning with ".", like

.Visible = True

The lines without are not using the With object. I'm betting you have a hanging Excel instance in Processes, though you may not have noticed it.

 

[mkaeser]

My goodness pbaldy I've been trying to figure out what the issue is for over an hour and in less than 5 minutes of making this post, my issues are gone! Thank you so much for your help, and for the link to that great site!

 

[pbaldy]

Happy to help!