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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Gustav BrockCIOCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.