VBA code to save excel file to csv

Question

I have the following code to create seperate files and save them as csv. But some how I am getting awarning: "the File Format and extension dont match. The file could be corrupt or unsafe. Can you please help me to correct this?

Sub Button3_Click()
 Dim wb As Workbook
 Dim ThisSheet As Worksheet
 Dim NumOfColumns As Integer
 Dim RangeToCopy As Range
 Dim RangeOfHeader As Range        'data (range) of header row
 Dim WorkbookCounter As Integer
 Dim RowsInFile         'how many rows (incl. header) in new files?

 Application.ScreenUpdating = False

 'Initialize data
 Set ThisSheet = ThisWorkbook.ActiveSheet
 NumOfColumns = ThisSheet.UsedRange.Columns.Count
 WorkbookCounter = 1
 RowsInFile = 11  'as your example, just 10 rows per file

 'Copy the data of the first row (header)
 Set RangeOfHeader = ThisSheet.Range(ThisSheet.Cells(1, 1), 
 ThisSheet.Cells(1, NumOfColumns))

 For p = 2 To ThisSheet.UsedRange.Rows.Count Step RowsInFile - 1
 Set wb = Workbooks.Add

 'Paste the header row in new file
 RangeOfHeader.Copy wb.Sheets(1).Range("A1")

 'Paste the chunk of rows for this file
 Set RangeToCopy = ThisSheet.Range(ThisSheet.Cells(p, 1), ThisSheet.Cells(p + RowsInFile - 2, NumOfColumns))
 RangeToCopy.Copy wb.Sheets(1).Range("A2")

'Save the new workbook, and close it
wb.SaveAs ThisWorkbook.Path & "\test" & WorkbookCounter
wb.Close SaveChanges:=False

'Increment file counter
WorkbookCounter = WorkbookCounter + 1
Next p

Application.ScreenUpdating = True
Set wb = Nothing
End Sub

user4039065 · Accepted Answer · 2018-07-26 19:39:31Z

2

Tell it explicitly that you want CSV files.

...
application.displayalerts = false
wb.SaveAs filename:=ThisWorkbook.Path & "\test" & WorkbookCounter, _
          fileformat:=xlCSV
wb.close savechanges:=false
application.displayalerts = true
...

answered Jul 26, 2018 at 19:39

user4039065

Sign up to request clarification or add additional context in comments.

1 Comment

Marc Over a year ago

@visu84, It's generally unhelpful to write "the code is throwing an error" without describing the error.

Collectives™ on Stack Overflow

VBA code to save excel file to csv

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related