VBA – File & Directory Handling

Create folder if not exist

Sub create_folder_()

'
folder_path = "C:\Users\aashish mittal\Downloads\learnskill"

If Dir(folder_path, vbDirectory) <> "" Then
MsgBox "exist"
Else
MsgBox "not exist"
MkDir folder_path
End If

End Sub

Create file if not exist

Sub check_file_exist()

filePath = "C:\Users\aashish mittal\Downloads\learnskill\company.xlsx"

If Dir(filePath, vbArchive) <> "" Then
MsgBox "File exist"
Else
MsgBox "File not exist"

End If
End Sub

Create Workbook – xlsx and xlsm

Sub CreateAndSaveWorkbook()
Dim newBook As Workbook

' Create a new workbook
Set newBook = Workbooks.Add

' Use a variable for the path and filename
Dim filePath As String
filePath = "C:\Users\aashish mittal\Downloads\learnskill\company.xlsm"  

With newBook
    ' Save the excel normal workbook with the specified path and filename
    '.SaveAs Filename:=filePath, FileFormat:=xlOpenXMLWorkbook
    'save macroeanbled excel book
    ActiveWorkbook.SaveAs Filename:=filePath, FileFormat:=xlOpenXMLWorkbookMacroEnabled

    ' Set document properties
    .BuiltinDocumentProperties("Title").Value = "My Company Workbook"
    .BuiltinDocumentProperties("Subject").Value = "VBA Created"
End With

' Close the new workbook (you can comment this out if you want to keep it open)
newBook.Close SaveChanges:=False

' Clean up the object variable
Set newBook = Nothing

MsgBox "Workbook saved and properties set!"
End Sub

Add worksheet and new columns header

Sub AddNewSheetAndHeaders()
Dim ws As Worksheet

' Add a new worksheet and set it as the active sheet
Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))

' Optionally, rename the new worksheet
ws.Name = "info"

' Add column headers to the first row of the new sheet
With ws
    .Cells(1, 1).Value = "id"
    .Cells(1, 2).Value = "email"
    .Cells(1, 3).Value = "location"
    ' Add more headers as needed
End With

End Sub