Clear excel rows after importing to access

[jobbie]

I have a macro in access which imports some rows from an excel sheet. How do I make it clear those rows from excel once they have been imported. I know I can use "Sub Workbook_Open()" inside the excel sheet but the problem is whenever anyone opens the excel sheet directly he/she ends up deleting those rows. I am using office 2007.

[June7]

Think would have to open the Excel file as an object in VBA.

Dim xlx As Object, xlw As Object, xls As Object
Set xlx = GetObject(, "Excel.Application")
Set xlw = xlx.Workbooks.Open("C:\Filename.xls")
Set xls = xlw.Worksheets("WorksheetName")
xls.Range("B11:B15").Value = ""

Might find this site of interest http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

[jobbie]

Think would have to open the Excel file as an object in VBA.

Dim xlx As Object, xlw As Object, xls As Object
Set xlx = GetObject(, "Excel.Application")
Set xlw = xlx.Workbooks.Open("C:\Filename.xls")
Set xls = xlw.Worksheets("WorksheetName")
xls.Range("B11:B15").Value = ""

Might find this site of interest http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

Thanks for your help. I made one small change to your code as it was giving me a runtime error 429. I replaced line 2 of your code with this

Set xlx = New Excel.Application

This is the code I have used:

Sub TestSub()
    On Error GoTo closeit
    Dim xlx As Object, xlw As Object, xls As Object
    
    Set xlx = New Excel.Application
    Set xlw = xlx.Workbooks.Open("C:\Test.xlsm")
    Set xls = xlw.Worksheets("Sheet1")
    RowCount = xls.Cells(xls.Rows.Count, "A").End(xlUp).Row
    xls.Range(Cells(2, 1), Cells(RowCount + 1, 4)).ClearContents
    xlw.Close SaveChanges:=True
closeit:
    Set xlx = Nothing
End Sub

It seems the code runs perfectly but when I try to open the excel file directly it doesn't. I have to then go into task manager and kill the process which says EXCEL.EXE*32 and only then does the file open.

[ItsMe]

Perhaps you could try replacing:

    xlw.Close SaveChanges:=True
closeit:
    Set xlx = Nothing

with:

    xlw.Save
closeit:
    Set xls = Nothing
    xlw.Close
    Set xlw = Nothing
    Set xlx = Nothing

[jobbie]

Perhaps you could try replacing:

    xlw.Close SaveChanges:=True
closeit:
    Set xlx = Nothing

with:

    xlw.Save
closeit:
    Set xls = Nothing
    xlw.Close
    Set xlw = Nothing
    Set xlx = Nothing

It doesn't seem to work. I gives an error "462 The remote server machine doesn't exist or is unavailable". I modified the code as below to show me the error:

closeit:
    MsgBox Err.Number & vbCrLf & Err.Description
    Set xls = Nothing    
    xlw.Close
    Set xlw = Nothing
    xlx.Quit
    Set xlx = Nothing

[ItsMe]

It doesn't seem to work. I gives an error "462 The remote server machine doesn't exist or is unavailable". I modified the code as below to show me the error:

Perhaps the error is from code previous to those lines. I do not get any error with the following.

    Dim xlx As Object
    Dim xlw As Object
    Dim xls As Object
    
    Set xlx = New Excel.Application
    Set xlw = xlx.Workbooks.Open("C:\Test\ExcelFiles\ExcelFiles.xlsx")
    Set xls = xlw.Worksheets("Joe")
    
    xls.Name = "Bob"
    
    xlw.Save
closeit:
    Set xls = Nothing
    xlw.Close
    Set xlw = Nothing
    Set xlx = Nothing

If you make a reference to Microsoft Excel XX.X Object Library you can use Intelisense to help you with your range. Using the reference, you can declare your variables like this.

    Dim xlx As Excel.Application
    Dim xlw As Workbook
    Dim xls As Worksheet
    Dim RowCount As Range

[June7]

I have encountered the issue of persistent Excel process and sometimes cannot figure out why it won't close. Review http://forums.aspfree.com/microsoft-...el-413629.html

[jobbie]

I have encountered the issue of persistent Excel process and sometimes cannot figure out why it won't close. Review http://forums.aspfree.com/microsoft-...el-413629.html

well what's the solution? All I want is once the macro imports the data from excel it should then proceed to delete/clear all the rows except the headers in the excel sheet... would really appreciate your help

[June7]

This worked for me:

Dim xlx As Excel.Application
Dim xlw As Workbook
Dim xls As Worksheet
Set xlx = New Excel.Application
Set xlw = xlx.Workbooks.Open("C:\Temp\Book1.xlsx")
Set xls = xlw.Worksheets("Sheet1")
xls.Range("A2:B3").Value = ""
xlw.Close True
xlx.Quit

[ItsMe]

This worked for me:

Dim xlx As Excel.Application
Dim xlw As Workbook
Dim xls As Worksheet
Set xlx = New Excel.Application
Set xlw = xlx.Workbooks.Open("C:\Temp\Book1.xlsx")
Set xls = xlw.Worksheets("Sheet1")
xls.Range("A2:B3").Value = ""
xlw.Close True
xlx.Quit

Yah, I have had issues before too. As long as you close things up and destroy the objects, there should not be any memory leaks. The exception is when the application hangs because you pass it bad code. Sometimes Access won't even throw an error but Excel does not like the code you give it. I stopped having memory leaks after I started using early binding and declaring almost every Object prior to executing methods and setting properties.

[ssanfu]

I also have had problems with closing and quitting Excel from Access code. I think it is a timing issue - Excel used to hang all of the time;
now I add a "DoEvents" line and it seems to have taken care of the problem

Dim xlx As Excel.Application
    Dim xlw As Workbook
    Dim xls As Worksheet
    Set xlx = New Excel.Application
    Set xlw = xlx.Workbooks.Open("C:\Temp\Book1.xlsx")
    Set xls = xlw.Worksheets("Sheet1")
    xls.Range("A2:B3").Value = ""
    xlw.Close True
    DoEvents
    xlx.Quit

Apparently, if the Excel workbook takes a long tome to save before closing, the quit command has already been executed. Excel doesn't get the quit command from Access, thus having to into taskmanager to kill the process.

(your results may vary....)

[ItsMe]

That makes perfect sense, Steve.

[jobbie]

This worked for me:

Dim xlx As Excel.Application
Dim xlw As Workbook
Dim xls As Worksheet
Set xlx = New Excel.Application
Set xlw = xlx.Workbooks.Open("C:\Temp\Book1.xlsx")
Set xls = xlw.Worksheets("Sheet1")
xls.Range("A2:B3").Value = ""
xlw.Close True
xlx.Quit

Thanks this worked for me too.