VBA ADO Connection to Excel XP

  • We've been using a tool in our department to take information from an Excel spreadsheet and send it into a Word document. At this point in time we're using Office 97, but an upgrade to Office XP is in the not too distant future. Currently, the connection code reads as follows (hope it doesn't wrap weird):

    Set conn = New ADODB.Connection

    With conn

    .Provider = "Microsoft.Jet.OLEDB.4.0"

    .Properties("Data Source") = strName

    .Properties("Extended Properties") = "Excel 8.0;HDR=Yes;IMEX=1"

    End With

    The problem is that we're no longer dealing with "Excel 8.0". According to MSDN, there is no information for Excel XP:

    http://207.46.196.115/library/default.asp?url=/library/en-us/odbc/htm/odbcjetexcelsqlgetinfo.asp

    Does anyone know a workaround?

    Thanks in advance,

    Matt

  • I would be surprised to find that it wont be supported, more likely a matter of figuring out what to call it. An obvious guess would be Excel.10. Should you run into problems for whatever reason, I can think of two alternatives. One is to just pull the info out via automation, essentially opening the workbook via code and pulling the information out - then using directly, throwing into an array or table, whatever. The other would be to convert the document via save as functionality down to the 97 format. We've been using the Word spell checker for a few things and I always set a reference the Word97.olb library even though I have Word 2000 and/or XP on my system - gives me backwards compatibility. You might find something similar would work. Any help? Sorry for the late reply, was out of the office last week.

    Andy

  • Hey, that's OK. After spending some more time on this forum I figured it might not be an appropriate place to post it. My first problem was that I wasn't loading the ADO Object Library in the VBA IDE. I never noticed that it needed to be loaded, and then I kept noticing references to that fact in the books I was looking in. Musta kept skipping over it. 🙂 That's what I get for skipping.

    I'll try using Excel.10 and see if that works. Thanks for your help!!

    Matt

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply