Solved

Reading Excel Strikethrough Property via OLEDB

Posted on 2008-06-18
13
642 Views
Last Modified: 2013-12-05
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
Comment
Question by:hennessym
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
13 Comments
 
LVL 12

Expert Comment

by:nexusnation
ID: 21815931
The property is:

ActiveCell.Font.Strikethrough

Thus,

If ActiveCell.Font.Strikethrough = False Then
  ' NO strikethrough
End If
0
 
LVL 12

Accepted Solution

by:
nexusnation earned 300 total points
ID: 21815941
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
 
LVL 1

Author Comment

by:hennessym
ID: 21815990
Thanks, nexus.  I could use ODBC...do you think that would work any better?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 12

Expert Comment

by:nexusnation
ID: 21816131
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 21818907
Hello hennessym,

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

Regards,

Patrick
0
 
LVL 1

Author Comment

by:hennessym
ID: 21832250
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
 
LVL 12

Expert Comment

by:nexusnation
ID: 21834567
Works for me.  Thanks :)
0
 
LVL 12

Expert Comment

by:nexusnation
ID: 21834577
Oops, just one thing, I'm wondering about the B grade.  Could I have been more detailed or answered your question more fully?
0
 
LVL 1

Author Comment

by:hennessym
ID: 21834622
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
 
LVL 12

Expert Comment

by:nexusnation
ID: 21834672
<< 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
 
LVL 12

Expert Comment

by:nexusnation
ID: 21834677
There's already one here. Would you look at that.  Saved me a lot of trouble. :)
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question