call a query in vba excel

[Bebe]

Anyone have the code to open a connection and call a query in excel using vba?

thanks

and then how do i populate the parameters into different cells in exceL?

[Mongo]

Granted this is Sybase, RDO, and I'm calling SPs but should may give you a few ideas...

Option Explicit

Private Declare Function FindWindow Lib "User32" Alias "FindWindowA" _
  (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function LockWindowUpdate Lib "User32" _
  (ByVal hwndLock As Long) As Long

Private mc As String, ub As String
'
Public Sub DoSomethingUseful()
  FreezeScreenUpdating Application.Caption
  DropAddSheet "B"
  If Not GetDataFor("A1") Then Exit Sub
 ' yada, yada...
End Sub

Private Function GetDataFor(dataFrom As String) As Boolean
Dim sdt As String, SQL_str As String, destRng As String, connectStr As String
Dim v As Variant, wN As Name, qt As QueryTable
  GetDataFor = False
  On Error GoTo oops
  If Len(dataFrom) > 0 Then
    destRng = "A1"
    Select Case dataFrom
      Case "A1": SQL_str = "storedProc1 'A1', 101, null, null"
      Case "A2": SQL_str = "storedProc2 'A2', 101,  '" & _
        Format(CDPV("Begin Date"), "General Date") & "' , '" & _
        Format(CDPV("End Date"), "General Date") & "'"
      Case "A3": SQL_str = "storedProc2 'A3', 101, null, null": destRng = "B1"
      Case "A6": SQL_str = "storedProc3 'A6', 101, null, null": destRng = "M1"
      Case "B3": SQL_str = "storedProc4 'B3'": destRng = "S1"
      Case Else: SQL_str = ""
    End Select
    If Len(SQL_str) > 0 Then
      ' store UID
      If Len(ub) = 0 Then ub = GetProp("LastUID", "", msoPropertyTypeString)
      connectStr = "ODBC;DRIVER={Sybase System 11};SRVR=SYBASE;DB=[database_name];UID=" & ub & ";PWD=" & mc
      Set v = ActiveSheet.QueryTables
      With ActiveSheet.QueryTables.Add(Connection:=connectStr, Destination:=Range(destRng))
        .Sql = Array(SQL_str):                .FieldNames = True
        .RefreshStyle = xlInsertDeleteCells:  .RowNumbers = False
        .FillAdjacentFormulas = False:        .RefreshOnFileOpen = False
        .HasAutoFormat = True:                .BackgroundQuery = True
        .TablesOnlyFromHTML = True:           .Refresh BackgroundQuery:=False
       '  since call multiple times, capture PWD on first pass for later session reuse...
        If Len(mc) = 0 Then
          destRng = v(1).Connection
          mc = Mid(destRng, InStr(1, destRng, "UID=") + 4, Len(destRng))
          destRng = Mid(mc, 1, InStr(1, mc, ";") - 1)
          SetProp "LastUID", destRng, msoPropertyTypeString
          mc = Mid(mc, Len(destRng) + 6, Len(mc))
          mc = Mid(mc, 1, InStr(1, mc, ";") - 1)
        End If
        .SavePassword = False:  .SaveData = True
      End With
    End If
  End If
  GetDataFor = True
oops:
  If v Is Nothing Then Set v = ActiveSheet.QueryTables
  For Each qt In v
    If InStr(1, qt.Name, "ExternalData") > 0 Then qt.Delete
  Next qt
  For Each wN In Application.Parent.Names
    If InStr(1, wN.Name, "ExternalData") > 0 Then wN.Delete
  Next wN
  Set qt = Nothing:  Set wN = Nothing:  Set v = Nothing
  If Not GetDataFor And Not force Then MsgBox "Cannot connnect to data source." & _
    vbCrLf & "Process halted.  Please try again later."
End Function

Private Sub FreezeScreenUpdating(sWindowCaption As String)
Dim lHwnd As Long, lReturnVal As Long
  lHwnd = FindWindow(vbNullString, sWindowCaption)
  lReturnVal = LockWindowUpdate(lHwnd)
End Sub

Private Sub UnFreezeScreenUpdating()
Dim lReturnVal As Long
  lReturnVal = LockWindowUpdate(0)
End Sub

Private Sub DropAddSheet(ID As String)
  On Error GoTo oops
  If SheetExists(ID) Then ClearSheet ID
  Worksheets.Add.Move After:=Worksheets(Worksheets.Count)
  ActiveSheet.Name = ID:  Range("A1").Select
oops:
End Sub

Private Sub ClearSheet(ID As String)
  On Error GoTo oops
  If SheetExists(ID) Then
    Application.DisplayAlerts = False
    If Sheets(ID).Visible = xlSheetVeryHidden Then Sheets(ID).Visible = xlSheetVisible
    Sheets(ID).Delete
  End If
oops:
End Sub

Public Function GetProp(CDPName As String, CDPValue As Variant, _
  Optional CDPType As Long) As Variant
Dim oCDP, oProp
  If IsMissing(CDPType) Then CDPType = msoPropertyTypeString
  Set oCDP = ActiveWorkbook.CustomDocumentProperties
  GetProp = CDPValue
  ' Compare each custom document property to see if it exists.
  For Each oProp In oCDP
  ' If Custom Property exists...
    If oProp.Name = CDPName Then
      With oProp
        .LinkToContent = False:  GetProp = .Value
      End With
      Exit Function
    End If
  Next oProp ' No match found, so create new property and value.
  oCDP.Add Name:=CDPName, Value:=CDPValue, Type:=CDPType, LinkToContent:=False
  Set oCDP = Nothing
End Function

[Nitro]

I did not know that Excel has a query. Do you mean Excel calling a query from Access or Microsoft SQL?

[Bebe]

yes i want to call a query from sql ; thanks for clarifying

[Nitro]

Check this thread out first!

I posted this morning for a similar question.
http://forums.vb-world.net/showthrea...threadid=19011

If you need further help, I have five other different method to execute a Microsoft SQL database. Have you looked into ADO controls or the Data Environment yet?

It does pretty much the samething.


DID THAT CODE I PROVIDED TO YOU LAST TIME WORK? THE ONE PASSING PARAMETERS TO A STORE PROCEDURE.

THE ONE THAT WAS TO THIS THREAD.
http://forums.vb-world.net/showthrea...threadid=17618

[Edited by Nitro on 06-09-2000 at 05:56 PM]