Insert a blank row in Excel from MS Access (1 Viewer)

[epiek]

Hi there

I insert data into Excel and create subtotals without a problem

I now want to search for the word 'Total' which I do successfully and it goes to that line item.

Now I need to insert a blank row.

How do you reference the activecell from Access and insert a blank row below the entry it found?

Help will be much appreciated!!

I have searched high and low on the internet to find a solution.

here is my bit of coding that I struggle with:

flag = False
While flag = False
On Error GoTo stoploop

objWkb.Sheets(1).Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

'coding works perfectly to here - now I need to insert a blank row!!


flag = False
Wend
stoploop:
objWkb.Sheets(1).Range("A6").Select

 

[Ranman256]

you can always use excel, record a macro,
insert row
stop macro
take the code from that macro, then insert it into your Access code, but remember to put the excel object in front:
i.e:
objwkb.rows.insert

 

[epiek]

I tried that.

In Access, the word selection must be replaced with the cell reference.

In Access, activecell does not work...

 

[epiek]

I managed to solve it, probably very long winded . The activecell works,but you have to reference the application!

flag = False
j = objWkb.Sheets(1).Range("A6").CurrentRegion.Rows.Count
i = 6
While flag = False And i < j
On Error GoTo stoploop

objWkb.Sheets(1).Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

i = objXL.Application.ActiveCell.Row
'Make total row bold
CurrentRowString = i & ":" & i
Rows(CurrentRowString).Select
Rows(CurrentRowString).Font.Bold = False
Rows(CurrentRowString).Font.Bold = True
'Now insert blank after the total row
i = i + 1
CurrentRowString = i & ":" & i
Rows(CurrentRowString).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
CurrentRowString = i + 1 & ":" & i + 1
Rows(CurrentRowString).Select
i = objXL.Application.ActiveCell.Row
CurrentRowString = "A" & i
Range(CurrentRowString).Select
k = objWkb.Sheets(1).Range(CurrentRowString).CurrentRegion.Rows.Count - 1 'for the grand total row
j = i + k
flag = False
Wend