millmo
08-21-2019, 09:09 AM
Hi,
I've got a macro written that exports an array of sheets from a workbook to CSV, however I've tried and failed to define the range it should export, rather than whatever excel defines as the default range. The code selects the desired range but then doesn't just copy and save it. Here's the code:
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", "13 Postcode SP", "15 Risk Score SP", "19 Flat Fee"))
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
Any help would be much appreciated!
(The context of this is I'm exporting to CSV so I can import to SAS, and I need to have all the data - the excel-defined range sometimes cuts off data, and other times makes the files way too big)
I've got a macro written that exports an array of sheets from a workbook to CSV, however I've tried and failed to define the range it should export, rather than whatever excel defines as the default range. The code selects the desired range but then doesn't just copy and save it. Here's the code:
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", "13 Postcode SP", "15 Risk Score SP", "19 Flat Fee"))
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
Any help would be much appreciated!
(The context of this is I'm exporting to CSV so I can import to SAS, and I need to have all the data - the excel-defined range sometimes cuts off data, and other times makes the files way too big)