Solved

Reading Excel Strikethrough Property via OLEDB

Posted on 2008-06-18
13
628 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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

822 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