1

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:

enter image description here

    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?

braX
  • 11,506
  • 5
  • 20
  • 33
Danny
  • 121
  • 8
  • A `csv` file is completely different than a `xlsx` one. You simple cannot do it only by changing the file extension. You should `Save` it `As xlsx`... But you way of setting `AnalysisWB` does not look very well, at least for me. You should also place "ArrearsAnalysis & "*.csv" instead of `ArrearsAnalysis & "*"`. If there will also be 'xlsx' workbooks with the same prefix, your code may confuse VBA... – FaneDuru Sep 16 '22 at 11:57
  • https://stackoverflow.com/a/37571268/3688861 https://learn.microsoft.com/en-us/office/vba/api/excel.xlfileformat – Tragamor Sep 16 '22 at 13:14
  • @FaneDuru I'm not sure what you're suggesting here. The `ArrearsAnalysis & *` has not caused any issues because I only keep one copy of the same file per folder. – Danny Sep 16 '22 at 13:48

2 Answers2

0

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
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • I just tried this but I'm seeing Case 0. Would it help that I'm trying to save a different workbook e.g. not ThisWorkbook? I tried changing `ThisWorkbook` to `ActiveWorkbook` but to no avail. – Danny Sep 16 '22 at 13:46
  • `ThisWorkbook` is `swb` and is also protected in the loop with `If Not dwb Is swb Then`. All workbooks are `dwb`. So you're matching the pattern to the names of all open workbooks, except `swb` (`Thisworkbook)`. Why don't you use something like `Debug.Print dwb.Name, dNamePatternLCase` first, right above `IF LCase...`, and later, below, then below the next line... and see what the results are to come to a conclusion. As you go lower, add more (relevant) variables to the `Debug.Print` line. Have you modified any of the constants? – VBasic2008 Sep 16 '22 at 14:40
0

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

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

Waly
  • 46
  • 5