VBA Run-time error '-2147417851 (800010105)'

ka8799
ka8799 used Ask the Experts™
on
I wrote a ms Access VBA code that creates an Excel workbook with multiple sheets and inserts certain data into the respectiive sheets.  Along with that I add hyperlinks to allow the user to move from one excel sheet to another.  The problem I am having is that I am able to run the vba code one time and everything works great.  If I attempt to run it again I get the following message.  Regardless if I close out excel or not.

Run-time error '-2147417851 (800010105)'
Method 'Add' of object 'Hyperlins'failed

I can get it to work again if I close excel go into debug mode in access and reset the routine.  I can also get it to work continuously if I comment out the hyperlinks in the access vba.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2011

Commented:
It would be helpful to see the code. I suspect you are not cleaning up properly.

Author

Commented:
Rorya,
Let me know if this is enough of the code.

Private Sub cmdSAV_Click()
    Dim mAPP_XL As Excel.Application
    Dim mBOOK As Workbook
    Dim mSHT As Worksheet    
    Dim mSUM As String    
   
'Create new instance of Excel.
    Set mAPP_XL = New Excel.Application
    mAPP_XL.Visible = True

'Add new workbook to Workbooks collection.
    Set mBOOK = mAPP_XL.Workbooks.Add
   
'Add new sheets to the Worksheets collection.
    Set mSHT = mBOOK.Worksheets.Add
    mSUM = "Summary"
    mAPP_XL.ActiveWorkbook.Sheets(1).Name = mSUM
   
        mSHT.Range("J1").Value = "Sum"        
        mSHT.Range("J1").Select
       
'Creating hyperlink  “”This is where the code breaks when I run it a second time.””
        mSHT.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & mSUM & "'!A2"
       
        End With
   
MsgBox "Successfully.."
Exit Sub
Most Valuable Expert 2011
Top Expert 2011
Commented:
Replace 'Selection' in the Hyperlinks.Add line with mSHT.Range("J1")

you should never use an unqualified reference like Selection or ActiveCell when automating Excel or you create an implicit reference to the Application object that is only released when your calling program ends.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial