Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Save excel active worksheet as a csv file

Posted on 2013-01-28
5
Medium Priority
?
708 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

877 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