VBA save xlsx as a csv file

[jak82]

Hi I have the folloowing script

Private Sub cmdXlsCsv_Click()

    Dim xls As Excel.Application
    Dim tmp As String
    Dim oWB As Excel.Workbook
    
    Set xls = New Excel.Application
    
    tmp = "C:\Users\cjack\Desktop\Copy of Book5.xlsx"
    
    Set oWB = xls.Workbooks.Open(tmp)
    oWB.SaveAs Filename:=Replace(tmp, ".xls", ".csv", , , vbTextCompare), FileFormat:=xlCSVMSDOS, CreateBackup:=False
    oWB.SaveAs Filename:=Replace(tmp, ".xls", ".csv", , , vbTextCompare), FileFormat:=xlCSVMSDOS, CreateBackup:=False
    oWB.Close
    xls.Quit
    
End Sub

However it is saving it as a csvx file instead of a csv file?

Any help appreciated.

 

[hotabae]

It probably has to do with how you are building the new filename.

You are editing the existing name with replace (.xls->.csv) which, in steps looks like this:

1. Copy of Book5.xlsx

2. Copy of Book5____x

3. Copy of Book5.csvx

I would recommend using this instead (edit in red):

oWB.SaveAs Filename:=Replace(tmp, [COLOR=#ff0000]".xlsx"[/COLOR], ".csv", , , vbTextCompare), FileFormat:=xlCSVMSDOS, CreateBackup:=False

 

[jak82]

Thanks, Works great!