Saving excel worksheet to CSV files with filename+worksheet name using VB

Question

I am very new with VB coding, I am trying to save multiple excel file worksheets to csv, I don't know to do this for multiple sheets, but I found a way to do for single file. I have found code on this site which are very useful for what I am trying to do, only problem is the files are saved with the worksheet name but I am trying to save them with the original file and worksheet name such as filename_worksheet name, I tried to do that myself but keep getting error, could you please advise what I am doing wrong?

The code I am using is as follows:

   Public Sub SaveWorksheetsAsCsv()

   Dim WS As Excel.Worksheet
   Dim SaveToDirectory As String

   Dim CurrentWorkbook As String
   Dim CurrentFormat As Long

   CurrentWorkbook = ThisWorkbook.FullName
   CurrentFormat = ThisWorkbook.FileFormat
   ' Store current details for the workbook
   SaveToDirectory = "H:\test\"
   For Each WS In ThisWorkbook.Worksheets
   WS.SaveAs SaveToDirectory & WS.Name, xlCSV
   Next

Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
Application.DisplayAlerts = True
' Temporarily turn alerts off to prevent the user being prompted
'  about overwriting the original file.

End Sub

score 47 · Accepted Answer · edited Jul 19 '19 at 07:05

47

I think this is what you want...

Sub SaveWorksheetsAsCsv()

Dim WS As Excel.Worksheet
Dim SaveToDirectory As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long

CurrentWorkbook = ThisWorkbook.FullName
CurrentFormat = ThisWorkbook.FileFormat
' Store current details for the workbook
SaveToDirectory = "H:\test\"

For Each WS In Application.ActiveWorkbook.Worksheets
    WS.SaveAs SaveToDirectory & WS.Name, xlCSV
Next

Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
Application.DisplayAlerts = True
' Temporarily turn alerts off to prevent the user being prompted
'  about overwriting the original file.

End Sub

edited Jul 19 '19 at 07:05

lbom

3
2

answered May 11 '12 at 13:00

Ben Strombeck

1,509
1
17
22

What about format of cells? If a cell of type date is formatted as DD/MM then on saving as CSV results as DD/MM and the Year part is skipped. Can you please advice? – bjan Jun 26 '13 at 09:10
1

Yes, that is how CSV works. It's strips everything except the plain text that is showing as the value of the cell. I'm not exactly sure what you're asking for, but if you open a new question and link it here in a comment, I'd be happy to look at it for you. – Ben Strombeck Jun 26 '13 at 12:54
1

I have changed Windows7 line separator to ';' but this macro is saving the .csv with a ',' separator instead. Is there a way to fix this? – RogueDeus Apr 12 '14 at 23:29
1

are you saying you already tried what is in this article here? http://social.technet.microsoft.com/wiki/contents/articles/10305.how-to-convert-format-excel-to-csv-with-semicolon-delimited.aspx I just tried it on my Windows 7 machine, and it worked. – Ben Strombeck Apr 18 '14 at 19:43
The code above works perfectly with one minor flaw; the resulting file is not saved with a .csv extension. – Tensigh Jun 15 '14 at 10:35
@Tensigh *This is a comment from [RazaJ77](http://stackoverflow.com/users/3305162/razaj77):* I added the following to code and it saved my file as a csv: `ActiveWorkbook.SaveAs Filename:=SaveToDirectory & ThisWorkbook.Name & "-" & WS.Name & ".csv", FileFormat:=xlCSV` – Artjom B. Jun 17 '14 at 16:25
Thanks, I went ahead and changed the line in the code above so that it correctly creates it as a .csv – Ben Strombeck Jun 17 '14 at 20:11
Thanks, I did the same thing when I used it. The code works really well! – Tensigh Jun 17 '14 at 23:18
Hi Ben, thanks for sharing this code. I intend to use it without giving you attribution. (Technically all code on Stack Overflow is supposed to be "attributed", but I am letting you know I do not intend to give attribution for this). Please let me know if this is a problem. – D.Tate Aug 19 '14 at 22:25
1

@D.Tate, no worries! You're welcome. Thanks for letting me know it's useful for you. That always feels good! Don't worry, all code on SO is free to use, and you don't need to do any attribution :-) – Ben Strombeck Aug 22 '14 at 15:24

score 9 · Answer 2 · edited Mar 25 '14 at 10:14

I had a similar problem. Data in a worksheet I needed to save as a separate CSV file.

Here's my code behind a command button

Private Sub cmdSave()
    Dim sFileName As String
    Dim WB As Workbook

    Application.DisplayAlerts = False

    sFileName = "MyFileName.csv"
    'Copy the contents of required sheet ready to paste into the new CSV
    Sheets(1).Range("A1:T85").Copy 'Define your own range

    'Open a new XLS workbook, save it as the file name
    Set WB = Workbooks.Add
    With WB
        .Title = "MyTitle"
        .Subject = "MySubject"
        .Sheets(1).Select
        ActiveSheet.Paste
        .SaveAs "MyDirectory\" & sFileName, xlCSV
        .Close
    End With

    Application.DisplayAlerts = True
End Sub

This works for me :-)

score 2 · Answer 3 · answered May 11 '12 at 12:58

2

Is this what you are trying?

Option Explicit

Public Sub SaveWorksheetsAsCsv()
    Dim WS As Worksheet
    Dim SaveToDirectory As String, newName As String

    SaveToDirectory = "H:\test\"

    For Each WS In ThisWorkbook.Worksheets
        newName = GetBookName(ThisWorkbook.Name) & "_" & WS.Name
        WS.Copy
        ActiveWorkbook.SaveAs SaveToDirectory & newName, xlCSV
        ActiveWorkbook.Close Savechanges:=False
    Next
End Sub

Function GetBookName(strwb As String) As String
    GetBookName = Left(strwb, (InStrRev(strwb, ".", -1, vbTextCompare) - 1))
End Function

answered May 11 '12 at 12:58

Siddharth Rout

147,039
17
206
250

Hi Siddharth, I have tried as you suggested but keep giving me errors, but dont worry the other answer actually sute more to waht I am looking for, thank you so much for offering help :) – Achak May 11 '12 at 13:22
Sure not a problem but i would like to know what errors were you getting as the code that i posted above is tried and tested and does what you want :) – Siddharth Rout May 11 '12 at 13:33

score 1 · Answer 4 · answered May 11 '12 at 13:04

1

Best way to find out is to record the macro and perform the exact steps and see what VBA code it generates. you can then go and replace the bits you want to make generic (i.e. file names and stuff)

answered May 11 '12 at 13:04

Bek Raupov

3,782
3
24
42

score 1 · Answer 5 · edited Jun 17 '14 at 16:59

1

The code above works perfectly with one minor flaw; the resulting file is not saved with a .csv extension. – Tensigh 2 days ago

I added the following to code and it saved my file as a csv. Thanks for this bit of code.It all worked as expected.

ActiveWorkbook.SaveAs Filename:=SaveToDirectory & ThisWorkbook.Name & "-" & WS.Name & ".csv", FileFormat:=xlCSV

edited Jun 17 '14 at 16:59

Uri Agassi

36,848
14
76
93

answered Jun 17 '14 at 16:01

RazaJ77

19
1

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient [reputation](http://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](http://stackoverflow.com/help/privileges/comment). – Artjom B. Jun 17 '14 at 16:26
I added your code comment to the appropriate position. – Artjom B. Jun 17 '14 at 16:26

Saving excel worksheet to CSV files with filename+worksheet name using VB

5 Answers5

Linked

Related