I'm looking to save a .csv file as an .xlsx file using VBA.

My VBA seems to disrupt the file when I change the file name from name.csv to name.xlsx and shows the following message when I try to open it:

    Sub SaveAnalysis()

    ArrearsAnalysis = "Early Arrears Analysis"

    For Each wb In Application.Workbooks
        If wb.Name Like ArrearsAnalysis & "*" Then
           Set AnalysisWB = Workbooks(wb.Name)
        End If
    Next wb
    
    Application.DisplayAlerts = False
    
    AnalysisWB.SaveAs Replace(AnalysisWB.FullName, ".csv", ".xlsx")
    
    AnalysisWB.Close True
    
    Application.DisplayAlerts = True

End Sub

Can someone see the issue and steer me in the right direction?

CodePudding user response：

Save Open Workbooks in Another Format

Option Explicit

Sub SaveAnalysis()

    ' Define constants.
    Const dNameLeft As String = "Early Arrears Analysis"
    Const dNameMiddle As String = "*."
    Const dNameRightOld As String = "csv"
    ' The following two lines have to be 'in sync' (compatible).
    Const dNameRightNew As String = "xlsx"
    Const dFileFormat As Long = xlOpenXMLWorkbook
    
    ' Build and store the name pattern ('dNamePatternLCase').
    Dim dNamePatternLCase As String
    dNamePatternLCase = LCase(dNameLeft & dNameMiddle & dNameRightOld)
    
    ' Store the length ('LenOld') of the (old, initial) file extension.
    Dim LenOld As Long: LenOld = Len(dNameRightOld)
    
    ' Reference the source workbook ('swb').
    Dim swb As Workbook: Set swb = ThisWorkbook ' workbook containing this code
    
    Application.ScreenUpdating = False
    
    Dim dwb As Workbook ' Destination Workbook
    Dim dPathOld As String
    Dim dPathNew As String
    Dim dCount As Long
    
    ' Loop through all open workbooks.
    For Each dwb In Workbooks
        ' Check if it's not the source workbook to not accidentally
        ' attempt to close it resulting in a run-time error.
        If Not dwb Is swb Then ' it's not the source workbook
            ' Using the Like operator, match the name of the current workbook
            ' to the pattern. Convert to lowercase,
            ' to enforce case-insensitivity ('A = a').
            If LCase(dwb.Name) Like dNamePatternLCase Then ' it's a match
                ' Store the current workbook's path ('dPathOld').
                dPathOld = dwb.FullName
                ' Build and store the new workbook path ('dPathNew').
                dPathNew = Left(dPathOld, Len(dPathOld) - LenOld) _
                    & dNameRightNew
                ' Save and close the workbook (the file).
                Application.DisplayAlerts = False ' overwrite (no confirmation)
                    dwb.SaveAs dPathNew, dFileFormat
                Application.DisplayAlerts = True
                dwb.Close SaveChanges:=False ' it has just been saved
                dCount = dCount   1
            'Else ' it's not a match; do nothing
            End If
        'Else ' it's the source workbook; do nothing
        End If
    Next dwb
    
    Application.ScreenUpdating = True
    
    ' Inform.
    Select Case dCount
    Case 0
        MsgBox "No " & dNameLeft & " workbooks saved.", vbExclamation
    Case 1
        MsgBox "1 " & dNameLeft & " workbook saved.", vbInformation
    Case Else
        MsgBox dCount & " " & dNameLeft & " workbooks saved.", vbInformation
    End Select

End Sub

CodePudding user response：

use the following, you have to set the file format to xlCSV

.SaveAs Filename:= "yourfile.csv" FileFormat :=xlCSV, CreateBackup:=False