Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset and ActiveCell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset and ActiveCell
Try select instead of activate...
..ActiveCell.Offset(0, icolumn).Select -- HTH... Jim Thomlinson "Pendragon" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset and ActiveCell
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? "Jim Thomlinson" wrote: Try select instead of activate... .ActiveCell.Offset(0, icolumn).Select -- HTH... Jim Thomlinson "Pendragon" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset and ActiveCell
...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 -- Rick (MVP - Excel) "Pendragon" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset and ActiveCell
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" wrote: ...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 -- Rick (MVP - Excel) "Pendragon" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset and ActiveCell
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? -- Rick (MVP - Excel) "Pendragon" wrote in message ... 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" wrote: ...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 -- Rick (MVP - Excel) "Pendragon" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset and ActiveCell
The code fails on
ActiveCell.Offset(0, icolumn).Activate "Rick Rothstein" wrote: 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? -- Rick (MVP - Excel) "Pendragon" wrote in message ... 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" wrote: ...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 -- Rick (MVP - Excel) "Pendragon" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset and ActiveCell
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 (MVP - Excel) "Pendragon" wrote in message ... The code fails on ActiveCell.Offset(0, icolumn).Activate "Rick Rothstein" wrote: 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? -- Rick (MVP - Excel) "Pendragon" wrote in message ... 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" wrote: ...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 -- Rick (MVP - Excel) "Pendragon" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset and ActiveCell
Oh, and what does this print out?
? icolumn -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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 (MVP - Excel) "Pendragon" wrote in message ... The code fails on ActiveCell.Offset(0, icolumn).Activate "Rick Rothstein" wrote: 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? -- Rick (MVP - Excel) "Pendragon" wrote in message ... 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" wrote: ...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 -- Rick (MVP - Excel) "Pendragon" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset and ActiveCell
Without looking any testing at all, maybe you just have to qualify that
activecell. objExcel.activecell...... Pendragon wrote: 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" wrote: ...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 -- Rick (MVP - Excel) "Pendragon" wrote in message ... 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 -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset and ActiveCell
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. "Rick Rothstein" wrote: Oh, and what does this print out? ? icolumn -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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 (MVP - Excel) "Pendragon" wrote in message ... The code fails on ActiveCell.Offset(0, icolumn).Activate "Rick Rothstein" wrote: 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? -- Rick (MVP - Excel) "Pendragon" wrote in message ... 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" wrote: ...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 -- Rick (MVP - Excel) "Pendragon" wrote in message ... 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset and ActiveCell
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. "Rick Rothstein" wrote: ...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 -- Rick (MVP - Excel) "Pendragon" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If activecell.column = variable then activecell,offset (0,1) | Excel Discussion (Misc queries) | |||
ActiveCell Offset | Excel Programming | |||
Activecell Offset | Excel Programming | |||
ActiveCell.Offset w/ VBA | Excel Programming | |||
activecell offset | Excel Programming |