Import userform using VBA

[ITs65458]

Hello, I have an excel file that creates a copy of one of my sheets and saves the sheet as a new workbook with a name constructed from info that the field agents are using. During the save process, one of my userforms is being exported and then imported to the new workbook during the creation of it. However, the VBA fails to import the userform tot to the new workbook and I receive a useform log file. The log file says 'Line 2: The Form or MDIForm name DriverReport is already in use; cannot load this form.'
In my VBA I have set to grab the useform named driverreport and rename the file when it gets exported. the frm. saves with the new name fine, but the log keeps saying that the name is already in use.
Below is my code. Any help will be very appreciated.

Private Sub CommandButton35_Click()


Workbooks("BEUS Digital 2.5.1 8.9.21.xlsm").Activate

Dim myValWU As Double

Dim strVehNum As String

Dim strName As String

Dim strShift As String

Dim strYear As String

Dim strPath As String

Dim strKW As String

Dim strWK As String

Dim sDate As String

Dim FSO As New FileSystemObject

Dim strFname As String

Dim DrNum As String

Dim Dr As String

Dim path As String

Dim FileName1 As String

Dim fn As String


 

      strPath = Sheets("ALL APP LINKS").Range("B10")

      strFname = Sheets("ALL APP LINKS").Range("B14")

      strVehNum = Sheets("ALL APP LINKS").Range("B29") ' assumes vehicle number in B29

      sDate = Format(Sheets("ALL APP LINKS").Range("B28"), "YYYYMMDD")

      strShift = Sheets("ALL APP LINKS").Range("C30") ' assumes  shift in B30

      strName = Sheets("ALL APP LINKS").Range("B31")

      Dr = Sheets("Tabelle1").Range("AF1").Value

      DrNum = Sheets("Tabelle1").Range("AE1").Value

      FileName1 = "DriverReport.frm"

      path = "C:\Users\taneviv\Documents\DIGITAL TEST FORMS\"

    

      ThisWorkbook.VBProject.VBComponents("DriverReport").Export _

      fileName:="C:\Users\taneviv\Documents\DIGITAL TEST FORMS\DriverReport.frm"


      fn = strPath & "\" & strFname & "_" & strVehNum & "_" & sDate & "_" & strShift & "_" & strName & "_" & Dr & "_" & DrNum & ".xlsm"

If Dir(fn) <> "" Then

End If


With Workbooks.Add(xlWBATWorksheet)

        ThisWorkbook.Sheets(Array("Tabelle1")).Copy after:=.Sheets(1)


        Application.DisplayAlerts = False

        .Sheets(1).Delete

        Application.DisplayAlerts = True

        On Error Resume Next

        Kill fn

        .SaveAs fn, xlOpenXMLWorkbookMacroEnabled

    

    .Close False

End With

 Call importfrm
 

    myValWU = Val(LBWU1.Caption)

    myValWU = myValWU + 1

    LBWU1.Caption = myValWU
 

    Sheets("Tabelle1").Range("AE1").Value = LBWU1.Caption

    Sheets("Tabelle1").Range("A2:U99,W2:AB99").ClearContents

End Sub

 

[ITs65458]

This is the second part where I use to import the frm to the new workbook.

  Dim strVehNum As String, strName As String, strShift As String, strYear As String, strPath As String, sDate As String, strFname As String, DrNum As String
  Dim Dr As String
  Dim AllPath As Workbooks
  Dim AllPath1 As String
    Dim path As String
    Dim fileName As String

 
        strPath = Sheets("ALL APP LINKS").Range("B10")
        strFname = Sheets("ALL APP LINKS").Range("B14")
        strVehNum = Sheets("ALL APP LINKS").Range("B29") ' assumes vehicle number in B29
        sDate = Format(Sheets("ALL APP LINKS").Range("B28"), "YYYYMMDD")
        strShift = Sheets("ALL APP LINKS").Range("C30") ' assumes  shift in B30
        strName = Sheets("ALL APP LINKS").Range("B31")
        Dr = Sheets("Tabelle1").Range("AF1").Value
        DrNum = Sheets("Tabelle1").Range("AE1").Value
       AllPath1 = strPath & "\" & strFname & "_" & strVehNum & "_" & sDate & "_" & strShift & "_" & strName & "_" & Dr & "_" & DrNum & ".xlsm"


    Dim path As String
    Dim fileName As String
    Workbooks.Open AllPath1
    path = "C:\Users\taneviv\Documents\DIGITAL TEST FORMS\"
    
    fileName = "OpenReportForm.frm"
    
    ThisWorkbook.VBProject.VBComponents.Import path & fileName
    
    ThisWorkbook.Close SaveChanges:=True

End Sub

 

[GWteB]

You're attempting to re-import the form in your current workbook, i.e. the workbook your code is running, so try replacing this snippet...

    Workbooks.Open AllPath1
    path = "C:\Users\taneviv\Documents\DIGITAL TEST FORMS\"
    
    fileName = "OpenReportForm.frm"
    
    ThisWorkbook.VBProject.VBComponents.Import path & fileName
    
    ThisWorkbook.Close SaveChanges:=True

with this part

    Dim oWb As Workbook
    Set oWb = Workbooks.Open(AllPath1)
    
    Path = "C:\Users\taneviv\Documents\DIGITAL TEST FORMS\"
    
    Filename = "OpenReportForm.frm"
    
    oWb.VBProject.VBComponents.Import Path & Filename
    
    oWb.Close SaveChanges:=True

 

[ITs65458]

You're attempting to re-import the form in your current workbook, i.e. the workbook your code is running, so try replacing this snippet...

    Workbooks.Open AllPath1
    path = "C:\Users\taneviv\Documents\DIGITAL TEST FORMS\"
   
    fileName = "OpenReportForm.frm"
   
    ThisWorkbook.VBProject.VBComponents.Import path & fileName
   
    ThisWorkbook.Close SaveChanges:=True

with this part

    Dim oWb As Workbook
    Set oWb = Workbooks.Open(AllPath1)
   
    Path = "C:\Users\taneviv\Documents\DIGITAL TEST FORMS\"
   
    Filename = "OpenReportForm.frm"
   
    oWb.VBProject.VBComponents.Import Path & Filename
   
    oWb.Close SaveChanges:=True

Hi GWteB and thanks for the reply. The open workbook follows the pat to the newly saved workbook. it opens the workbook and it should import the frm. to it. I'm sure you are correct in what you are saying. Can you let me know what edits you suggest. I recently added the separate module that opens the newly saved workbook as I thought my conflict might have been that was all structured in one. Ideally would be to have the export/import performed under one command during the creation of the new workbook and before it is saved, but I can't seem to get that part correct.

 

[ITs65458]

You're attempting to re-import the form in your current workbook, i.e. the workbook your code is running, so try replacing this snippet...

    Workbooks.Open AllPath1
    path = "C:\Users\taneviv\Documents\DIGITAL TEST FORMS\"
   
    fileName = "OpenReportForm.frm"
   
    ThisWorkbook.VBProject.VBComponents.Import path & fileName
   
    ThisWorkbook.Close SaveChanges:=True

with this part

    Dim oWb As Workbook
    Set oWb = Workbooks.Open(AllPath1)
   
    Path = "C:\Users\taneviv\Documents\DIGITAL TEST FORMS\"
   
    Filename = "OpenReportForm.frm"
   
    oWb.VBProject.VBComponents.Import Path & Filename
   
    oWb.Close SaveChanges:=True

sorry, I follow what you saying now. I had it opened on my phone and was unable to see the full reply. i will give it a try.
Thank you

 

[ITs65458]

You're attempting to re-import the form in your current workbook, i.e. the workbook your code is running, so try replacing this snippet...

    Workbooks.Open AllPath1
    path = "C:\Users\taneviv\Documents\DIGITAL TEST FORMS\"
   
    fileName = "OpenReportForm.frm"
   
    ThisWorkbook.VBProject.VBComponents.Import path & fileName
   
    ThisWorkbook.Close SaveChanges:=True

with this part

    Dim oWb As Workbook
    Set oWb = Workbooks.Open(AllPath1)
   
    Path = "C:\Users\taneviv\Documents\DIGITAL TEST FORMS\"
   
    Filename = "OpenReportForm.frm"
   
    oWb.VBProject.VBComponents.Import Path & Filename
   
    oWb.Close SaveChanges:=True

That worked!! Thanks a lot for the quick replay and for pointing me in the right direction.

 

[GWteB]

You're welcome and thanks for letting me know.