CODEWITHSUNDEEP
excelvbacsvsave
Omar
Omar

Reputation: 53

Save CSV with same name as current open CSV

I have a macro that runs a separate loop macro from personal workbook on a CSV (without opening it) and should re-save the document with the same name.

I get this error when it tries to save.

Run-time error '1004':
You cannot save this workbook with the same name as another open workbook or add-in. Choose a different name or close the other workbook or add-in before saving.

I need this as there is a tool looking for this document name.

This is the macro that opens the csv and runs the loop macro (without opening Excel).

Sub ExcelMacroExample() 
    Dim xlApp 
    Dim xlBook 

    Set xlApp = CreateObject("Excel.Application") 
    Set xlBook = xlApp.Workbooks.Open("F:\Folder\specific name.csv", 0, True) 
    xlapp.workbooks.open("C:\Users\name\AppData\Roaming\Microsoft\Excel\XLSTART \Personal.xlsb")
    xlApp.Run "Personal.xlsb!LoopColumnC "
    xlApp.Quit 

    Set xlBook = Nothing 
    Set xlApp = Nothing 
End Sub

The macro for LoopColumnC is where it tries to save.

Sub LoopColumnC()
    '
    ' LoopColumnC Macro
    '

    '
    'This is to disregard any popups.
    Application.DisplayAlerts = False
    'This is the actual loop. Works just fine. 
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 3).End(xlUp).Row
    For i = 2 To lastRow
        If Cells(i, 3).Value = 1 Then Cells(i, 3).Value = Cells(i - 1, 3).Value
      Next i

    'Here is where it errors out because the file it is trying to save as is already opened in the background. 
    ThisWorkbook.SaveAs "F:\Folder\specific name.csv",

    Application.DisplayAlerts = True
    
End Sub

Upvotes: 0

Views: 385

Answers (1)

Tim Williams
Tim Williams

Reputation: 166550

You should modify your code to pass the worksheet object directly to the called macro - don't rely on Activesheet etc.

Sub ExcelMacroExample() 
    Dim xlApp 
    Dim xlBook

    Set xlApp = CreateObject("Excel.Application") 
    'if you want to save with the same name then you can't open as read-only...
    Set xlBook = xlApp.Workbooks.Open("F:\Folder\specific name.csv", 0, True) 
    xlapp.workbooks.open("C:\Users\name\AppData\Roaming\Microsoft\Excel\XLSTART \Personal.xlsb")
    xlApp.Run "Personal.xlsb!LoopColumnC", xlBook.Worksheets(1) '<< passes the sheet

    xlBook.Close '<<
    xlApp.Quit 

    Set xlBook = Nothing 
    Set xlApp = Nothing 
End Sub

Your processing macro receives the sheet object:

'gets a worksheet object from the caller
Sub LoopColumnC(ws As Worksheet)
  
    
    Dim lastRow As Long
    'operate on the provided worksheet
    lastRow = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row
    For i = 2 To lastRow
        If ws.Cells(i, 3).Value = 1 Then 
            ws.Cells(i, 3).Value = ws.Cells(i - 1, 3).Value
        End If
    Next i

    Application.DisplayAlerts = False
    ws.Parent.SaveAs "F:\Folder\specific name.csv" 'ws.Parent = workbook
    Application.DisplayAlerts = True
    
End Sub

Upvotes: 3

Related Questions