Automation: Access to Excel : Change Font On fly (1 Viewer)

[wlgzhang]

Hi all,
I have Export data from access to Excel worksheet.
I am trying to change font weight form normal to Bold form each Schedule heading and Schedule table colmn headings on the fly.
I have tried by using:
with objWs.Row(PreColheading, PreColHeading+3)
.Font.Bold = True
.Cell(..)
end with
However, I got error msg:"Application or Object Not defined"
the following is my function witch produces and schedule heading :
Function FillScheduleHeading(objWS As Excel.Worksheet, preHeading As Integer, ExamPlace As String, Session As String) _
As Integer

Dim HeadingData As Recordset
Dim HeadingSql As String
Dim NextIRow As Integer

HeadingSql = "SELECT * FROM ExamLocation " _
& "WHERE ((ExamLocation.Location) Like '*" & ExamPlace & "*')"

Set HeadingData = CurrentDb.OpenRecordset(HeadingSql)
' MsgBox "debug" & CStr(HeadingData!Address)
With objWS
'MsgBox "Debug Font"
' With Worksheets(objWS).Rows(preHeading, preHeaging + 3)
'.Font.Bold = True
'.FontSize = 12
'Set up Exam Schedule/subSchedule heading
.Cells(preHeading, 1).Value = "SessionYear"
'.Cells(preHeading, 1).Font.Bold
.Cells(preHeading, 2).Value = Session
' .Cells(preHeading, 2).Font.Bold
.Cells(preHeading + 1, 1).Value = "Location"
'.Cells(preHeading + 1, 1).Font.Bold
.Cells(preHeading + 1, 2).Value = ExamPlace
'.Cells(preHeading + 1, 2).Font.Bold
.Cells(preHeading + 2, 1).Value = "Address"
' .Cells(preHeading + 2, 1).Font.Bold
.Cells(preHeading + 2, 2).Value = CStr(HeadingData!Address) & " " & CStr(HeadingData!Region) _
& " " & CStr(HeadingData!Country)
' .Cells(preHeading + 2, 2).Font.Bold
.Cells(preHeading + 3, 1).Value = "Contact Officer"
'.Cells(preHeading + 3, 1).Font.Bold
.Cells(preHeading + 3, 2).Value = CStr(HeadingData!ContactOffice)
'.Cells(preHeading + 3, 2).Font.Bold
'End With
End With
NextIRow = 4
FillScheduleHeading = NextIRow
HeadingData.close
Set HeadingData = Nothing
End Function
I have post same question on Automation: Access to Excel for several days.
Could anybody help me?

Any help is appreciated

Wei Zhang

 

[namliam]

The proper syntax for rows is not:
objWs.Row(PreColheading, PreColHeading+3)

It is:
objWs.Row(PreColheading & ":" & PreColHeading+3)


Greetz

 

[wlgzhang]

Automation: Access to Excel: Change Font on fly

Hi Greetz,
Thank you for your help.
I have tried your code, it now works.
However, there is trick, and I am worry about it:
When I change the code like what you told me exactly as the following:

Function FillScheduleHeading(objWS As Excel.Worksheet, preHeading As Integer, ExamPlace As String, Session As String) _
As Integer

Dim HeadingData As Recordset
Dim HeadingSql As String
Dim NextIRow As Integer

HeadingSql = "SELECT * FROM ExamLocation " _
& "WHERE ((ExamLocation.Location) Like '*" & ExamPlace & "*')"

Set HeadingData = CurrentDb.OpenRecordset(HeadingSql)
' MsgBox "debug" & CStr(HeadingData!Address)
With objWS
'MsgBox "Debug Font"
With objWS.Rows(preHeading & ":" & preHeading + 3)
MsgBox " debug 1 rows " & CStr(preHeading)
.Font.Bold = True

'Set up Exam Schedule/subSchedule heading
.Cells(preHeading, 1) = "SessionYear"
.Cells(preHeading, 2) = Session
MsgBox "debug 2 rows " & CStr(preHeading) & Session
.Cells(preHeading + 1, 1) = "Location"
.Cells(preHeading + 1, 2) = ExamPlace

.Cells(preHeading + 2, 1).Value = "Address"
.Cells(preHeading + 2, 2).Value = CStr(HeadingData!Address) & " " & CStr(HeadingData!Region) _
& " " & CStr(HeadingData!Country)

.Cells(preHeading + 3, 1).Value = "Contact Officer"
.Cells(preHeading + 3, 2).Value = CStr(HeadingData!ContactOffice)
MsgBox "rows " & " " & CStr(preHeading) & " " & CStr(preHeading + 3)
End With
End With
NextIRow = 4
FillScheduleHeading = NextIRow
HeadingData.close
Set HeadingData = Nothing
End Function

When I run the function, it change the font to bold, but it was messy up all my out put: all the schedule heading were disappeared except the first one.
After I put my debug message, I found out the start rows number floated away by double the preheading - 1 after the first iteration. For Example, in the second iterations the value of the preHeading is 30 to 34, but all the output for the second schedule heading is print at row 59 to 62. Therefore, I fixed code to make it work. The code is shown at the following that will help you to understand what I am talk about.

Function FillScheduleHeading(objWS As Excel.Worksheet, preHeading As Integer, ExamPlace As String, Session As String) _
As Integer

Dim HeadingData As Recordset
Dim HeadingSql As String
Dim NextIRow As Integer

HeadingSql = "SELECT * FROM ExamLocation " _
& "WHERE ((ExamLocation.Location) Like '*" & ExamPlace & "*')"

Set HeadingData = CurrentDb.OpenRecordset(HeadingSql)
' MsgBox "debug" & CStr(HeadingData!Address)
With objWS
'MsgBox "Debug Font"
With objWS.Rows(preHeading & ":" & preHeading + 3)
MsgBox " debug 1 rows " & CStr(preHeading)
.Font.Bold = True

'Set up Exam Schedule/subSchedule heading
.Cells(preHeading - preHeading + 1, 1) = "SessionYear"
.Cells(preHeading - preHeading + 1, 2) = Session
MsgBox "debug 2 rows " & CStr(preHeading) & Session
.Cells(preHeading - preHeading + 1 + 1, 1) = "Location"
.Cells(preHeading - preHeading + 1 + 1, 2) = ExamPlace

.Cells(preHeading - preHeading + 1 + 2, 1).Value = "Address"
.Cells(preHeading - preHeading + 1 + 2, 2).Value = CStr(HeadingData!Address) & " " & CStr(HeadingData!Region) _
& " " & CStr(HeadingData!Country)

.Cells(preHeading - preHeading + 1 + 3, 1).Value = "Contact Officer"
.Cells(preHeading - preHeading + 1 + 3, 2).Value = CStr(HeadingData!ContactOffice)
MsgBox "rows " & " " & CStr(preHeading) & " " & CStr(preHeading + 3)
End With
End With
NextIRow = 4
FillScheduleHeading = NextIRow
HeadingData.close
Set HeadingData = Nothing
End Function

I am wandering that is there a bug in excel or in Access or just in my PC? Because of this DB application is developed from scratch by me along, I am concerning what would happen after I delivery the application to my users. Do you why? Or anybody knows why?
By the way do you know or anybody knows the syntax for making boundary line for table and lines between columns (Automation from access to excel)? I like my out put looks nice.

Thanks again for your help.

Wei

 

[Bat17]

I am not quite sure what this is meant to do
.Cells(preHeading - preHeading + 1 + 1, 1) = "Location"
as
preHeading - preHeading must = 0
so
.Cells(2, 1) = "Location"

Peter

 

[wlgzhang]

Automation: Access to Excell: Change Font on fly

Hi Greetz and all,

Sorry about the stupid question that I posted this morning. I think now I know the answer:
Because of the statement of:
with objWS.Rows([defualt row],[Column])

end with

the default row is the start row, and the rows inside with and end with statement will add up from the start row. Thus, I must do:

with objWS.Rows(preHeading & ":" & preHeading + 3)
.Font.Bold = True
.Cells(1, 1) = "....."
.cells(1, 2) = "......"
.........
end with.

And I am not worry any more.

Thanks You all.

Wei