Solved

Excel cell colors into Access table

Posted on 2014-11-05
7
281 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 50

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

840 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