none
Export sheets and save in PDF or Excel Individual RRS feed

  • Question

  • Good day All

    I want to export the sheets into individual workbook form column selection, I have a data form column A to till column T with headers and macro should be,

    • When I Run macro, small window appear to ask for that
      1.  Which from column, I want to export.
      2. Export with Excel sheet or PDF or both (if i select both export in excel and pdf in both)
    • All sheet save with the name that which column I will select either column has date, numeric or alphabets.
    • In which folder my sheet exist for export all sheets should save in particular in folder.
    • In case of any special character macro change it into (-) automatically.

    Nabeel Gondal


    Wednesday, March 1, 2017 8:20 AM

Answers

  • Hi Nabeel Gondal,

    From the description of the thread it looks like you have bunch of requirements(around 6 requirements).

    here, I want to ask you one thing, can you show us what did you try till now to achieve these requirements?

    no one will give you the whole working solution.

    we will try to divide your requirements in to several parts and you need to combine it by yourself.

    (1) When I Run macro, small window appear to ask for that

    1.  Which from column, I want to export.
    2. Export with Excel sheet or PDF or both (if i select both export in excel and pdf in both)

    -> for this you can try to create a User Form, when you run macro User Form will display and ask the necessary details. something like below.

    Code to display User Form:

    Sub demo()
    UserForm1.Show
    End Sub

    (2) All sheet save with the name that which column I will select either column has date, numeric or alphabets.

    looks like you want to save the Sheet with the column name(title of the column), if yes then refer code below.

    Sub demo1()
    Sheets("Sheet2").Name = Sheets("Sheet1").Cells(1, 1).Value
    End Sub

    (3) In which folder my sheet exist for export all sheets should save in particular in folder.

    for this requirement you can give any static path saved in variable.

    the another way is to show the Open file Dialog box and then user will select for particular folder.

    Sub demo3()
    Dim fDialog As Object
    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
     
    'Optional: FileDialog properties
    fDialog.Title = "Select a folder"
    fDialog.InitialFileName = "C:\"
     
    If fDialog.Show = -1 Then
      Debug.Print fDialog.SelectedItems(1)
    End If
    
    End Sub

    (4) In case of any special character macro change it into (-) automatically.

    Sub demo4()
    Const SpecialCharacters As String = "!,@,#,$,%,^,&,*,(,),{,[,],}"  'modify as needed
    Dim myString As String
    Dim newString As String
    Dim char As Variant
    myString = "!p#*@)k{kdfhouef3829J"
    newString = myString
    For Each char In Split(SpecialCharacters, ",")
        newString = Replace(newString, char, "-")
    Next
     Debug.Print newString
    End Sub


    (5) export the sheets into individual workbook

    Sub SplitWorkbook()
    'Updateby20140612
    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 Val(Application.Version) < 12 Then
            FileExtStr = ".xls": FileFormatNum = -4143
        Else
            Select Case xWb.FileFormat
                Case 51:
                    FileExtStr = ".xlsx": FileFormatNum = 51
                Case 52:
                    If Application.ActiveWorkbook.HasVBProject Then
                        FileExtStr = ".xlsm": FileFormatNum = 52
                    Else
                        FileExtStr = ".xlsx": FileFormatNum = 51
                    End If
                Case 56:
                    FileExtStr = ".xls": FileFormatNum = 56
                Case Else:
                    FileExtStr = ".xlsb": FileFormatNum = 50
            End Select
        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
     

    Reference:

    How to export and save each worksheet as new workbook in Excel?

    VBA Save Sheet as Workbook Excel Macro Code

    How to split a workbook to separate Excel files in Excel?

    (6) Export worksheet as PDF.

    Sub ExportToPDFs()
    Dim ws As Worksheet
    Dim nm As String
    For Each ws In Worksheets
    ws.Select
    nm = ws.Name
     
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="C:\Users\v-padee\Desktop\" & nm & ".pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=False
     
    Next ws
     
    End Sub
    

    Reference:

    Excel VBA – Export Each Worksheet to a Separate PDF – Macro

    Excel VBA - Save As PDF Files

    How to use Command Button to save active worksheet as PDF file in Excel?

    How to Export an Excel Sheet as PDF file

    Hope , these examples gives you an idea to fulfil your requirements.

    now you can try to combine these code in to one and try to modify as per your need.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, March 2, 2017 2:07 AM
    Moderator

All replies

  • Hi Nabeel Gondal,

    From the description of the thread it looks like you have bunch of requirements(around 6 requirements).

    here, I want to ask you one thing, can you show us what did you try till now to achieve these requirements?

    no one will give you the whole working solution.

    we will try to divide your requirements in to several parts and you need to combine it by yourself.

    (1) When I Run macro, small window appear to ask for that

    1.  Which from column, I want to export.
    2. Export with Excel sheet or PDF or both (if i select both export in excel and pdf in both)

    -> for this you can try to create a User Form, when you run macro User Form will display and ask the necessary details. something like below.

    Code to display User Form:

    Sub demo()
    UserForm1.Show
    End Sub

    (2) All sheet save with the name that which column I will select either column has date, numeric or alphabets.

    looks like you want to save the Sheet with the column name(title of the column), if yes then refer code below.

    Sub demo1()
    Sheets("Sheet2").Name = Sheets("Sheet1").Cells(1, 1).Value
    End Sub

    (3) In which folder my sheet exist for export all sheets should save in particular in folder.

    for this requirement you can give any static path saved in variable.

    the another way is to show the Open file Dialog box and then user will select for particular folder.

    Sub demo3()
    Dim fDialog As Object
    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
     
    'Optional: FileDialog properties
    fDialog.Title = "Select a folder"
    fDialog.InitialFileName = "C:\"
     
    If fDialog.Show = -1 Then
      Debug.Print fDialog.SelectedItems(1)
    End If
    
    End Sub

    (4) In case of any special character macro change it into (-) automatically.

    Sub demo4()
    Const SpecialCharacters As String = "!,@,#,$,%,^,&,*,(,),{,[,],}"  'modify as needed
    Dim myString As String
    Dim newString As String
    Dim char As Variant
    myString = "!p#*@)k{kdfhouef3829J"
    newString = myString
    For Each char In Split(SpecialCharacters, ",")
        newString = Replace(newString, char, "-")
    Next
     Debug.Print newString
    End Sub


    (5) export the sheets into individual workbook

    Sub SplitWorkbook()
    'Updateby20140612
    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 Val(Application.Version) < 12 Then
            FileExtStr = ".xls": FileFormatNum = -4143
        Else
            Select Case xWb.FileFormat
                Case 51:
                    FileExtStr = ".xlsx": FileFormatNum = 51
                Case 52:
                    If Application.ActiveWorkbook.HasVBProject Then
                        FileExtStr = ".xlsm": FileFormatNum = 52
                    Else
                        FileExtStr = ".xlsx": FileFormatNum = 51
                    End If
                Case 56:
                    FileExtStr = ".xls": FileFormatNum = 56
                Case Else:
                    FileExtStr = ".xlsb": FileFormatNum = 50
            End Select
        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
     

    Reference:

    How to export and save each worksheet as new workbook in Excel?

    VBA Save Sheet as Workbook Excel Macro Code

    How to split a workbook to separate Excel files in Excel?

    (6) Export worksheet as PDF.

    Sub ExportToPDFs()
    Dim ws As Worksheet
    Dim nm As String
    For Each ws In Worksheets
    ws.Select
    nm = ws.Name
     
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="C:\Users\v-padee\Desktop\" & nm & ".pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=False
     
    Next ws
     
    End Sub
    

    Reference:

    Excel VBA – Export Each Worksheet to a Separate PDF – Macro

    Excel VBA - Save As PDF Files

    How to use Command Button to save active worksheet as PDF file in Excel?

    How to Export an Excel Sheet as PDF file

    Hope , these examples gives you an idea to fulfil your requirements.

    now you can try to combine these code in to one and try to modify as per your need.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, March 2, 2017 2:07 AM
    Moderator
  • Dear Deepak

    thanks, this is really good guidance, i will try to combine it

    Nabeel 

    Thursday, March 2, 2017 5:57 AM