Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    XLQUERY.XLA in Ex2002 (2002)

    Where's XLQUERY.XLA in EXCEL XP (2002)... I'm using refrerences to this in my VBA code. I noticed that in EX2002 this file is listed as "missing". Tried to install but couldn't find it...

    EJ

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XLQUERY.XLA in Ex2002 (2002)

    Hi ErikJan,

    Is this any help:

    http://support.microsoft.com/default.aspx?...b;en-us;Q277620

    If so, don't save in that format. It is buggy and when no-one uses XL95 or older unnecessary. XP files can be read from XL97 and up.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XLQUERY.XLA in Ex2002 (2002)

    Not fully with you... I have an VBA app running in XL97/XL2000 now (not 95). It requires a reference to XLQUERY.XLA [as I access some db's].
    Next year the users will all get WinXP/OfficeXP systems; there XLQUERY is no longer present. What will happen to my application / how do I have to change it?

    Erik Jan

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XLQUERY.XLA in Ex2002 (2002)

    I guess you would benefit from changing the whole thing to use ADO. Here is some sample code:

    Option Explicit

    Sub ADOImportFromAccessTable(DBFullName As String, TableName As String, TargetRange As Range, sQuerystring As String)
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
    Set TargetRange = TargetRange.Cells(1, 1)
    ' open the database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";"
    Set rs = New ADODB.Recordset
    With rs
    ' open the recordset
    '.Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable ' all records
    .Open sQuerystring, cn, , , adCmdText ' filter records

    RS2WS rs, TargetRange ' write data from the recordset to the worksheet

    ' ' optional approach for Excel 2000 or later (RS2WS is not necessary)
    ' For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
    ' TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
    ' Next
    ' TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data

    End With
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    End Sub


    'RS2WS rs, Range("A3") ' rs is an ADO recordset variable


    Sub RS2WS(rs As ADODB.Recordset, TargetCell As Range)
    Dim f As Integer, r As Long, c As Long
    If rs Is Nothing Then Exit Sub
    If rs.State <> adStateOpen Then Exit Sub
    If TargetCell Is Nothing Then Exit Sub

    With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .StatusBar = "Writing data from recordset..."
    End With

    With TargetCell.Cells(1, 1)
    r = .Row
    c = .Column
    End With

    With TargetCell.Parent
    .Range(.Cells(r, c), .Cells(.Rows.Count, c + rs.Fields.Count - 1)).Clear ' clear existing contents
    ' write column headers
    For f = 0 To rs.Fields.Count - 1
    On Error Resume Next
    .Cells(r, c + f).Formula = rs.Fields(f).Name
    On Error GoTo 0
    Next f
    ' write records
    On Error Resume Next
    rs.MoveFirst
    On Error GoTo 0
    Do While Not rs.EOF
    r = r + 1
    For f = 0 To rs.Fields.Count - 1
    On Error Resume Next
    .Cells(r, c + f).Formula = rs.Fields(f).Value
    On Error GoTo 0
    Next f
    rs.MoveNext
    Loop
    .Rows(TargetCell.Cells(1, 1).Row).Font.Bold = True
    .Columns("A:IV").AutoFit
    End With

    With Application
    .StatusBar = False
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    End With
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •