Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    Open excel workbook save each worksheet as csv with tab name

    Dear All,

    I am looking for code that open excel file from specific path then create .csv of individual worksheet tab with tab names in specific folder?

    Thanks
    hammeed

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Hammeed,

    If I read your question correctly you have a workbook with multiple sheets {tabs} in a folder let's say C:\Documents\Excel\Hammeed.

    You want VBA code to open the workbook then save each sheet {tab} as a different .csv file, e.g. sheet1.csv, sheet2.csv, sheet3.csv and write them to a specified folder?

    If this is correct I have 2 questions:
    1. Is the .xls file always in the same directory or should the VBA allow the user to specify the folder?
    2. Are the resulting .csv files always written to the same folder or again should the user be able to specify the folder?

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    1. Is the .xls file always in the same directory or should the VBA allow the user to specify the folder?

    Answer 1: yes the .xlsx file is always in the same directory (hard coded please) e.g C:\Documents\Excel\Hammeed.


    2. Are the resulting .csv files always written to the same folder or again should the user be able to specify the folder?

    Answer 2: Yes, but different path e.g C:\Documents\Excel\Hammeed\Created CSV\Sheet1.csv

    Thanks
    hammeed
    Last edited by farrukh; 2011-08-13 at 07:02.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts

    Code to Write Sheets as Individual .CSV files

    Hammeed,
    Code:
    Option Explicit
    
    Sub WriteSheetsToCSV()
    
       Dim zSourcePath As String
       Dim zDestPath   As String
       Dim zTabName    As String
       Dim oSheet      As Worksheet
       
       zSourcePath = "C:\Documents\Excel\Hammeed\"
       zDestPath = zSourcePath & "Created CSV\"
       
       For Each oSheet In ActiveWorkbook.Sheets
          oSheet.Activate
          zTabName = oSheet.Name
          
            ActiveWorkbook.SaveAs Filename:= _
            zDestPath & zTabName & ".csv", FileFormat:=xlCSV, _
            CreateBackup:=False
    
       Next   'oSheet
    
    End Sub   'WriteSheetsToCSV
    My questions are:
    1. Is it always the same file name? If so can the macro reside in that file?
    2. Should the user be prompted for the file name?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    farrukh (2011-08-14)

  6. #5
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    SIR,


    1. Is it always the same file name? If so can the macro reside in that file?
    Ans: Yes the master.xlsx is the file name which is constant and yes macro reside the same file.

    2. Should the user be prompted for the file name?

    Ans: No the user does not prompt for file name ( the .csv path to write like C:\Documents\Excel\Hammeed\Created CSV\

    Thanks
    Hammeed

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ok Hammeed,

    All you need to do is open the Master.xlsx file.

    1. Resave it as Master.xlsm {Macro Enabled file}.
    2. Press Ctrl+F11 to open the VBA editor.
    3. In the Left pane click on VBAProject(Master.xlsm)
    4. Click Insert then Module.
    5. Copy & paste the code in Post #4 above into the open window on the right.
    6. Resave the file.


    When you Open Master.xlsm just press F8 and double-click WriteSheetsToCSV.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. The Following User Says Thank You to RetiredGeek For This Useful Post:

    farrukh (2011-08-14)

  9. #7
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    Lightbulb

    Sir,

    When i run this code getting error the error.zip is attached with...

    Thanks
    hammeed
    Attached Files Attached Files

  10. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Hammeed,

    I can't really tell from the screen shot what is wrong.
    Here are a couple of things you can check.

    1. Is Sheet1 Protected?
    2. Is the Workbook Protected?
    3. Is there a Sheet1.csv in the destination directory. I didn't have the macro check for that.
    4. Did you place the workbook in a Trusted Location. See the security settings for Excel.

    Let me know what you find out. If you want and the data isn't sensitive attach a copy of the workbook to your reply or send it to me in a private message.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. The Following User Says Thank You to RetiredGeek For This Useful Post:

    farrukh (2011-08-14)

  12. #9
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    RetiredGeek,

    Sir i have attached the master.xlsm please remove after copy...

    Thanks
    hammeed
    Attached Files Attached Files

  13. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Hammeed,

    Ok, I've copied the file but I can't delete it. You'll have to edit your post and remove the file.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. The Following User Says Thank You to RetiredGeek For This Useful Post:

    farrukh (2011-08-14)

  15. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Hammeed,

    The file you posted did not have the VBA included. I added the code from the previous post, of course I had to change the paths for my machine, but the macro worked and wrote the two csv files. Note: close the open Excel files before attempting to open one of the create csv files.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  16. The Following User Says Thank You to RetiredGeek For This Useful Post:

    farrukh (2011-08-14)

  17. #12
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    Thumbs up

    Sir RetiredGeek,

    Thanks alot i did the same and it worked for me

    You are highly appreciated

    Thanks
    hammeed

Posting Permissions

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