Convert Excel macro to Access code

Question

I am exporting the data for this code from an Access 2003 database into excel so we can have others fill in a form.   This code works great in excel (it creates header rows inbetween sections of other data), but I am having a problem getting it to run when access is telling excel what to do.  I have tried many variations (putting objxl or objsheet in from of ranges and activecell lines), but I must be missing something...

My access to excel objects are as follows:

Dim objXL As Object

Dim objWB As Object

Dim objSheet As Object

These are then set to the following:

 Set objXL = CreateObject("Excel.Application")

 Set objWB = objXL.Workbooks.Add

Set objSheet = objWB.ActiveSheet

Excel code being used, needing to be converted using the objects:

Range("A2").Select

    ActiveCell.Rows("1:1").EntireRow.Insert shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    ActiveCell.Offset(1, 0).Copy Destination:=ActiveCell

    iPrevious = ActiveCell.Value

    For Each c In Range(ActiveCell.Offset(1, 0).Address, Chr(64 + ActiveCell.Column) & ActiveCell.SpecialCells(xlLastCell).Row)

        If c.Value <> "" Then

            If c.Value = iPrevious Then

                c.Value = ""

            Else

                c.Rows("1:1").EntireRow.Insert shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

                c.Offset(-1, 0).Value = c.Value

                iPrevious = c.Value

            End If

        End If

    Next c

Any ideas would be great!

Answer 1

Okay, you should read this

http://www.btabdevelopment.com/ts/excelinstance

as it will help you but I converted this over (including creating the constants since we are using late binding (which is a good idea).

Dim objXL As Object

Dim objWB As Object

Dim objSheet As Object

Dim c As ObjectConst xlDown As Integer = -4121Const xlFormatFromLeftOrAbove As Integer = 0Const xlLastCell As Integer = 11

Set objXL = CreateObject("Excel.Application")

Set objWB = objXL.Workbooks.Add

Set objSheet = objWB.ActiveSheet

With objXLobjSheet.Range("A2").Select

    **.**ActiveCell.Rows("1:1").EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    **.ActiveCell.Offset(1, 0).Copy Destination:=.**ActiveCell

    iPrevious = **.**ActiveCell.Value

    For Each c In **objSheet.Range(.**ActiveCell.Offset(1, 0).Address, Chr(64 + **.**ActiveCell.Column) & **.**ActiveCell.SpecialCells(xlLastCell).Row)

        If c.Value <> "" Then

            If c.Value = iPrevious Then

                c.Value = ""

            Else

                c.Rows("1:1").EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

                c.Offset(-1, 0).Value = c.Value

                iPrevious = c.Value

            End If

        End If

    Next c

End With

Answer 2

THANK YOU so much!  :)

I was putting way too many objsheet references.  Thought I had to do so before each .activecell, etc.  Very new to this, but learning.  I will definitely read the website you referred to.  Just very happy to end the work week with a definite product, especially since it was needed by Monday.  Thanks again!