Reading Excel Strikethrough Property via OLEDB

I have a module in an Access 2003 project that opens an OLEDB connection to an Excel 2003 document.  The code looks something like this:

Public Sub ConnectToExcel()

Dim cn As New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\myexcel.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"

Dim rst As New ADODB.Recordset
rst.Open ("SELECT * FROM [Sheet1$]"), cn, adOpenDynamic, adLockOptimistic

Do Until rst.EOF = True

    If Len(rst(1) > 0)  Then
        MsgBox rst(1)
    End If
    rst.MoveNext
Loop

End Sub

I'd like to select only the records where the strikethrough property of the cell in column B is FALSE.  Does anyone know how to accomplish this?

Thanks in advance.
LVL 1
hennessymAsked:
Who is Participating?
 
nexusnationConnect With a Mentor Commented:
What you'll have to do is use Excel automation to use this.  See http://www.mvps.org/access/modules/mdl0035.htm.  I don't think OLEDB will translate over such font styles.
0
 
nexusnationCommented:
The property is:

ActiveCell.Font.Strikethrough

Thus,

If ActiveCell.Font.Strikethrough = False Then
  ' NO strikethrough
End If
0
 
hennessymAuthor Commented:
Thanks, nexus.  I could use ODBC...do you think that would work any better?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
nexusnationCommented:
Take a look at the link above, which discusses OLE Automation.  Instead of opening the Excel file as a recordset, you will be actually opening the Excel file (via "Automation") and copying in the records.
0
 
Patrick MatthewsCommented:
Hello hennessym,

nexusnation is correct about having to go with OLE automation here.  Do you need help with setting that
up?

Regards,

Patrick
0
 
hennessymAuthor Commented:
Thanks, guys.  I think I'm just going to add a column to the spreadsheet so that the strikethrough property is superfluous and I can retrieve the required information from the new column via OLEDB.  Nexus, I'm awarding you the points.  Thanks again.
0
 
nexusnationCommented:
Works for me.  Thanks :)
0
 
nexusnationCommented:
Oops, just one thing, I'm wondering about the B grade.  Could I have been more detailed or answered your question more fully?
0
 
hennessymAuthor Commented:
The B grade was more a reflection that the solution wasn't exactly what I was looking for, e.g. I was hoping to solve the problem w/o resorting to excel automation, but that's not your fault - it's a limit of OLEDB.  I'm happy to change the grade to an A if the moderators allow it.
0
 
nexusnationCommented:
<< The B grade was more a reflection that the solution wasn't exactly what I was looking for, e.g. I was hoping to solve the problem w/o resorting to excel automation, but that's not your fault - it's a limit of OLEDB. >>

You hit the nail on the head. :) Regarding a moderator, I'll flag one down.  Thanks again!
0
 
nexusnationCommented:
There's already one here. Would you look at that.  Saved me a lot of trouble. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.