Import userform using VBA

ITs65458

New Member
Joined
Nov 5, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

ITs65458

New Member
Joined
Nov 5, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
This is the second part where I use to import the frm to the new workbook.

VBA Code:
  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

Well-known Member
Joined
Sep 10, 2010
Messages
2,417
Office Version
  1. 2013
Platform
  1. Windows
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...
Rich (BB code):
    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
VBA Code:
    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
 
Solution

ITs65458

New Member
Joined
Nov 5, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
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...
Rich (BB code):
    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
VBA Code:
    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

New Member
Joined
Nov 5, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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...
Rich (BB code):
    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
VBA Code:
    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

New Member
Joined
Nov 5, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
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...
Rich (BB code):
    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
VBA Code:
    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

Well-known Member
Joined
Sep 10, 2010
Messages
2,417
Office Version
  1. 2013
Platform
  1. Windows
You're welcome and thanks for letting me know.
 

Forum statistics

Threads
1,147,568
Messages
5,741,874
Members
423,692
Latest member
Bhanu1988

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top