Conver to XLSM then import module - error 91

Scott Manning 0
Viewed 36

Why am I getting object not set error? Seems to be on this line with debug?

xlsmTarget.VBProject.VBComponents.Import "C:\New Name Test\Testv3\CreateUniqueContainers.bas"

However target seems to be set, what have I done wrong?

    Sub ConvertXLSFilesToXLSM()
Dim Path As String
Dim DestPath As String
Dim xlsmTarget As Workbook
Const ModulePath As String = "C:\New Name Test\Testv3\CreateUniqueContainers.bas"

Path = "C:\New Name Test\Testv3\Files\"
DestPath = "C:\New Name Test\Testv3\Files\"
WorkFile = Dir(myPath & "*.xls")

Do While WorkFile <> ""
    If Right(WorkFile, 4) <> "xlsm" Then
        Workbooks.Open Filename:=Path & "\" & WorkFile
        ActiveWorkbook.SaveAs Filename:= _
        DestPath & WorkFile & ".xlsm", 
    FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
        xlsmTarget.VBProject.VBComponents.Import ModulePath
        ActiveWorkbook.Save
        ActiveWorkbook.Close
     End If
     WorkFile = Dir()
    Loop
End Sub
automation vba excel
2 Answers
Option Explicit

Sub ConvertXLSFilesToXLSM()

    Const FOLDER = "C:\New Name Test\Testv3\"
    Const MODNAME = "CreateUniqueContainers.bas"
    
    Dim SrcPath As String, DestPath As String
    Dim SrcFile As String, DestFile As String
    Dim n As Long
    
    SrcPath = FOLDER & "Files\"
    DestPath = FOLDER & "Files\"
    SrcFile = Dir(SrcPath & "*.xls*")
    
    Do While SrcFile <> ""
        If Right(SrcFile, 4) <> "xlsm" Then
        
            Debug.Print SrcFile
            Workbooks.Open Filename:=SrcPath & "\" & SrcFile
            
            ' replace .xls or .xlsx with .xlsm
            DestFile = DestPath & Split(SrcFile, ".")(0) & ".xlsm"
                 
            ' save with new name
            With ActiveWorkbook
                .SaveAs Filename:=DestFile, _
                FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
                CreateBackup:=False
                
                ' import module and save
               .VBProject.VBComponents.Import FOLDER & MODNAME
               .Save
               .Close
            End With
            n = n + 1
        End If
         
        SrcFile = Dir()
    Loop
    MsgBox n & " files created", vbInformation
End Sub
CDP18020

You need to actually set your variable, and use it. Also, don't rely on Active*

Sub ConvertXLSFilesToXLSM()
    Dim Path As String
    Dim DestPath As String
    Dim xlsmTarget As Workbook

    Const ModulePath As String = "C:\New Name Test\Testv3\CreateUniqueContainers.bas"
    Path = "C:\New Name Test\Testv3\Files\"
    DestPath = "C:\New Name Test\Testv3\Files\"
    WorkFile = Dir(Path & "*.xls") 

    Do While WorkFile <> ""
        If Right(WorkFile, 4) <> "xlsm" Then
            Set xlsmTarget = Workbooks.Open(Path & WorkFile)
            xlsmTarget.SaveAs _
              Filename:=DestPath & replace(WorkFile, ".xls", ".xlsm"), _           
              FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
              CreateBackup:=False

 
            xlsmTarget.VBProject.VBComponents.Import ModulePath
            'xlsmTarget.Save
            xlsmTarget.Close, True
         End If
         WorkFile = Dir()
    Loop
End Sub
chris neilsen0
Related