Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 658
  • Last Modified:

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.
0
hennessym
Asked:
hennessym
  • 7
  • 3
1 Solution
 
nexusnationCommented:
The property is:

ActiveCell.Font.Strikethrough

Thus,

If ActiveCell.Font.Strikethrough = False Then
  ' NO strikethrough
End If
0
 
nexusnationCommented:
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
 
hennessymAuthor Commented:
Thanks, nexus.  I could use ODBC...do you think that would work any better?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now