How to identify highlighted or bolded text in a cell in an excel spreadsheet

[Santana]

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 (a tedious process since I have to then open each file and then import them into access). I am looking for a way to open the file via script, identify the highlighted cells or the bolded text and writing the value of these cells into a text file or directly into an access database. I would really appreciate if anyone could point me to the right direction and thank you all in advance.

Thank you in advance.

[l3ill]

Looks like you may be able to use the 

_Excel_FilterSet

 

you will need to find out how to set the Custom Sort Option called "Cell Color" with this function.

 

 

good luck !

[junkew]

create first a testbook.xlsm with

A1=12 in bold

A2=34

A3=56 with yellow background

A4=78 with red text

const $xlsTRUE=-1
const $xlsYellow=65535
const $xlsRed=-16776961

$oAppExcel=objcreate("Excel.application")
$oAppExcel.visible=$xlsTRUE
$wb=$oAppExcel.workbooks.open("testbook.xlsm")

consolewrite($wb.name & @CRLF)
$i=1
while $wb.worksheets(1).cells($i,1).value <>""
   consolewrite("row "& $i & " with value " &  $wb.worksheets(1).cells($i,1).value)
   if $wb.worksheets(1).cells($i,1).font.bold=$xlsTRUE Then
      consolewrite(" Text is bold ")
   EndIf
   if $wb.worksheets(1).cells($i,1).interior.color=$xlsYellow Then
      consolewrite(" Text background is yellow ")
   EndIf
   if $wb.worksheets(1).cells($i,1).Font.Color = $xlsRed Then
      consolewrite(" Text is bold ")
   EndIf
   consolewrite(@CRLF)
 
   $i=$i+1
   
WEnd

Exit

 

would then give something like this

  

testbook.xlsm
row 1 with value 12 Text is bold
row 2 with value 34
row 3 with value 56 Text background is yellow
row 4 with value 78

 

search forum for a specific excel library which also has examples