Passing excel objects to subroutines as parameters

[henryonyeagbako]

I have the following procedure MoveGrandTotalLabel code below which
finds a specified cell value on an excel spreadsheet and then formats
the found cell and inserts text next to the found cell. The sub is
passed excel objects from have that have been intiated in the main
subroutine as shown below. The problem is that when i step through the
code as soon i get to the CellAddress = Cells.Find statement the code
simply exits the sub routine and proceeds with the next statement in
the main sub. When i copy and paste this exact same code into the main
sub it works with no problems at all. The cell value to be searched is
clearly on the excel spreadsheet. The procedure MoveGrandTotalLabel is
in a different module to the PublishDataInExcel procedure. Any
suggestions on why this is happening and possible solutions welcome

Public Sub PublishDataInExcel(dbrs As DAO.Recordset, dbfld As
DAO.Field, SheetName As String, firstdate As Date, lastdate As Date)
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWs As Excel.Worksheet
Dim xlrng As Excel.Range


MoveGrandTotalLabel xlApp, xlWb, xlWs, xlrng, firstdate, lastdate



Sub MoveGrandTotalLabel(App As Excel.Application, Wb As
Excel.Workbook, Ws As Excel.Worksheet, rng As Excel.Range, firstdate
As Date, lastdate As Date)
Dim CellAddress As String
Dim Findstring As String
Findstring = "Grand Total"
rng.Value = Ws.name
Set Ws = Wb.ActiveSheet
rng.Value = Wb.name
CellAddress = Cells.Find(What:="Grand Total",
After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Set rng = Ws.Range(CellAddress) '.Address
If Not rng Is Nothing Then
FirstAddress = rng ' .Address 'ActiveCell.Address
Do
rng.Cut
rng.End(xlToLeft).Select
Ws.Paste
Set rng = Ws.Columns("A:A")
rng.EntireColumn.AutoFit
Set rng = ActiveCell.FindNext(rng)
Loop While Not rng.Value Is Nothing And ActiveCell.Address <>
FirstAddress
End If
Set rng = rng.Offset(0, 1).Range("A1").Select
rng.Value = dbrs.RecordCount & " assets Aquired in the selected
period " & firstdate & " - " & lastdate
End Sub

 

[RoyVidar]

I have the following procedure MoveGrandTotalLabel code below which
finds a specified cell value on an excel spreadsheet and then formats
the found cell and inserts text next to the found cell. The sub is
passed excel objects from have that have been intiated in the main
subroutine as shown below. The problem is that when i step through
the code as soon i get to the CellAddress = Cells.Find statement the
code simply exits the sub routine and proceeds with the next
statement in the main sub. When i copy and paste this exact same code
into the main sub it works with no problems at all. The cell value to
be searched is clearly on the excel spreadsheet. The procedure
MoveGrandTotalLabel is in a different module to the
PublishDataInExcel procedure. Any suggestions on why this is
happening and possible solutions welcome

Public Sub PublishDataInExcel(dbrs As DAO.Recordset, dbfld As
DAO.Field, SheetName As String, firstdate As Date, lastdate As Date)
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWs As Excel.Worksheet
Dim xlrng As Excel.Range


MoveGrandTotalLabel xlApp, xlWb, xlWs, xlrng, firstdate, lastdate



Sub MoveGrandTotalLabel(App As Excel.Application, Wb As
Excel.Workbook, Ws As Excel.Worksheet, rng As Excel.Range, firstdate
As Date, lastdate As Date)
Dim CellAddress As String
Dim Findstring As String
Findstring = "Grand Total"
rng.Value = Ws.name
Set Ws = Wb.ActiveSheet
rng.Value = Wb.name
CellAddress = Cells.Find(What:="Grand Total",
After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Set rng = Ws.Range(CellAddress) '.Address
If Not rng Is Nothing Then
FirstAddress = rng ' .Address 'ActiveCell.Address
Do
rng.Cut
rng.End(xlToLeft).Select
Ws.Paste
Set rng = Ws.Columns("A:A")
rng.EntireColumn.AutoFit
Set rng = ActiveCell.FindNext(rng)
Loop While Not rng.Value Is Nothing And ActiveCell.Address <>
FirstAddress
End If
Set rng = rng.Offset(0, 1).Range("A1").Select
rng.Value = dbrs.RecordCount & " assets Aquired in the selected
period " & firstdate & " - " & lastdate
End Sub

The problem, is that Cells (and also ActiveCell), are Excel objects
that need to be "anchored" in the relevant Excel objects, else they
will create sideffects like only working the first time, RT 1004
_method blah blah failed, keeping an instance of Excel left in memory
etc...

Without any testing, I would guess you could prefix Cells with either
your sheet or application object, and activecell with your application
object.

CellAddress = xlApp.Cells.Find(What:="Grand Total", _
After:=xlApp.ActiveCell, LookIn:=xlValues, _
....

for more info, check out for instance this
http://support.microsoft.com/default.aspx?kbid=178510

 

[henryonyeagbako]

message <[email protected]>:









The problem, is that Cells (and also ActiveCell), are Excel objects
that need to be "anchored" in the relevant Excel objects, else they
will create sideffects like only working the first time, RT 1004
_method blah blah failed, keeping an instance of Excel left in memory
etc...

Without any testing, I would guess you could prefix Cells with either
your sheet or application object, and activecell with your application
object.

CellAddress = xlApp.Cells.Find(What:="Grand Total", _
After:=xlApp.ActiveCell, LookIn:=xlValues, _
...

for more info, check out for instance thishttp://support.microsoft.com/default.aspx?kbid=178510

Thank you very much for this tip it helped me rectify problem coding.
My problem now is when I step through the code and get to the
following line

Set rng =App.ActiveCell.FindNext(rng)

There is only one occurence of "grand total" in the spreadsheet so the
code should see this and follow the next line to bring the loop to a
stop and then proceed to the next lines which place text next to the
findstring. However this is not the case as the code immediately exits
the procedure before executing the remaining lines. How can i get the
code to check that there are no more occurences of the findstring
"grand total" and cleanly exit the loop display the text and then exit
the procedure. Thanks for your assistance greatly appreciated.

 

[RoyVidar]

Thank you very much for this tip it helped me rectify problem coding.
My problem now is when I step through the code and get to the
following line

Set rng =App.ActiveCell.FindNext(rng)

There is only one occurence of "grand total" in the spreadsheet so
the code should see this and follow the next line to bring the loop
to a stop and then proceed to the next lines which place text next to
the findstring. However this is not the case as the code immediately
exits the procedure before executing the remaining lines. How can i
get the code to check that there are no more occurences of the
findstring "grand total" and cleanly exit the loop display the text
and then exit the procedure. Thanks for your assistance greatly
appreciated.

I'm sorry, I'm not very familiar with those objects. Unless someone
with experience with this particular challenge pops in, I can only
offer
som thougts.


What I go by when automating, I can play with the stuff in the
automated app, convert the code to automation code (prefix with
relevant object variables), then if it exhibits strange behaviour,
or the behaviour differs from when using it within the automated
application, then my first and main suspect, is what we've alredy
discussed.

Are there still any unqualified references to Excel objects, methods
and properties?

If I'm entirely sure I've found, and dealt with all of those, I'll
either try and see if using all inline code matters (in stead of
passing objects to routines/functions, do it all within one routine)
or start playing a bit more with the automated application. In this
process, I would probably also check NGs for the automated application
for similar errors.

 

[henryonyeagbako]

message <[email protected]>:









I'm sorry, I'm not very familiar with thoseobjects. Unless someone
with experience with this particular challenge pops in, I can only
offer
som thougts.

What I go by when automating, I can play with the stuff in the
automated app, convert the code to automation code (prefix with
relevant object variables), then if it exhibits strange behaviour,
or the behaviour differs from when using it within the automated
application, then my first and main suspect, is what we've alredy
discussed.

Are there still any unqualified references toExcelobjects, methods
and properties?

If I'm entirely sure I've found, and dealt with all of those, I'll
either try and see if using all inline code matters (in stead ofpassingobjectsto routines/functions, do it all within one routine)
or start playing a bit more with the automated application. In this
process, I would probably also check NGs for the automated application
for similar errors.

Thanks for your help i have managed to rework the procedure and now it
works like a charm
'Sub MoveGrandTotalLabel()
Sub MoveGrandTotalLabel(App As Excel.Application, Wb As
Excel.Workbook, Ws As Excel.Worksheet, rng As Excel.Range, first As
Date, last As Date, xlrs As DAO.Recordset)
'Dim rng As Range
Dim Findstring As String
Findstring = "Grand Total"
With Ws.Range("A:I")
'With ActiveSheet.Range("A:I")

Set rng = App.Cells.Find(What:=Findstring,
After:=App.ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
'Set rng = Cells.Find(What:=Findstring, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not rng Is Nothing Then
FirstAddress = rng.End(xlToLeft).Address ' .Address
'ActiveCell.Address
Do

rng.Cut
rng.End(xlToLeft).Select
Ws.Paste
'ActiveSheet.Paste
Set rng = .FindNext(rng)
Loop While Not rng Is Nothing And rng.Address <> FirstAddress
End If
Set rng = rng.Offset(0, 1) '.Range(rng.Address)
rng.Value = xlrs.RecordCount & " assets Aquired in the selected
period " & Format$(first, "short date") & " - " & Format$(last, "short
date")
rng.Font.Bold = True
'rng.Value = "40 assets Aquired in the selected period " &
firstdate & " - " & lastdate
Set rng = Ws.Columns("A:A")
'Set rng = ActiveSheet.Columns("A:A")
rng.EntireColumn.AutoFit
End With
End Sub

Tnak you very much for your assistance