Solved

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

Posted on 2016-08-22
4
18 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
[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
  • 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

Industry Leaders: 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

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…
I met Paul Devereux (@pdevereux) today when I responded to his tweet asking “Anybody know how to automate adding files from disk to a folder in #outlook  ?”.  I replied back and told Paul that using automation, in this case scripting, to add files t…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

617 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