Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Reading Excel Strikethrough Property via OLEDB

Posted on 2008-06-18
13
Medium Priority
?
651 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 1200 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 93

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

721 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