Solved

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

Posted on 2016-08-22
4
15 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Introduction During my participation as a VBScript contributor at Experts Exchange, one of the most common questions I come across is this: "I have a script that runs against only one computer. How can I make it run against a list of computers in …
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 recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

735 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