Solved

How to save two excel tabs as .csv with a macro?

Posted on 2016-08-22
4
14 Views
Last Modified: 2016-09-24
I have an excel spreadsheet with 5 tabs. I am looking for a macro that I can run and it will save two of the tabs to a specific location on my network. The two tabs I want to save as a CSV are "Sales1" and "Sales2". I am wanting to save those tabs to "R:\Sales\" folder. I would like them to be a separate csv. So one would be called "Sales1.csv" and the other "Sales2.csv". Thanks!
0
Comment
Question by:brasiman
  • 3
4 Comments
 
LVL 18

Accepted Solution

by:
xtermie earned 500 total points (awarded by participants)
ID: 41765479
Something like:
  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 = "R:\Sales\"
   For Each WS In ThisWorkbook.Worksheets
   If WS.Name = "Sales1" Or WS.Name = "Sales2" Then
         WS.SaveAs SaveToDirectory & WS.Name, xlCSV
   End If
   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
 

Author Comment

by:brasiman
ID: 41765498
Thank you xtermie! That is perfect!!!! A side question. In the future I might want to change the file name to something different when I save the Sales1 and Sales2 tabs. I'm thinking in the future, I will have Sales1 save as "Sales1Export" and the Sales2 tab as "Sales2Export". Just so I can differentiate the reports vs exports. Is that possible?
0
 
LVL 18

Assisted Solution

by:xtermie
xtermie earned 500 total points (awarded by participants)
ID: 41785355
Yes u can
In the above code code just set the new name as follows

For Each WS In ThisWorkbook.Worksheets
      If WS.Name = "Sales1" Or WS.Name = "Sales2" Then
      Myfilenane = WS.Name & "_export"      
WS.SaveAs SaveToDirectory &  Myfilename, xlCSV
   End If
   Next
0
 
LVL 18

Expert Comment

by:xtermie
ID: 41813562
Solution provided and author verified the solution was good
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Script to copy or move mouse-selected collection of files plus targets referenced by shortcuts (.lnk) The purpose of this article is to help illuminate the real challenges and options available (where they may exist) for utilizing simple scriptin…
Welcome back!  My apologies for taking so long to write part two of this series; it's been a long time coming!  As I promised in Part 1, this article will focus on how to locate those elusive AD properties that you are searching for.  Why is this us…

766 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