Originally Posted by
Zethro
hi all,
I want to create a calendar-type of report with notes in user-selectable cells (x,y grid-type of report plotted on a ~3'x10' paper strip for wall display). In addition, permit the user to select the background color of each note. A mini-version of the reports works in Access, but the 22" report-max width is killin' me.
I have the source data in a simple format
category(row)
weeknum(column)
Note (free text)
cellcolor (the numeric code for the *.backcolor property)
I can export the crosstabbed Notes to their right cells in Excel, and crosstab cellcolor to get their proper x,y property locations... I thought about setting sheet1 as the notes, sheet2 as the colorcodes and then all I'd have to do is figure how to build a template in Excel when sheet1.backcolor = each cell property in Sheet2... but I do not know how to accomplish that either.
Is there any way to tell Excel to set a cell property (backcolor of the cell in this case) to the contents of an Access field?
Thoughts?
Thanks,
Keith
Keith,
You could try something like this to create the Excel worksheet:
Code:
Sub MakeMySheet()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set cnn = CurrentProject.Connection
With rs
.ActiveConnection = cnn
.Open "SELECT * FROM MyTable" 'modify as needed
Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.ActiveWorkbook
Set xlWS = xlWB.Worksheets(1)
Do Until .EOF
xlWS.Cells(!Category, !weeknum).Value = !Note
xlWS.Cells(!Category, !weeknum).Interior.ColorIndex = !cellcolor
.MoveNext
Loop
.Close
End With
Set rs = Nothing
Set cnn = Nothing
xlWB.Save "c:\yourfolder\yourfilename.xls"
xlWB.Close
Set xlWS = Nothing
Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub
You can add additional formatting, if needed.
Reply With Quote
