Creating Directory and saving the excel file via VBA

Question

I'm Trying to create a directory in excel-VBA and then copy the xlsm and ,pdf file into this directory.

I can create the directory but I can't seem to save the files into this directory?

Code below. Any Help is much appreciated. This is driving me crazy. All I'm doing is concatenating the filename and it's being saved to the c:\temp folder, but I want it to save it into a sub folder in c:\temp

Sub Macro2()
'
' Macro2 Macro
'
Dim FileName As String
Dim FileName2 As String
'FileName3 As String


FileName = Sheet1.TextBox1.Text
FileName2 = ("C:\TEMP\" & FileName)
'CheckDir As String


MsgBox (FileName2)
MkDir (FileName2)


 ChDir (FileName2)

    ActiveWorkbook.SaveAs FileName:=FileName2 & FileName & "2xlsm.xlsm" _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
        FileName2 & "FileName" & "_2xlsm.pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True

End Sub

enter image description here

Answer 1

The format of FileName2 should be fixed to properly concatenate FileName in the file creation. Also, the FileName argument of both SaveAs and ExportAsFixedFormat should be the same, assuming you want both the XLSM and PDF files to be saved in one directory.

Please refer to the modified code below:

Sub Macro2()
'
' Macro2 Macro
'
    Dim FileName As String
    Dim FileName2 As String

    FileName = Sheet1.TextBox1.Text
    FileName2 = "C:\TEMP\" & FileName & "\"

    MsgBox (FileName2)
    MkDir (FileName2)

    ChDir (FileName2)

    ActiveWorkbook.SaveAs FileName:=FileName2 & FileName & "2xlsm.xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
        FileName2 & FileName & "_2xlsm.pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True
End Sub

Answer 2

Your missing & "\" & (backslash) on FileName2 & "\" & FileName, _

See Complete code

Option Explicit
Sub Macro2()
    '
    ' Macro2 Macro
    '
    Dim FileName As String
    Dim FileName2 As String


    FileName = Sheet1.TextBox1.Text
    FileName2 = ("C:\TEMP\" & FileName)

    MsgBox (FileName2)
    MkDir (FileName2)

    ActiveWorkbook.SaveAs FileName:= _
        FileName2 & "\" & FileName, _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
                                CreateBackup:=False

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
    FileName2 & "\" & "FileName", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub

Or simply do this

FileName2 = ("C:\TEMP\" & FileName & "\")