Applying a format to a dynamic field

[coachdan]

I am trying to find a way to perform a simple format to a dynamic field and cannot get the code in the right place. All I want to do is Bold the column header for the dynamic field "Homesite", but everything I've tried causes other rows (other than A) to bold when it advances to the next Homesite. Here's the section of code - I will add a comment to the area that I tried:



Private Sub cmdDeliverRep_Click()

Dim DBase As Database
Dim ExApp As Object
Dim ExBook As Object
Dim ExSheet As Object
Dim RecRaw As Recordset
Dim Qdata As Recordset
Dim Row As Integer
Dim Col As Integer
Dim Center As String
Dim I1 As Integer
Dim I2 As Integer
Dim I3 As Integer
Dim I4 As Integer
Dim Q As Integer
Dim x As Integer
Dim QDef As QueryDef
Dim Ranking As Long
Dim Alpha
Dim TaskCount

'Set the database used to the current db
Set DBase = CurrentDb()


'Create an Excel spreadsheet, add a tab to it and name it
Set ExApp = CreateObject("Excel.Application")
Set ExBook = ExApp.Workbooks.Add
ExApp.Visible = True


Set ExSheet = ExBook.Worksheets.Add

Sheets("Sheet4").Name = "Delivery Report"


'Open recordsets from the Access db
Set Rec = DBase.QueryDefs("qryReportDelivery_Crosstab").OpenRecordset
Set Rec2 = DBase.QueryDefs("qryReportDelivery_Crosstab_homesite").OpenRecordset


TaskCount = 0 'Initialize a counter to use to cycle through the tasks listed
EndCol = 0 'Not sure what this counter is used for
SiteCount = 0 'Initialize a counter to use to cycle through the sites
Alpha = "A" 'Still not clear, but know that it is used to handle cell addresses
Row = 1 'Initializing the row to start at 1
Col = 1 'Initializing the column to start at 1


' Headers for Excel Report
ExSheet.Cells(Row, Col) = "Task"
ExSheet.Range(Alpha & Col).EntireColumn.ColumnWidth = 40
ExSheet.Range(Alpha & Col).Font.Bold = True
Col = Col + 1
Alpha = NumToAlpha(Col)

' Print out the names of all the sites dynamically
' This is where I made my attempts - I tried several
things in this section of code
Do While Not Rec2.EOF

ExSheet.Cells(Row, Col) = Rec2("Homesite")
ExSheet.Range(Alpha & Col).EntireColumn.ColumnWidth = 10
ExSheet.Range(Alpha & Col).Font.Bold = True
SiteCount = SiteCount + 1
Col = Col + 2
Alpha = NumToAlpha(Col)

Rec2.MoveNext
Loop

ExSheet.Cells(Row, Col) = "Grand Total"
ExSheet.Range(Alpha & Col).EntireColumn.ColumnWidth = 10


Col = 1
Row = Row + 1


Do While Not Rec.EOF
Set Rec2 = DBase.QueryDefs("qryReportDelivery_Crosstab_homesite").OpenRecordset

ExSheet.Cells(Row, Col) = Rec("Task")
TaskCount = TaskCount + 1
Col = Col + 2


Do While Not Rec2.EOF
ExSheet.Cells(Row, Col) = Rec(Rec2("Homesite"))


Col = Col + 2
Rec2.MoveNext
Loop
EndCol = Col

ExSheet.Cells(Row, Col) = Rec("TotalOfHours")




Col = 1
Row = Row + 1

Rec.MoveNext
Loop





Col = 1
ExSheet.Cells(Row, Col) = "Grand Total"

Col = Col + 2

Alpha = "C"
Alpha2 = "B"
Col2 = 2

SiteCountTemp = SiteCount + 1
Do While SiteCountTemp > 0

ExSheet.Cells(Row, Col) = "=Sum(" & Alpha & "2:" & Alpha & (TaskCount + 1) & ")"


Col2 = Col2 + 2
Alpha2 = NumToAlpha(Col2)

Col = Col + 2
SiteCountTemp = SiteCountTemp - 1
ColNum = AlphaToNum(Alpha)
ColNum = ColNum + 2
Alpha = NumToAlpha(ColNum)

Loop


Row = 2
Col = 2
Alpha = "C"
TotalLine = TaskCount + 2
TaskCountTemp = TaskCount
SiteCountTemp = SiteCount + 1


Do While SiteCountTemp > 0

Do While TaskCountTemp > 0
ExSheet.Cells(Row, Col) = "=(" & Alpha & Row & "/" & Alpha & TotalLine & ")"
ExSheet.Cells(Row, Col).NumberFormat = "0.00%"

Row = Row + 1
TaskCountTemp = TaskCountTemp - 1

Loop

ExSheet.Cells(Row, Col) = "1.0"
ExSheet.Cells(Row, Col).NumberFormat = "0.00%"
TaskCountTemp = TaskCount


Row = 2
Col = Col + 2
Alpha = NumToAlpha(Col + 1)
SiteCountTemp = SiteCountTemp - 1


Loop


AlphaToNum (and vice versa) is just a function that assigns a number to a letter (and vice versa). Example: A=1, B=2, etc. It is used to help with positioning/maneuvering in the cells and it works fine throughout the code.

coachdan32

http://www.malehsbaseball.com

 

[PHV]

First, I'd play with Cells instead of Range (=> no need of AlphaToNum function ...)
Second, why setting .Font.Bold inside a loop if you you want only the header row to be bolded ?
Third, I prefer to play with FormulaR1C1 for cell's formula.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[coachdan]

I put it inside the loop because it is pulling the homesite from the datatbase and making the value a header then moving to the next and making it a header. It is not making the word "Homesite" a header, but rather the value pulled from the "Homesite" field.

coachdan32

http://www.malehsbaseball.com

 

[PHV]

So, trace the evolution of ExSheet.Range(Alpha & Col).Address inside your loop.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[coachdan]

How would you do that? I am new to VBA programming and have no clue how to do that.

coachdan32

http://www.malehsbaseball.com

 

[PHV]

Do While Not Rec2.EOF
ExSheet.Cells(Row, Col) = Rec2("Homesite")
MsgBox "Range(" & Alpha & Col & ") is really what I want ? " & ExSheet.Range(Alpha & Col).Address
ExSheet.Range(Alpha & Col).EntireColumn.ColumnWidth = 10


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244