CODEWITHSUNDEEP
vbaexcel
Aman Devrath
Aman Devrath

Reputation: 406

Export worksheets as separate files by using userform and browse for file path

I know export worksheets as separate files has been asked before too. I checked those answers but I couldn't find solution for my problems, hence asked new question.

Code :

Private Sub cancel_button_Click()
Unload Me
End Sub

Private Sub export_button_Click()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim xWs As Worksheet
Dim xWb As Workbook
Dim FolderName As String
Application.ScreenUpdating = False
Set xWb = Application.ThisWorkbook
DateString = Format(Now, "yyyy-mm-dd hh-mm-ss")
FolderName = xWb.Path & "\" & xWb.Name & " " & DateString
MkDir FolderName

For Each xWs In xWb.Worksheets
    xWs.Copy
        If xlsx = True Then
            FileExtStr = ".xlsx": FileFormatNum = 51
            Unload Me
        ElseIf xlsm = True Then
                FileExtStr = ".xlsm": FileFormatNum = 52
                Unload Me
        ElseIf xls = True Then
            FileExtStr = ".xls": FileFormatNum = 56
            Unload Me
        ElseIf xlsb = True Then
            FileExtStr = ".xlsb": FileFormatNum = 50
            Unload Me
        End If
    xFile = FolderName & "\" & Application.ActiveWorkbook.Sheets(1).Name & FileExtStr
    Application.ActiveWorkbook.SaveAs xFile, FileFormat:=FileFormatNum
    Application.ActiveWorkbook.Close False
Next

MsgBox "You can find the files in " & FolderName
Application.ScreenUpdating = True
End Sub

Userform :
enter image description here

With above code and userform, I am able to work perfectly. I don't know the FileFormatNum for csv and txt, hence I could'nt create if loop for those two fileformat.

I need your help. I need to create a combobox in userform specifying around 15 fileformats. And also, a file browse button to select the export folder. User does both the selection(file format and location of folder for export).
I have created the userform:
enter image description here

  1. On click of browse button, a select folder dialog box should open up from which user selects a folder to export the worksheets. The path of the folder should be displayed in the textbox beside browse button.
  2. The combobox will display 15 fileformats. On selecting any one from the list, the sheets will be exported with that file extension.

How can I do it? I am not able to do it. Please anyone. Just give me a push and I'll finish it.

Upvotes: 0

Views: 321

Answers (1)

Kamran Mushtaq
Kamran Mushtaq

Reputation: 183

FileFormatNum for csv and txt are here.

  • csv : 20
  • txt (UniCode) : 42

https://msdn.microsoft.com/en-us/VBA/Excel-VBA/articles/xlfileformat-enumeration-excel

For eaisly finding xlfileformat just Go to the object browser in the VB window (Press F2) and then type xlFileFormat in the Search Text box.

Upvotes: 1

Related Questions