2913 Access & Excel - Deleting Excel Rows Using Access via Button (Code)

[stevied56]

I have an Access db that imports several Excel.xlsx files, create tables, etc. I also created an Excel file template, created in a separate folder. from the Access folder The template has a range (A1:AG35000) established and named qry009_Export2Excel. I am able to export Access qry into the template and retain the formatting and even run another script from Access to make the Excel cells auto fit the data. Where I am having a problem is deleting the rows in the Excel Template worksheet before exporting the Access qry back into it. I keep getting an Range 1004 error. Any suggestions for making this work? Just want to remove the data in Rows 1:35000.

 

[stumac]

If you are already manipulating the spreadsheet from access VBA it should be as easy as .Rows("1:35000").EntireRow.Delete - you would need to fully qualify the workbook and sheet name... this works for me:

Sub DeleteXLLines()
Dim Myexcel As Object
Dim Myworkbook As Object
Dim Mysheet As Object
Dim MyFile As String
MyFile = "[COLOR=#FF0000]C:\MyFielname.xlsx[/COLOR]"
Set Myexcel = CreateObject("Excel.Application")
Set Myworkbook = Myexcel.Workbooks.Open(MyFile)
Set Mysheet = Myworkbook.sheets("[COLOR=#FF0000]MySheet[/COLOR]")
Mysheet.Rows("1:35000").EntireRow.Delete
Myworkbook.Close True
Set Mysheet = Nothing
Set Myworkbook = Nothing
Set Myexcel = Nothing
    
End Sub

 

[stevied56]

If you are already manipulating the spreadsheet from access VBA it should be as easy as .Rows("1:35000").EntireRow.Delete - you would need to fully qualify the workbook and sheet name... this works for me:

Sub DeleteXLLines()
Dim Myexcel As Object
Dim Myworkbook As Object
Dim Mysheet As Object
Dim MyFile As String
MyFile = "[COLOR=#FF0000]C:\MyFielname.xlsx[/COLOR]"
Set Myexcel = CreateObject("Excel.Application")
Set Myworkbook = Myexcel.Workbooks.Open(MyFile)
Set Mysheet = Myworkbook.sheets("[COLOR=#FF0000]MySheet[/COLOR]")
Mysheet.Rows("1:35000").EntireRow.Delete
Myworkbook.Close True
Set Mysheet = Nothing
Set Myworkbook = Nothing
Set Myexcel = Nothing
    
End Sub

Thank you and I will save this for future use. After posting my original post I realized I had made a naming convention problem which corrected it.