27958

Export all but certain excel sheets to CSV via VBA?

Question:

Based on some other stuff I found here, I have made the following script to do almost exactly what I want. It will export all but 4 specific sheets in an excel file to CSV files, append dates to them, and save them to dated folders. The only problem is it renames the sheets it exported in the original processing file. How can I rectify this?

Sub SaveLCPWorksheetsAsCsv() 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 = "C:\test\" & Format(Date - 1, "YYYYMM") & "\" If Len(Dir(SaveToDirectory, vbDirectory)) = 0 Then MkDir SaveToDirectory End If For Each WS In ThisWorkbook.Worksheets If WS.Name <> "Input" And WS.Name <> "Ref" And WS.Name <> "Total" And WS.Name <> "Affected Accounts" Then WS.SaveAs SaveToDirectory & WS.Name & "_" & Format(Date - 1, "YYYYMMDD"), 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

Answer1:

Sub Tester() Dim ws As Worksheet, wb As Workbook For Each ws In ThisWorkbook.Worksheets ws.Copy 'creates a new workbook With ActiveWorkbook .SaveAs "C:\temp\" & ws.Name & "_blah.csv", xlCSV .Close False End With Next ws End Sub

Recommend

  • Export graphs from Excel
  • Runs as a macro on visual basic for windows application - Access Database
  • VBA 6 : regex not recognizing complicated string
  • Str adds extra space [duplicate]
  • Excel VBA: search a string to find the first non-text character
  • not able to Open Powerpoint using VBA
  • In excel have to search for specific string in cell and apply style to that particular string
  • Insert text before and after selection and set style of new text
  • Use text from record to paste into an Access form controlbox
  • How to save digital signature of received mail
  • Julia: How to give multiple workers access to functions in a module?
  • FileDialog persists previous filters
  • Multiplying arrays with scalars and adding in VBA
  • asp.net GridView IF empty show message
  • Subsetting data frames in R
  • Reading HTML file in VBA Excel
  • How to get cell reference from range object in Excel using VB.net?
  • Sort macro and data validation macro
  • Workbook Permissions in VBA Macros
  • ADO Command Parameter Not Passing to Stored Procedure or Stored Procedure 'Ignoring' Param
  • System.Timers.Timer (.NET) really slow?
  • .Net Compact Framework 3.5 HTTP POST
  • Execute SQL Server stored procedure from VBA and retrieve all messages and result sets
  • Create an object whose type is in a string parameter
  • Tensorflow not predicting accurate enough results
  • Format DateAsOrdinal xAxis labels in ZedGraph
  • Copying and renaming unopened workbook in excel
  • Unselect column after pasting data
  • Convert DotNetZip ZipFile to byte array
  • VB.NET Iterating through objects of a structure
  • Alter Table doesn't work under MS Access 64 bit. Why?
  • Sorting a DataTable using LINQ, when sort-by-columns may vary
  • Passing Data between VB.NET forms
  • Pasting URLs That Have Been Scraped From a Webpage
  • how to know the last row filled in vba (excel)? [duplicate]
  • Align Excel cell to center VB - xlCenter is not declared
  • Double-click autofill - dynamic based on adjacent cell
  • Excel VBA URLDownloadToFile Error for https Ressource
  • Creating a Multidimensional, Associative Array in VBScript
  • Conversion from string “a” to type 'Boolean' is not valid