Access VBA to format Excel

[Pendragon]

This was working and now is not. Going to put in the Excel group as well.
Any ideas are appreciated. The error is 91 Object variable or With variable
not set.

Set rs = db.OpenRecordset(sSQL)
stCell = "B2"
rs.MoveFirst

With objWkbk
.Sheets("Sheet1").Select
.Sheets("Sheet1").Activate
.Sheets("Sheet1").Name = stSheetName
icolumn = 0

.ActiveSheet.Range("A1").Activate
.ActiveSheet.Range("A1").Value = "Officials Assignments - " &
Format(MyDate, "dddd, mmmm dd, yyyy")
.ActiveSheet.Range("A1").Font.Bold = True

Do While Not rs.EOF
icolumn = icolumn + 1
CName = rs("CourtName")

With .ActiveSheet
.Range(stCell).Select
.Range(stCell).Activate
End With

With .ActiveSheet
ActiveCell.Offset(0, icolumn).Select ***FAILS HERE***
ActiveCell.Offset(0, icolumn).Activate

With ActiveCell
.Value = CName
.HorizontalAlignment = xlcenter
.Font.Bold = True
End With
......

I've matched up all the WITH - END WITH pairs and double-checked that
everything is defined. I put in a MsgBox to test that the worksheet was
activated and named properly - this was successful. I tried to do the same
thing for the active cell (Activecell.Name) but this generated the same error.

Thanks for any help.

 

[Pendragon]

Thanks Doug. Unfortunately that produces the Object or Method property not
supported message. I had to do quite a bit of research on the ActiveCell and
Offset business to learn how it worked and I was very happy when it did. But
now it isn't and I haven't made any changes to the code.

Beginning to think some MS updates are the culprit.......

 

[Ken Snell [MVP]]

Try replacing .ActiveSheet with .Sheets("Sheet1") and replacing .ActiveCell
with .Sheets("Sheet1").Range(stCell).

 

[Pendragon]

Well, I thought maybe MS Updates were a possible solution. They weren't, but
in having to reboot, I did discover my original code worked.

Here's what I found:

I'm using the fIsAppRunning function to either Create Excel object or Get
Excel object. When the process runs and I finish up and close/exit Excel,
there is still an Excel process running (as I see in Task Manager). After
making some programming adjustments, the code eventually bombed out because
either a) I needed to compact & repair (which I usually do anyway so Access
doesn't crash as it likes to), or b) close the Access application altogether.
When I didn't close Access and continued to run test after test, I started
getting a "remote machine could not be reached" error.

If I compact frequently and every now and then close and restart my Access,
I am not having any problems.

btw Ken, making your changes did allow the code to run, but the offsets did
not return the results I needed and that would have been a major overhaul to
recalculate. So I'm glad I figured this out.

Still don't know why Excel is still running even though I've done a FILE
CLOSE in Excel, but my code works, so I guess I don't care for now.....

Thanks gentlemen!

 

[Ken Snell [MVP]]

EXCEL continues to run because you're using ActiveSheet and ActiveCell
references in your code (and perhaps there are other uses of unqualified
references). Those references cause ACCESS to instantiate additional
instances of EXCEL because they are not fully qualified through object
references that you created in your code.

See these Microsoft Knowledge Base articles for more information about this
"phenomenon":

INFO: Error or Unexpected Behavior with Office Automation When You Use Early
Binding in Visual Basic (see the topics "The Problems in Using Unqualified
Code with Office" and "Qualifying the Code to Avoid Errors"):
http://support.microsoft.com/kb/319832/

http://support.microsoft.com/default.aspx?scid=kb;en-us;178510

http://support.microsoft.com/kb/199219/en-us

 

[Pendragon]

This is great! Thanks very much for the articles - they will be quite useful
in resolving other application issues I was having for other clients.