Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jan 2002
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Read cell color while importing into access (Excel 2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Read cell color while importing into access (Excel 2000)

    (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>post 145733</post#> and <post#=170120>post 170120</post#>.

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

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Read cell color while importing into access (Excel 2000)

    Another way to get memofields into XL completely is by using (from XL) Data, get external data, new database query.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Lounger
    Join Date
    Jan 2002
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Read cell color while importing into access (Excel 2000)

    Can you explain it a little more

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Read cell color while importing into access (Excel 2000)

    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.

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


    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.
    Legare Coleman

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Read cell color while importing into access (Excel 2000)

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •