Originally Posted by
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
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
Code:
Set xlx = New Excel.Application
This is the code I have used:
Code:
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.