unspecified error when creating hyperlink in excel via acces

[tonnic]

Hi,

I have following problem:

I'm exporting data from access into several excel-files.
For every shop I'm creating a new excel-file and in this file I have different worksheets ....
So, after I've exported the whole data to the file I let access run some additional VBA-code for Subtotals and creating area-names and creating hyperlinks.

This all works without any problem for one shop.
But if I run the code for more shops, I always get an "unspecified-error" at the second file.

Below is the Code I used - the "error-part" is marked red.



.... exporting data
.....
Public Sub tabellen_ausblenden(ByVal pFileName As String)
'/**************************************************************************************

'/***************************************************************************************

'vars
Dim xls As Object
Dim file As String
Dim wksh As Worksheet



'init vars
file = pFileName

'init excel
Set xls = CreateObject("excel.application")
xls.workbooks.Open FileName:=file
xls.Visible = True ' I read on the microsoft-site that an error could occur when the application is hidden - but I couldn't notice any difference ...
'hiding worksheets
For Each wksh In xls.Worksheets
Select Case wksh.Name
Case "0", "1", "2", "3", "9"
wksh.Visible = False
End Select
Next

'Creating Subtotals and so on
For Each wksh In xls.Worksheets
Select Case wksh.Name
Case "Detail_9", "Detail_3", "Detail_2", "Detail_1", "Detail_0"
wksh.Select
Call teilergebnisse(xls)
Call CellNames(xls, Right(wksh.Name, 2))
Call DetailLinks(xls) 'This function gets the error
End Select
Next


'Tabellenblatt "HL" anzeigen
With xls
.Sheets("HL").Select
'save/close
.ActiveWorkbook.Save
.ActiveWorkbook.Close
.Quit
End With

'reset vars

Set wksh = Nothing

Set xls = Nothing

End Sub



Public Sub DetailLinks(ByVal obj As Object)

With obj
.Range("A2").Select

While Not IsEmpty(.ActiveCell)

If .ActiveCell.Text = "Betriebskosten" Then
.ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
.ActiveCell.Value & "_0" 'On creating the hyperlink I get the error
End If
.ActiveCell.Offset(1, 0).Activate

Wend

End With

End Sub


... next file

I hope you have an idea why it doesn't work
Let me know, if you need more information or if my problem is unclear..

 

[Norie]

Does xls not represent an application object rather than a workbook object?

 

[tonnic]

Hi Norie,

now I know was the real problem is.
Normally I close the excel-session after every file.

But when I use the line with .activesheet.hyperlink.add, the excel-session is still open in the task-manager after closing the workbook and application.quit.

In the next file VBA doesn't know, which session is the right one for this transaction ...
But I couldn't find a solution till know