Creating CSV with VBA to much cells

Question

I have an vba macro that should (after updating some values from an online xml) save one sheet to a CSV file. This works, but if I open the CSV file, I see this:

CHF,Swiss Franc,1.2352,,,
CNY,Yuan Renminbi,8.1803,,,
EUR,Euro,1,,,
GBP,Pound Sterling,0.8585,,,
HKD,Hong Kong Dollar,10.3617,,,
SGD,Singapore Dollar,1.7097,,,
USD,US Dollar,1.3361,,,
,,,,,
,,,,,

So the problem is that three empty colomns and two empty rows are also saved. I know this raises no problems after opening the CSV file in Excel, but in the program I import it, it does.

I did some research and found that this probably has to do with the UsedRange. But i could find no way to set the usedRange to a new value, or only save a small part of the sheet.

I probably can copy the range I want, open a new workbook, paste this range and save this to a file, but this seems a bit harder than it should be. And it does not guarantee that my UsedRange in this new workbook is not also to big.

Does anyone know how to set the usedrange or do the things I want another way? I am an absolute VBA amateur, so if I did strange things, thats the reason.

The rest of my code works fine, so the problem is only in the extra comma's in the saved file.

My VBA code is below:

Sub updateCurrencies()
'
' updateCurrencies Macro
'
' Keyboard Shortcut: Ctrl+e
'
Application.DisplayAlerts = False
Dim wb As Workbook

Dim ws As Worksheet
Dim file As String
 file = "\\SBS2008\RedirectedFolders\jasper\Desktop\currencies.csv"
    Sheets("Sheet1").Select
    ActiveWorkbook.XmlMaps("Envelope_Map").DataBinding.Refresh
Set ws = Sheets("currencies")
ws.Select
    ActiveWorkbook.RefreshAll

    ws.SaveAs file, FileFormat:=6

    Sheets("info").Select
   Set wb = Workbooks.Open(file)

    wb.Save

    Sheets("info").Select
    Call CloseAllWorkbooks

End Sub

Public Sub CloseAllWorkbooks()

Dim wb As Workbook



For Each wb In Workbooks

If (wb.Name = "currencies") Then

    wb.Close True
Else

    wb.Close False ' Or True if you want changes saved
End If

Next wb



End Sub

empty values in an export are usually from cells with spaces, or some errant formatting (not saved with file, but adds to the used range). Try deleting the unused columns — SeanC

APrough APrough · Accepted Answer · 2013-08-26T17:50:44

See the answer here by Daniel Cook

Convert xls File to csv, but extra rows added?

Sub CorrectUsedRange()
Dim values
Dim usedRangeAddress As String
Dim r As Range
'Get UsedRange Address prior to deleting Range
usedRangeAddress = ActiveSheet.UsedRange.Address
'Store values of cells to array.
values = ActiveSheet.UsedRange
'Delete all cells in the sheet
ActiveSheet.Cells.Delete
'Restore values to their initial locations
Range(usedRangeAddress) = values
End Sub

Creating CSV with VBA to much cells

1 Answers