Access VBA delete entire column or columns and shift lef in Excel

[jydbman]

Come to think about it, I think the code you where given will save by default, The reason of the error is that you probably have some rouge excel instances in memory. clear them out and you'll be good to go.

JR

Thank you, JR!

How to clear the rouge excel instances in memory? I did kill all the Excel processes from windows task manager, but the pop up dialog box still shows: A file name 'RESUME.XLW' already exists in this location. Do you want to replace it?

 

[JANR]

i dont' know, could be a Windows or Excel intefering, I don't get any messages when I run the code. BTW here is my test code:

Public Sub DeleteColumn( _
    ByVal bookname As String, _
    ByVal sheetname As String, _
    ByVal cellcolumn As String)
    Const xlShiftToLeft = -4159
    
    Dim oXL As Object
    Dim oBook As Object
    Dim oSheet As Object
    Set oXL = CreateObject("Excel.Application")
    Set oBook = oXL.Workbooks.Open(bookname)
    Set oSheet = oBook.Worksheets(sheetname)
    
    oSheet.Range(cellcolumn).EntireColumn.Delete xlShiftToLeft
    
    Set oSheet = Nothing
    oBook.Close True
    Set oBook = Nothing
    oXL.Quit
    Set oXL = Nothing
End Sub

Function testXL()
DeleteColumn "d:\file1.xls", "file1", "H:H"
End Function

and it just deletes the coulmn H everytime I run function testXL.

Perhaps others might step in with more experience on excelautomation.

JR

 

[jydbman]

i dont' know, could be a Windows or Excel intefering, I don't get any messages when I run the code. BTW here is my test code:

Public Sub DeleteColumn( _
    ByVal bookname As String, _
    ByVal sheetname As String, _
    ByVal cellcolumn As String)
    Const xlShiftToLeft = -4159
    
    Dim oXL As Object
    Dim oBook As Object
    Dim oSheet As Object
    Set oXL = CreateObject("Excel.Application")
    Set oBook = oXL.Workbooks.Open(bookname)
    Set oSheet = oBook.Worksheets(sheetname)
    
    oSheet.Range(cellcolumn).EntireColumn.Delete xlShiftToLeft
    
    Set oSheet = Nothing
    oBook.Close True
    Set oBook = Nothing
    oXL.Quit
    Set oXL = Nothing
End Sub

Function testXL()
DeleteColumn "d:\file1.xls", "file1", "H:H"
End Function

and it just deletes the coulmn H everytime I run function testXL.

Perhaps others might step in with more experience on excelautomation.

JR

Thank you, JR.

I think some how the access vba open it behind the scene without releasing it and cause the file to be read only. So it always ask to save as and to replace the existing file.

Any suggestion?

Thanks!

 

[JANR]

well you could try my previous suggestion and explicitly issue a save in the code before you release the object:

oXL.Save

As for checking for rouge excel, Open the Windows Task manager and under Prossesses, check for Excel. If it's there mark it and end prosess.

JR

 

[jydbman]

well you could try my previous suggestion and explicitly issue a save in the code before you release the object:

oXL.Save

As for checking for rouge excel, Open the Windows Task manager and under Prossesses, check for Excel. If it's there mark it and end prosess.

JR

Thank you, JR.

Without oXL.Save, I got save as dialog box. With it, I got "A file name 'RESUME.XLW' already exists in this location. Do you want to replace it?"

I googled it and I changed oXL.Save to oXL.Activeworkbook.save and it works fine now. However, I used the same method in another function to save Excel file after using VBA to change the excel, it always pop up the Save as dialog box. It seems to me that the function open up the excel to make change and keep it in read only mode so when the VBA want to change it, it asks for Save as and replace the existing.

Any idea why the method works for one but not the other?
Thank you very much!