Solved

Excel cell colors into Access table

Posted on 2014-11-05
7
271 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
7 Comments
 
LVL 49

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 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 46

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now