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.

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.......

"Douglas J. Steele" wrote:

> Realistically, there's no reason for the
>
> End With
>
> With .ActiveSheet
>
> directly before that line, but I doubt that will solve the problem.
>
> I suspect you need .ActiveCell, not ActiveCell (i.e.: you need a period
> before ActiveCell) on that line, the next line, and the With ActiveCell
> line.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Pendragon" <(E-Mail Removed)> wrote in message
> news:2C744554-3523-40C5-9D40-(E-Mail Removed)...
> > 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.
>
>
>

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

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




"Pendragon" <(E-Mail Removed)> wrote in message
news:2C744554-3523-40C5-9D40-(E-Mail Removed)...
> 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.

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!

"Pendragon" wrote:

> 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.

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...b;en-us;178510

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

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



"Pendragon" <(E-Mail Removed)> wrote in message
news:C36038CB-BE86-4A2D-B834-(E-Mail Removed)...
> 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!

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.

"Ken Snell [MVP]" wrote:

> 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...b;en-us;178510
>
> http://support.microsoft.com/kb/199219/en-us
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
> http://www.accessmvp.com/KDSnell/
>
>
>
> "Pendragon" <(E-Mail Removed)> wrote in message
> news:C36038CB-BE86-4A2D-B834-(E-Mail Removed)...
> > 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!
>
>
>