Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2005
    Posts
    1

    Can we post Access cell data to Excel cell properties?

    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

  2. #2
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275

    Re: Can we post Access cell data to Excel cell properties?

    Quote 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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Call Excel Data into Access table
    By jiguvaidya in forum Import/Export Data
    Replies: 0
    Last Post: 09-15-2008, 04:58 AM
  2. VBA Opening Excel File and Placing Data in Cells
    By BobTHG in forum Programming
    Replies: 0
    Last Post: 01-13-2008, 03:01 PM
  3. Inserting into multible cell
    By Intersysop in forum Access
    Replies: 1
    Last Post: 10-19-2006, 08:07 AM
  4. problem adding properties to a access db
    By dnzone in forum Programming
    Replies: 0
    Last Post: 10-03-2006, 11:02 PM
  5. Replies: 1
    Last Post: 01-10-2006, 12:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums