ka8799
asked on
VBA Run-time error '-2147417851 (800010105)'
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.
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.
It would be helpful to see the code. I suspect you are not cleaning up properly.
ASKER
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.She ets(1).Nam e = 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
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.She
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.