VBA: SaveAs method creates infinite csv file

Question

I am looping through several worksheets in a file, copying information to another sheet (called wsCSV, which is stored in ThisWorkbook) and then I want to create a CSV file from this worksheet. After creating the sheet, the loop should continue to the next sheet.

Here is part of the code which should create the CSV:

' Create CSV file
If iControl = 1 Then
    Application.DisplayAlerts = False
    wsCSV.Copy
    ActiveWorkbook.SaveAs Filename:=sDestin & "\" & sShName & ".csv", FileFormat:=xlCSV, CreateBackup:=True
    ActiveWorkbook.Close SaveChanges:=False
    Application.DisplayAlerts = True
End If

Anyway, when I proceed the code, SaveAs method starts creating "infinite" file, till it consumes all my disk space. I don't know how is it possible, as the file has 6 rows and 3 columns, so it should have 1-2 kb. I have to stop the process manually. Any thoughts? I tried this code in another macro and it worked OK.

Thanks!

Comment out the Application.DisplayAlerts = False line until you have it working - it may be hiding some useful information. Step through your code in debug mode to see what's happening. Considering how small a snippet of code you've provided, I'd guess that whatever wsCSV is, it's much larger than you think. — FreeMan
FreeMan: Commenting out Alerts statement didn't help, I've considered this option before. wsCSV is a small worksheet, with values pasted in A1:C6. When I open the created csv file, even it has only these values filled, it has many gigabytes in size (if I stop the process soon) — Gateux
Commenting that line out won't fix anything, but it's a good idea to check to see if it's been hiding any alerts that would tell you what's going on - it's always a good first step in debugging. Are you opening the .CSV in Excel or in Notepad to look at it? If in Excel, hit Ctrl-End to see what the last used cell is. Try in Notepad (or Notepad++ or other text editor - multi GiB may be too big for Notepad), Look to see if there's some sort of unexpected random garbage at the end. Also, use Ctrl-End to ensure that wsCSV is only as full as you think it is. — FreeMan
Yes, this has worked! I don't know why, but with Ctrl-End I got that all the cells in wsCSV were used (although only few of them contained values). Resetting the wsCSV sheet did work. Thank you! — Gateux

FreeMan FreeMan · Accepted Answer · 2017-09-11T14:56:53

Make sure that your wsCSV worksheet is truly as empty as you think it is. Use Ctrl-End to see what the last used cell is and clean it up if necessary.

VBA: SaveAs method creates infinite csv file

1 Answers