?
Solved

Save excel active worksheet as a csv file

Posted on 2013-01-28
5
Medium Priority
?
723 Views
Last Modified: 2013-04-21
Hi Experts!

I have an excel file with 4 worksheets. I have a button, on which when the user clicks is able to save the current active worksheet as a csv file. It would be fancy that there is a save as dialog where the user could choose the folder, but if that will be too much I would be happy to have the file saved in the same directory as the excel file with the name : activeWorksheet_currentDate.csv


What I have so far is this macro. However this saves all worksheets available in the file and I have a problem that I need to remove all the empty cell lines

Moreover I need to remove the first 2 lines in the exported csv file.

I am not a VBA expert so I put together this code from several locations. The problem I am facing when deleting and restoring rows is that the previous formating of the rows is gone. I have some olor backgrounds for delimiters and header names.

Private Sub CommandButton1_Click()

Dim WS As Excel.Worksheet
Dim SaveToDirectory As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long
Dim fileName As String

 CurrentWorkbook = ThisWorkbook.FullName
 CurrentFormat = ThisWorkbook.FileFormat
 
 fileName = "export_" + Format(Now(), "ddmmyyyy")
 
    Dim values
    Dim usedRangeAddress As String
    Dim r As Range
    'Get UsedRange Address prior to deleting Range
    usedRangeAddress = ActiveSheet.UsedRange.Address
    'Store values of cells to array.
    values = ActiveSheet.UsedRange
    'Delete all cells in the sheet
    'ActiveSheet.Cells.Delete
    'Restore values to their initial locations
    'Range(usedRangeAddress) = values
' Store current details for the workbook

      'SaveToDirectory = "C:\"

      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

Open in new window

0
Comment
Question by:dlcnet
  • 4
5 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 38830042
I see several issues here let me just recap them and tell me if I am wrong:

1) It would be fancy that there is a save as dialog where the user could choose the folder
2) However this saves all worksheets available in the file and I have a problem that I need to remove all the empty cell lines
3) Moreover I need to remove the first 2 lines in the exported csv file.
4) The problem I am facing when deleting and restoring rows is that the previous formating of the rows is gone

Is that correct ??
gowflow
0
 

Author Comment

by:dlcnet
ID: 38830122
Yes
This is the code I have so far. I would like to have those issues removed :) First I would like to save as csv only the active worksheet not all 4. Second when the csv file is generated I would like to remove the first 2 lines. Third. if there are empty cells I would like to hjave those removed from the file instead of having like ",,,,,,,,,,," lines
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38830275
ok fine let me work on soemthing and will revert.
gowflow
0
 
LVL 31

Accepted Solution

by:
gowflow earned 1500 total points
ID: 38836482
I looked at it and its all pretty straigt forward but to make it workable as cannot envision what you mean by Empty cells !!! as there maybe plenty and in CSV how can you delete a cell it will still be empty. !!! anyway I perfer if you could upload a sample workbook to look at it and build the macro accordingly.
gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38840105
Did you hv a chance to look at the request ?
gowflow
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question