Solved

Save excel active worksheet as a csv file

Posted on 2013-01-28
5
699 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the 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…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

615 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