MS Query in Excel 2010 1

[shelby55]

Hello

I had a worksheet that worked great in 2003 but it's not working now in 2010. I figured I would recreate it in 2010.

I have a RawData worksheet which has client visit information (one line of data is one patient visit). I have referral date and assessment date. On the one worksheet I wish to query RawData and pull across the referral and assessment date as long as there is an assessment date.

Skip helped me in the previous project to create an automatic requery using the following code:

Option Explicit

Private Sub Worksheet_Activate()
    Dim sConn As String, sSQL As String, sPath As String, sDB As String
    
    sPath = ThisWorkbook.Path
    
    sDB = Split(ThisWorkbook.Name, ".")(0)
    
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xls;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"

    With ActiveSheet
       With .QueryTables(1)
            .Connection = sConn
            Debug.Print .CommandText
            .Refresh False
        End With
        .Calculate
    End With
End Sub

This code is now stopping on

With .QueryTables(1)

Can anyone advise why that would be? What do I need to do to get this to query upon worksheet activation? Why do I get the error message "ODBC Excel Driver Login Failed" when I try to edit the query?

Thanks.

 

[SkipVought]

Teh DRIVERID is not correct for 2010. You might google to find. Use Connection Strings, excel 2010.

Also...

    sDB = ThisWorkbook.Name
    
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ";"
'.......

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

Yes, sorry, you advised me of that earlier but I still couldn't get it to work.

Also, I can manually refresh the data no problem but if I add a macro and then save the document as a macro enabled Excel document (which I'm forced to do on exit), then the refresh, manual or otherwise, doesn't work. Why is that?

 

[xlbo]

if the code stops on the with Querytables(1) it means that there is not a querytable on the sheet!

..or there is and it is querytables(0)
can;t remember if the z_index for the collection is 0 based or not...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Geoff, I should have picked that up. Actually for Excel 2007+, when you ADD a querytable, (Shelby, you need to macro record adding a QT and observe the recorded code, which will tell you the latest DriverID) Excel now has this kind of object structure...

SheetObject.ListObject.QueryTableObject

The ListObject is new to the object model and your code should be changed to reflect that chnage as well as the new DriverID. RECORD a macro and post back with your code, if you need help. It will look something like this...

    With ActiveSheet
'assuming ONE structured table on this sheet...
       With .ListObjects(1).QueryTable
            .Connection = sConn
            Debug.Print .CommandText
            .Refresh False
        End With
        .Calculate
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

Thanks Skip and Geoff but now it's stopping at .Refresh False.

As stated in another entry in this thread, even manual refresh doesn't work once the document is saved as a macro enabled worksheet.

 

[SkipVought]

Please post your code as currently modified.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

Option Explicit

Private Sub Worksheet_Activate()
    Dim sConn As String, sSQL As String, sPath As String, sDB As String
    
    sPath = ThisWorkbook.Path
    
    sDB = Split(ThisWorkbook.Name, ".")(0)
    
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xls;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"

    With ActiveSheet
       With .ListObjects(1).QueryTable
            .Connection = sConn
            Debug.Print .CommandText
            .Refresh False
        End With
        .Calculate
    End With
End Sub

I did as you suggested to get the new driver ID.

 

[SkipVought]

AGAIN...

'......
    sDB = ThisWorkbook.Name
    
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ";"
'......

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Skip I'm not understanding what you're looking for here.....

 

[SkipVought]

I corrected the code that you posted twice!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Thanks Skip!! That fixed it.