Results 1 to 4 of 4

Thread: Export all sheets as XLSX
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Export all sheets as XLSX

    I want to export all sheets as xlsx with vba, and save that macro in the personal workbook.
    I searched online for some code and then I modified it, so that I could select the folder in which I want to save the sheets in, and it works, but only if the macro is saved in the workbook I am working on, but it no longer works, if I save it in the personal workbook.
    This is the code I have:
    Code:
    Sub SplitWorkbook()
    'Updateby20140612
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim xWs As Worksheet
    Dim xWb As Workbook
    Dim FolderName As String
    Dim selectedfolder As String
    With Application.FileDialog(msoFileDialogFolderPicker)
    If .Show() = -1 Then
            selectedfolder = .SelectedItems(1)
            Application.ScreenUpdating = False
            Set xWb = Application.ThisWorkbook
            DateString = Format(Now, "yyyy-mm-dd hh-mm-ss")
            FolderName = selectedfolder & "\" & 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
    Else
    End If
    End With
    End Sub
    Any help to fix the issue with this macro and the personal workbook will be greatly appreciated.
    Thanks in advance.

  2. #2
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,360
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Export all sheets as XLSX

    Hello,

    You can test following modification

    Set xWb = ActiveWorkbook

    Hope this will help

  3. #3
    Board Regular
    Join Date
    Jun 2013
    Posts
    84
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export all sheets as XLSX

    That's exactly what I needed.
    You're the best.
    Thanks.

  4. #4
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,360
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Export all sheets as XLSX

    You are welcome

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •