Identifying Highligted or Bolded cells with VBA code to import into Access

[usa_dreamer2002]

Hi all,
this is a weird question. I receive a bunch of excel files with items to be added to a database. some of the items are to be added as new, some to be deleted and others to be modified.
I identify the action (add/delete/modify) by looking at a column in the excel file e.g. column A.
at this point I am fine with the add/delete because I can filter the data or bring all the files to a temporary table in an Access database via vba script and then running queries to do the rest.

My issue is that the spreadsheets have a lot of columns and for the modifications what they do is use the same file for instance, make changes on the cells (any cell) that need modification and then highlight, bold or underline only the items that need modification and ignore the rest of the file (a tedious process since I have to then open each file and manually modify the database). Can anyone advise of any way (or tool) that could be used to accomplish the modification task?
I would post the same in the MS Excel Forum as I could use help either in Access or Excel.

Thank you in advance.
​

 

[Rx_]

This is somewhat straightforward using Excel object code to identify the colors then setting up a reference to DAO to append directly to an Access table.
However, if you are not well versed in Excel object model, the learning curve would be huge.

So, if you are just jsing the built in tools to move the data to Access (or using Access to import an excel worksheet) let me offer this much more manual but straightforward method.

In the Excel workbook, set your Filter on the Header row.
Then Filter by Color. This should provide the color for lets say Append.
Now, select those specific color data.
Open a new Excel workbook, worksheet.
Now Paste Special - there is a choice to bring over the hidden data (the non color filtered) or just the data in your filter.
Now, import the data from this new Excel workbook.

Realizing it is much more manual, writing Excel object code with DAO recordset append code each time the cursor finds a specific color as it loops through each row will take some learning and time too.

And worse, if the Excel is not a template, and they add, subtract a column, the code would probably need to be modified each time. I had an opportunity to enforce templates for projects. Then hundreds of users filled out thousands of workbooks. Once set of code was more efficient to migrate it into SQL Server. Without an enforced template, it probably would have never worked.