Results 1 to 3 of 3

Thread: Split Workbook In To Sheets, Export To Folder and Move File

  1. #1
    Junior Member
    Join Date
    May 2013
    Posts
    2
    Rep Power
    0

    Split Workbook In To Sheets, Export To Folder and Move File

    Hi all - I currently have a macro that exports all "Sheets" to separate CSV's based on the sheet name (See Below for script). I would like to add a step prior to create a folder based on two cells with a "-" between such as C2-B2. The current script will create the exports within the same folder that the .XLSM file is located. I would like the script to create the new folder within parent folder where the workbook is located and then export the CSVs into that newly created folder. Also, I would like the script at the end to copy a file called "header.txt" from the parent folder into the newly created folder.

    Oh I forgot - Is there any way to prevent the export script from exporting the first sheet only?

    Any assistance is appreciated!!

    Current Export Macro---
    Code:
    Sub CSVExportTest2()
    
    Dim tmpWS As Worksheet
    Application.DisplayAlerts = False
    For Each WS In ThisWorkbook.Worksheets
    
            filePath = exportPath & "" & WS.Name & ".csv"
    
            WS.Copy
            Set tmpWS = ActiveSheet
            tmpWS.SaveAs Filename:=filePath, FileFormat:=xlCSV
            tmpWS.Parent.Close False
    Next
    
    End Sub
    Last edited by Excel Fox; 05-22-2013 at 11:50 AM. Reason: Code Tags Added

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    I'm sure you can modify this to suit your need.

    Code:
    Sub CSVExportTest2()
    
        Dim tmpWS As Worksheet
        Application.DisplayAlerts = False
        Dim strPath As String, filePath As String
        strPath = ThisWorkbook.Path & "\" & ThisWorkbook.Range("B2").Value & "-" & ThisWorkbook.Range("B2").Value
        MkDir strPath
        For Each ws In ThisWorkbook.Worksheets
            If ws.Index > 1 Then
                filePath = exportPath & "" & ws.Name & ".csv"
                ws.Copy
                Set tmpWS = ActiveSheet
                tmpWS.SaveAs Filename:=filePath, FileFormat:=xlCSV
                tmpWS.Parent.Close False
            End If
        Next
        Name ThisWorkbook.Path & "\header.txt" As strPath & "\header.txt"
    
    
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    May 2013
    Posts
    2
    Rep Power
    0
    Thanks for the code! I am although getting a compile error stating "Method or data member not found" at the ".Range" in the strPath line.

Similar Threads

  1. Replies: 6
    Last Post: 05-20-2013, 10:06 PM
  2. Replies: 7
    Last Post: 05-17-2013, 10:38 PM
  3. Replies: 7
    Last Post: 05-08-2013, 07:12 PM
  4. Replies: 1
    Last Post: 02-14-2013, 12:09 PM
  5. Find Parent Folder From Given Folder / File Path
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 05-28-2011, 03:50 PM

Posting Permissions

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