?
Solved

Excel cell colors into Access table

Posted on 2014-11-05
7
Medium Priority
?
296 Views
Last Modified: 2014-12-03
Hi,

I have a monthly excel workbook files for several years, each with 1-31 worksheets.  Each worksheet is a daily schedule with 10 machines as columns B-K and 30 minute time slots between 8AM and 10PM as rows 4-32.  Each day essentially has 280 data elements.  When the machine is used the cell color is changed to one of several colors depending on the process that was used.

I need to get this data into an MS Access table for analysis.

Table structure is : ID, DateTime, MachineNo, ProcessNo
ID=PK Autonumber

I think I can loop through the worksheets, then the cells but how do I read the cell color using vba and scan the data?

Thanks in advance.

Bill
0
Comment
Question by:Bill Ross
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 40423679
Hi,

the color property is found under

Range.Interior.Color

Regards
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40423757
or you can use Range.Interior.ColorIndex, which may be more user friendly.
0
 
LVL 14

Author Comment

by:Bill Ross
ID: 40423764
Hi Phillip,

I don't work much with Excel vba - mostly Access.  So do I define a range as a single cell, read the color and then go to the next cell?  Can you give me some sample code to get me started?

Thanks,

Bill
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 24

Accepted Solution

by:
Phillip Burton earned 2000 total points
ID: 40423796
The following code will copy the ColorIndex of columns B-K, rows 1-10000, into columns AB-AK. Make sure you have saved your spreadsheet before running this code, and you cannot undo it.

Sub ExtractColours()
For intcol = 2 To 11
    For introw = 1 To 10000
        Cells(introw, intcol + 26) = Cells(introw, intcol).Interior.ColorIndex
    Next
Next
End Sub

Open in new window


The above code assumes by "color" you mean "background color".

For a translation of the numbers issued by ColorIndex, see this page: http://msdn.microsoft.com/en-us/library/office/ff840443(v=office.15).aspx

(-4142 means "no color").
0
 
LVL 14

Author Comment

by:Bill Ross
ID: 40423801
Hi Phillip,

OK.  I'll give it a try.

Thanks,

Bill
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40478141
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question