Sign in to follow this  
Followers 0
Santana

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

3 posts in this topic

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.

Just another special date with a different challenge

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0