Solved

Save excel active worksheet as a csv file

Posted on 2013-01-28
5
648 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 29

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 29

Expert Comment

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

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 29

Expert Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

746 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now