Use this UDF created in Excel in Access

[silentwolf]

Hi guys,
I was hoping for some help.

Sub Importieren()
    Dim appExcel As Excel.Application
    Dim objFiledialog As FileDialog
    Dim FileWasChosen As Boolean

    Dim wbkQuelle As Workbook
    Dim wksQuelle As Worksheet
    Dim varPfadDatei As Variant
    Dim wksZiel As Worksheet

    Dim strFileName As String

    Set appExcel = HoleAnwendung("Excel.Application")

    varPfadDatei = appExcel.Application.GetOpenFilename("Alle Daten,*.xl*,Text Dateien, *.csv*", 1, "Daten auswählen", , False)

    If varPfadDatei = False Then
        Exit Sub
    End If

        Set wbkQuelle = Workbooks.Open(varPfadDatei)
        Set wksQuelle = wbkQuelle.Worksheets(1)

        strFileName = wbkQuelle.Name
        strFileName = Replace(NurDatei(varPfadDatei), ".xlsx", "")
    
    If WorksheetExists(strFileName) Then
        MsgBox "Blatt wurde bereits importiert!", vbInformation, p_cstrAppTitel
        wbkQuelle.Close xlDoNotSaveChanges
        Exit Sub
    Else
        Set wksZiel = ThisWorkbook.Worksheets.Add()
        wksZiel.Name = strFileName

        wksQuelle.UsedRange.Copy wksZiel.Cells(1, 1)

    End If

    wbkQuelle.Close xlDoNotSaveChanges

    Set wbkQuelle = Nothing
    Set wksZiel = Nothing
End Sub

Public Function WorksheetExists(strBlattName As String) As Boolean
    Dim objBlatt As Object
    
    WorksheetExists = False
[COLOR=#ff0000]    For Each objBlatt In ThisWorkbook.Sheets[/COLOR]
        If objBlatt.Name = strBlattName Then
            WorksheetExists = True
            Exit For
        End If
    Next objBlatt
End Function

the above sub calls the function I have created both in excel and it works fine. However as I like to run all my code out of access I would need to modify this peace of code so it works also there.
The sub is working modified to suit in access as shown above with a littel UDF "HoleAnwendung" which is translated "GetApplication" .. reference to Excel libary is set and the first part works fine.
However when it goes into WorksheetExist the code stops at marked text in red.

Run-time error '1004' : Method 'Range' of object'_Global' failed

So what needs to be done so it can reconise this object


Hope someone could please help me on this.

 

[silentwolf]

Thank you so no more errors but still only goes into the msgbox..

 

[RoryA]

If you're getting the message, the sheet must already be in the workbook.

 

[silentwolf]

I have to look at this closer.. I added appExcel.visible now it opens the new workbook but afterwards also jumpes into the msgbox.
so there must me a missarangement somehow in my code.