UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Import Ms Excel Spreadsheet And Detect & Translate Colored Cells To Database Field?, Access 2010    
 
   
DominicG
post Oct 13 2017, 10:17 AM
Post#1



Posts: 232
Joined: 3-July 07
From: Fort Washington PA


Good morning,
I have a client who just LOVES their long complex spreadsheets. This is one of those textbook cases where a database is far superior for tracking this level of data. And I can import the spreadsheet into my database quite easily.

But here's the tricky part. They use color to determine the status of a task. Green is "Completed", Yellow is "Pending", and Red means "Immediate action is required". I was wondering if there was a VB Routine or method out there that would read the color and let me assign it's value to a check box. The other part I should mention is that the color is manually applied and NOT conditional.

Something like, "Site Survey Status" has a series of three check boxes next to it that are tied together. The code that imports the spreadsheet reads the color of the cell and enters data into the appropriate check box (or something like that)

Is that possible?

I found something that suggest it IS. But I'm unsure how to apply it to this particular problem
https://windowssecrets.com/forums/showthrea...ss-(Excel-2000)
Go to the top of the page
 
cheekybuddha
post Oct 13 2017, 10:47 AM
Post#2


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


Yes, you can do this, but you will no longer be able to use the import tool built in to Access.

You will have to open the spreadsheet in code and read each record in to the db record by record.

Is 'Site Survey Status' a table in the Access database?

If so, a couple of quick comments:

Avoid spaces in table/field names - it's not the end of the world but will definitely make your life easier.

You mention 3 checkboxes 'tied together'. Are these 3 yes/no fields in the table?

If so, it could be an indication of poor table design - worth fixing earlier rather than later.

Provide more detail and the folks here can advise.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
DominicG
post Oct 13 2017, 12:22 PM
Post#3



Posts: 232
Joined: 3-July 07
From: Fort Washington PA


David,
Thanks for the response.

The name "Site Survey Status" was just an example In the database it's actually "SiteSurvey" and is a text field that contains notes concerning the status of the Site Survey. The color of that Site Survey cell in the Excel Spreadsheet is what I need to look at and then assign some kind of value to a separate field called "SiteSurveyStatus". Perhaps the values could be as simple as "Completed", "Pending", Or "Action Required"?

The real goal here is to be able to set up a report that will sort out all of the current items with a "SiteSurveyStatus" of "Action Required"
Go to the top of the page
 
doctor9
post Oct 13 2017, 12:29 PM
Post#4


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


DominicG,

I suppose you could loop through each potentially-colored cell (I'm assuming they're all in one row or column), and place an expression in the next row/column over that displays a string based on the original cell's .Interior.Color property. You'd need a user defined function that would take the cell object as an argument, read the .Interior.Color property for that cell, and return a string. Shouldn't be too difficult.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
DominicG
post Oct 13 2017, 01:27 PM
Post#5



Posts: 232
Joined: 3-July 07
From: Fort Washington PA


Thanks for the reply Dennis

Because I've never done anything like this before, I need to look into what's needed to write such a routine. I'm familiar with writing simple routines within the confines of a database. But nothing like this. I've also been out of the loop with MS Access for a couple years. 3 years ago in my previous job I was using it on a daily basis. When I switched jobs that came to an end. Now that I switched jobs AGAIN, I'm back at it. So I need to refresh my memory a bit.

Have you got any pointers or places to look for more info on this kind of stuff?
Go to the top of the page
 
doctor9
post Oct 13 2017, 01:33 PM
Post#6


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


DominicG,

Yes. Click on the link in my post. It's the underlined text. It'll take you to a similar function I wrote a while back. It converts the color to an Integer value. You can add a Select Case statement or a series of If tests to see "If it's the integer value for red, then return "Completed" and so on.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
DominicG
post Oct 13 2017, 01:34 PM
Post#7



Posts: 232
Joined: 3-July 07
From: Fort Washington PA


Thank You! I'll let you know how I make out.
Go to the top of the page
 
kfield7
post Oct 13 2017, 01:36 PM
Post#8



Posts: 775
Joined: 12-November 03
From: Iowa Lot


Or approach it on the other end--can you modify the Excel sheet?
You could insert a column and a VBA function to check the color of the referenced cell next to it. Then import the data into Access.
If you are frequently receiving updates to the spreadsheet, though, it might be better in the long run to create the code in your Access import as suggested.
Go to the top of the page
 
kfield7
post Oct 13 2017, 01:39 PM
Post#9



Posts: 775
Joined: 12-November 03
From: Iowa Lot


Is the color in the spreadsheet set manually or is it a conditional format?
If conditional, your import routine can test the same criteria more directly.
Go to the top of the page
 
DominicG
post Oct 13 2017, 01:59 PM
Post#10



Posts: 232
Joined: 3-July 07
From: Fort Washington PA


We expect to get an updated spreadsheet every week.

But I like your idea. If all else fails, I could convince them to add some columns to their spreadsheet and supply the required code that may be another direction to take.
Go to the top of the page
 
doctor9
post Oct 13 2017, 03:22 PM
Post#11


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


DominicG,

It's a much better idea for the data supplier to encode this information as a value in a cell rather than as a color in a cell. If you can get them to do that, your task becomes much simpler, obviously.

I would think they'd much prefer putting "Pending" in a cell rather than coloring a cell to indicate that it's pending.

Good luck!

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
gemmathehusky
post Oct 16 2017, 11:20 AM
Post#12


UtterAccess VIP
Posts: 4,432
Joined: 5-June 07
From: UK


I think it might depend on what the spreadsheet is used for.

If they need the visual check that the column provides, then they won;t be happy about using a text column.
They could have both and hide the text column of course.

If they use spreadsheets, it begs the question as to why you need the database as well.....

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    14th December 2017 - 09:44 PM