From Access Delete all rows in excel worksheet but not cell formats

Access 2010
Excel 2010

I'm trying to get to an excel workbook(without actually opening it),
and Delete the rows below the column Headers(row 1)..but this is not working below

Screen.MousePointer = vbHourglass
          Dim strDame As String
          Dim DBs As DAO.Database
          Dim rst As DAO.Recordset
          Dim STRSQL As String
          Dim xlApp As Object
          Dim xlWb As Object
          Dim xlWs As excel.Worksheet
          Dim recArray As Variant
          Dim strDB As String
          Dim fldCount As Integer
          Dim recCount As Long
          Dim iCol As Integer
          Dim iRow As Integer
          Dim accExcel As excel.Application
  RecNo = 0

STRSQL = "SELECT * FROM Import_Data"
   
 
   Set DBs = CurrentDb
   Set rst = DBs.OpenRecordset(STRSQL)
   Set accExcel = CreateObject("excel.application.9") <---- error here 
   
   accExcel.Workbooks.Open "H:\Escalation_Import\Excel_Sku_Import.xlsx"
   accExcel.Visible = False
   accExcel.UserControl = False

 Set xlWs = accExcel.Worksheets("Import_Data")
 xlWs.rows("2:65536").ClearContents

 ' Close ADO objects
 rst.Close
 Set rst = Nothing
 ' Release Excel references
 Set xlWs = Nothing
 Set xlWb = Nothing
 Set xlApps = Nothing
 Screen.MousePointer = vbArrow

Open in new window




Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
First, add a reference to Excel (menu Tools, References, "Microsoft Excel 16.0 Object Library").

Then, open and close Excel correctly. This is the flow:

Public Sub ManipulateWorkSheet()

    Dim xls     As Excel.Application
    Dim wkb     As Excel.Workbook
    Dim wks     As Excel.Worksheet
    
    Set xls = New Excel.Application
    Set wkb = xls.Workbooks.Open("d:\folder\workbook1.xlsx")
    Set wks = wkb.Worksheets(1) ' or any other worksheet.
    
    ' Manipulate the wks worksheet as needed.

    wkb.Close True
    
    Set wks = Nothing
    Set wkb = Nothing
    
    xls.Quit
    
    Set xls = Nothing

End Sub

Open in new window

0
 
PatHartmanCommented:
You need to open the spreadsheet to use OLE automation.  I don't know how to do what you are asking.  When I need to automate Excel, I open a workbook, turn on the macro recorder and perform the action.  Then I take the generated code and run it from Access.  You may need to change references.
0
 
Ryan ChongConnect With a Mentor Commented:
what if replace:

Dim accExcel As excel.Application
Set accExcel = CreateObject("excel.application.9") <---- error here 

Open in new window


with:
Dim accExcel As Object
Set accExcel = CreateObject("excel.application") 

Open in new window


?
0
 
FordraidersAuthor Commented:
This is what worked:

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

Set wb = xlApp.Workbooks.Open("H:\Escalation_Import\Excel_Sku_Import.xlsx"", False, False)
'wb.Sheets(1).rows(2).Delete
wb.Sheets("Import_Data").rows("2:65536").ClearContents
wb.Save
'wb.Close
Set wb = Nothing
Set xlApp = Nothing
Screen.MousePointer = vbArrow
0
 
FordraidersAuthor Commented:
Thanks all
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.