• Read cell color while importing into access (Excel 2000)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Read cell color while importing into access (Excel 2000)

    Author
    Topic
    #375317

    Hi ,
    I need some help on these 2 problems. I get a excel sheet from users.
    (1) In one of the column depending on the status of a task, cells are filled with either RED or GREEN color. I import this excel sheet to Access and then generate a report. Now in this report my users wants to see the same colors as in excel sheet. Is there a way to read the color in excel sheet while importing ??

    (2) This task column in excel usually has more than 255 characters so when I import in into Access a memo field is created for this particular column. Sometimes I am required to export this access table back to Excel and in that process this memo field gets truncated. Is there any way to work around this problem??

    PLEASE HELP SOMEBODY
    Thanks in advance,
    Isha

    Viewing 2 reply threads
    Author
    Replies
    • #610348

      (1) If the color is applied according to some criteria, you can use the same criteria to color text boxes in the Access report.

      If you really need to read the cell color, you can’t do it while importing – Access only imports data into a table, no formatting. But you can use Automation to open the Excel spreadsheet from within Access, and use Excel VBA to read the cell colors. It’ll be a lot of work, though. If you do a search in the Access forum for Excel.Application, you’ll find lots of posts dealing with controlling Excel from Access, for instance post 145733 and post 170120.

      (2) Consider linking the Excel sheet in Access instead of importing it.

    • #610361

      Another way to get memofields into XL completely is by using (from XL) Data, get external data, new database query.

      • #610379

        Can you explain it a little more

        • #610577

          Follow these steps (in XL):

          – Insert a blank sheet (or choose one)
          – Data, Get External Data, New Database query
          – Choose “MS Access ??? Database”, OK
          – Browse to the database file
          – Choose the table you need (either expand the table in the lefthand box and select the fields or click the table and press the button with the small arrow to select all fields)
          – Next, select fields that need criteria
          – Next change the sort options
          – Next Select what XL should do with the query (wise to save it)
          – Click Finish and select the target cell of the data to be returned. Also, click properties for more options.

    • #610461

      The User Defined Function below will return the colorindex or the cell background of the cell passed as a parameter. You could put this in another column and then export the color numbers to Access. I would assume that some VBA code in Access might turn these numbers back into colors.

      Public Function getcolorNum(oCell As Range) As Long
          Application.Volatile
          getcolorNum = oCell.Interior.ColorIndex
      End Function
      

      Unfortunately, Excel does not recognize a color change as a recalculate event. So, if you change the color of one of the cells, the function will not recalculate and return the new colorindex number. However, the way the function is coded, if you make any change in any cell, it should recalculate.

    Viewing 2 reply threads
    Reply To: Read cell color while importing into access (Excel 2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: