Offset and ActiveCell

[Pendragon]

Office03

Hey gang,

Have tried to get some assistance in the Access group with limited success
for formatting, but I need some VBA help in referencing Excel from Access.

All code is in Access. I am looping through a recordset and writing data to
an Excel template. The data is being written down-then-across from column to
column. "A2" is always the starting point and I am using an offset to move to
each column and then move downward to write data.

I am having problems setting the reference to the new cell (activating the
cell) in using Offset and then being able to write data to that cell and
format it appropriately.

Any help, clean-up, suggestions are appreciated!

I've eliminated working code to shorten the post but have kept (and
double-checked) all paired IF, WITH, etc., statements

....Working code before this, dim statements, etc.

Set objWkbk = objExcel.workbooks.Open(stTemplate)
x = 0
y = 1

....working code...

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

With objWkbk
.sheets("Sheet1").Select
.sheets("Sheet1").Activate
.sheets("Sheet1").Name = stSheetName
irow = 2
icolumn = 1
Do While Not rs.EOF
icolumn = icolumn + x
CName = rs("CourtName")
.....series of IF statements to set variables......

With .ActiveSheet

....ERROR on the following line. Msg: Object doesn't support this property or
method

.ActiveCell.Offset(0, icolumn).Activate

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

CID = rs("CourtID")
Do While CID = rs("CourtID")
TID = rs("MatchTimeID")
Do While TID = rs("MatchTimeID")
....write data here (once I get the above problem
resolved)....
Loop
TID = -1
Loop
End With
CID = -1
x = x + 1
Loop
End With

 

[Jim Thomlinson]

Try select instead of activate...

..ActiveCell.Offset(0, icolumn).Select

 

[Pendragon]

The error is Object Variable or With Block variable not set. I checked all
of my variables and they have been assigned values (and were defined), and
all of the Withs have End Withs. What am I missing?

 

[Rick Rothstein]

...ERROR on the following line. Msg: Object doesn't support this

property or method

.ActiveCell.Offset(0, icolumn).Activate

For the above error, just remove the dot in front of the ActiveCell
reference. Putting the dot in makes the ActiveCell try and reference the
ActiveSheet... the ActiveSheet does not have an ActiveCell property (hence
the error)... the ActiveCell is automatically the active cell on the active
sheet (the active cell cannot be located on a non-active sheet). So, make
the above line this...

ActiveCell.Offset(0, icolumn).Activate

 

[Pendragon]

Error was Object Variable or With Block Variable not set (see response to
Mr. Thomlinson).

Here is the entire code. Perhaps a different set of eyes will see something
I'm not.

Dim sSQL As String
Dim db As Database
Dim rs As Recordset
Dim objExcel As Object
Dim objWkbk As Object
Dim stPathName As String
Dim stFileName As String, stNewName As String
Dim stDocName As String, stSheetName As String
Dim stTemplate As String, stYear As String, stMonth As String, stDay As String
Dim MyDate As Date
Dim CID As Integer, irow As Integer, icolumn As Integer, TID As Integer
Dim stCell As String
Dim CName As String, R1R2 As String, SK1SK2 As String, LJ1LJ2 As String

stPathName = "c:\RefOnCourt\"
stTemplate = "c:\RefOnCourt\DailyGrid.xlt"
MyDate = Me.cboTournDay
stYear = Str(Year(MyDate))
If Month(MyDate) < 10 Then
stMonth = "0" & Trim(Str(Month(MyDate)))
Else
stMonth = Trim(Str(Month(MyDate)))
End If
If Day(MyDate) < 10 Then
stDay = "0" And Trim(Str(Day(MyDate)))
Else
stDay = Trim(Str(Day(MyDate)))
End If

stFileName = "DailyGrid" & Trim(stYear) & stMonth & stDay
stSheetName = "DailyGrid" & Trim(stYear) & stMonth & stDay

If fIsAppRunning("Excel") = -1 Then
Set objExcel = GetObject(, "Excel.Application")
objExcel.Visible = True
Else
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
End If

stFileName = stPathName & stFileName
If fIsFileDIR(stFileName) = -1 Then
retval = MsgBox("A file already exists for this date. Do you want to
delete it?", vbYesNo)
If retval = vbYes Then
Kill stFileName
Else
stNewName = InputBox("Please enter a new name for this file. It will
be saved in the same directory.")
End If
End If

Set objWkbk = objExcel.workbooks.Open(stTemplate)
CID = -1
x = 0
y = 1
TID = -1
NewCell = ""

Set db = CurrentDb
sSQL = "SELECT qryT_ViewGrid.CourtID, qryT_ViewGrid.CourtName,
adm_MatchTimes.MatchTimeID, qryT_ViewGrid.MatchTime, qryT_ViewGrid.R1R2,
qryT_ViewGrid.LJ1LJ2, qryT_ViewGrid.SK1SK2 " & _
"FROM qryT_ViewGrid INNER JOIN adm_MatchTimes ON
qryT_ViewGrid.MatchTimeID = adm_MatchTimes.MatchTimeID " & _
"ORDER BY qryT_ViewGrid.CourtID, adm_MatchTimes.MatchTimeID;"

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

With objExcel
.sheets("Sheet1").Select
.sheets("Sheet1").Activate
.sheets("Sheet1").Name = stSheetName
irow = 2
icolumn = 1
Do While Not rs.EOF
icolumn = icolumn + x
CName = rs("CourtName")

If IsNull(rs("R1R2")) Then
R1R2 = ""
Else
R1R2 = rs("R1R2")
End If

If IsNull(rs("SK1SK2")) Then
SK1SK2 = ""
Else
SK1SK2 = rs("SK1SK2")
End If

If IsNull(rs("LJ1LJ2")) Then
LJ1LJ2 = ""
Else
LJ1LJ2 = rs("LJ1LJ2")
End If

With .ActiveSheet
ActiveCell.Offset(0, icolumn).Activate

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

CID = rs("CourtID")
Do While CID = rs("CourtID")
TID = rs("MatchTimeID")
Do While TID = rs("MatchTimeID")
If R1R2 <> "" Then
ActiveCell.Offset(1, icolumn).Value = R1R2
ActiveCell.Offset(1, icolumn).Alignment = xlcenter
End If
If SK1SK2 <> "" Then
ActiveCell.Offset(2, icolumn).Value = SK1SK2
ActiveCell.Offset(2, icolumn).Alignment = xlcenter
End If
If LJ1LJ2 <> "" Then
ActiveCell.Offset(3, icolumn).Value = LJ1LJ2
ActiveCell.Offset(3, icolumn).Alignment = xlcenter
End If
rs.MoveNext
Loop
TID = -1
Loop
End With
CID = -1
x = x + 1
Loop
End With


Exit Sub

 

[Rick Rothstein]

You cannot always rely on the text of the error message coupled with the
breakpoint at which the code stops. Your full code is kind of lengthy and is
dependent on outside data, so we can't run it to see where it fails... can
you indicate the failure point within the code you posted for us?

 

[Pendragon]

The code fails on

ActiveCell.Offset(0, icolumn).Activate

 

[Rick Rothstein]

When the code stops there, execute this code in the Immediate window...

? ActiveCell.Offset(0, icolumn).Address

and tell us what it is printed out. If that errors out, then execute these
one at a time and tell us which one errors out.

? ActiveCell.Offset(0, icolumn).Row

? ActiveCell.Offset(0, icolumn).Column

 

[Rick Rothstein]

Oh, and what does this print out?

? icolumn

 

[Dave Peterson]

Without looking any testing at all, maybe you just have to qualify that
activecell.

objExcel.activecell......

 

[Pendragon]

icolumn = 1

all three other immediate window statements you asked for returned the same
Object/With Block variable not set.

Here's an interesting point, though - I happened to close Excel entirely
instead of closing the workbook. When Excel was reinitiated and the template
opened, the code passed successfully through what was the error point. I
then closed the workbook and left open Excel, and ran the code again. It
errored at the same point.

 

[Pendragon]

Thanks for your help. This line worked. Still haven't figured out why the
code bombs if Excel is open but the template is not, but I guess it doesn't
matter right now. If I remember to close Excel when using this application,
it works just fine.