Hi, I'm currently trying to convert some SAS code into VBA, as we no longer have the package which allows you to import from .xlsx, so I'm having to convert all my sheets to CSV before bringing them into SAS. I'm essentially trying to select some worksheets and define which columns to keep, as would be done in a one-line keep statements in SAS. Here's the VBA code so far: Sub ExportSheetsToCSV()
Dim xWs As Worksheet
Dim xcsvFile As String
For Each xWs In Application.ActiveWorkbook.Worksheets(Array("0 (C)", "2 PostcodeGroupingTable", "11 Region Based Loading CLS", "13 Postcode SP CLS", "15 Risk Score SP CLS", "19 Flat Fee Supplement"))
If xWs.Name = "0 (C)" Then xWs.Range("A1:A561,C1:C561").Select
Selection.Copy
'Change the file to suit the relevant destination folder
xcsvFile = "X:\Dept\_2019_05_16\Macro_Test" & "\" & xWs.Name & ".csv"
Application.ActiveWorkbook.SaveAs Filename:=xcsvFile, _
FileFormat:=xlCSV, CreateBackup:=False
Application.ActiveWorkbook.Saved = True
Application.ActiveWorkbook.Close
Next
End Sub I'm hoping then to copy the selection/range statement to have a defined range for each spreadsheet/csv separately. At the moment the process is working, however it's just ignoring the range thing completely. Sorry for testing your brains with VBA, the hope is someone knows an easy conversion!
... View more
