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

x
?
Solved

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

Posted on 2016-08-22
4
Medium Priority
?
20 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 2000 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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

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