Exit Sub in Class Module

[silentwolf]

Hi guys,
I got a little issue I would need some help from you.

I got a class Module where I import Excel Files and then modify this file to suit.
All works quite well.

The issue is that I like to import the file and then run the next sub afterwards from that sub.

Sub AuszuegeImportieren()
    Set clsA = New clsAuszuege
    
    On Error GoTo Fehler:
    With clsA
        .ExcelImport
    End With
    
Fehler:
    MsgBox "No file has been selected!", vbInformation, p_cstrAppTitel
    Exit Sub
    
    ModifyFile
    
    Set clsA = Nothing
End Sub

The Code above is my attempt to have an error handler when no file has been chosen.
It works if I do not select a file but it fails to run "ModifyFile" wenn I do select a file.

How would I need to change this error handler to suit so it exit the sub if no file has been selected and run "ModifyFile" once a file has been selected.

Hope this is clear?

Many thanks

Albert

 

[Rick Rothstein]

If I understand what you are trying to do correctly, I believe the following modification to your code is what you want...

Sub AuszuegeImportieren()
    Set clsA = New clsAuszuege
    
    On Error GoTo Fehler:
    With clsA
        .ExcelImport
    End With
    Set clsA = Nothing
    
    ModifyFile
    
    Exit Sub
    
Fehler:
    MsgBox "No file has been selected!", vbInformation, p_cstrAppTitel
    Set clsA = Nothing
    
End Sub

 

[silentwolf]

Hi Rick,
many thanks to your reply.
It is getting close very close.
But still not quite exiting the sub correctly..
I try and will get back to you..

if I step through the code with F8 it still jumps to the "ModifyFile" and then get the same error..

 

[silentwolf]

Hi Rick,
when I run the code the way I got it in my first post following happens.
1. When I do not select a file then the error handler is working.
2. if I select a file it imports the file but stops also at the error handler same msgbox and exit sub

When I run your code following happens
1. When I do not select a file it runs ModifyFile.. and that gives me an error as no file has been selected
2.if I select a file it imports the file and runs the ModifyFile sub..

So somewhere the code lines need to swap so it does what it suppose to do..

I am trying but still no luck to get it run smoothly .(

 

[silentwolf]

Just wondering if there is someone who could help me with this issue?
Still working on it but can't seam to get it right

Many thanks

Albert

 

[Rick Rothstein]

Hi Rick,
many thanks to your reply.
It is getting close very close.
But still not quite exiting the sub correctly..
I try and will get back to you..

if I step through the code with F8 it still jumps to the "ModifyFile" and then get the same error..

What exactly does .ExcelImport do, both when you select a file and when you don't select a file?

 

[silentwolf]

Hi Rick,
thanks for your reply again.

What exactly does .ExcelImport do, both when you select a file and when you don't select a file?

It does what I did post on my reply on #4
but I try to explain it better..

with this code

Sub AuszuegeImportieren()
    Set clsA = New clsAuszuege
    
    On Error GoTo Fehler:
    With clsA
        .ExcelImport
    End With
    
Fehler:
    MsgBox "No file has been selected!", vbInformation, p_cstrAppTitel
    Exit Sub
    
    ModifyFile
    
    Set clsA = Nothing
End Sub

No file is selectet
it goes to Fehler:
'______________________
if I select a file which I already have importet then
a msgbox shows "That I have already importet that file!" that comes from the .Excel Import sub
then it goes back to the Fehler:
'---------------------------------------------------
If I run the code again with the importet File does not exist then following is happening
-it imports the file but jumps to the Fehler:
'______________________________________________

when I run your code
then following is happening
'------------------------------------
If I select a file to open and no file has been importet then
it gets importet and the ModifyCode is running
'_________________________________________
If I dont select a file and no file is already importet then
it imports the file.. and run the ModifyFile----- which gives me then an error as the Modify code is running but no file"worksheet" has been importet
'--------------------------------------------------
if I have a file already importet and select the same file then
it tells me that the file is already importet which comes from the ExcelImport
and it runs the ModifyFile as well...

hope this is now better to understand.

Not easy gg

 

[silentwolf]

Good Morning,
Maybe this code helps to understand.
I am just careful as in other forums some guys not like if to many codes are providet.

Sub ExcelImport()
    Dim wbkQuelle As Workbook
    Dim wksQuelle As Worksheet
    Dim varPfadDatei As Variant
    Dim wksZiel As Worksheet
    
    Dim strFileName As String
    
    varPfadDatei = Application.GetOpenFilename("Alle Daten,*.xl*,Text Dateien, *.txt*", 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

Would love to get this up and running so there are error handler working properly.

Many thanks for help and input!