none
ThisWorkbook.Connections("DataConnection").OLEDBConnection - Object doesn't support this action RRS feed

  • Question

  • I have an excel sheet in which we enter the from date and to date which is fed into the sql query. Based on what dates are entered in the cells the sql query gets updated and has to refresh the data on the table which is pulled from SQL. I have created a workbook connection called DataQuery in excel.

    Below is the code i have which connects to the sql database but the data connection refresh fails.

    Public Sub RefreshAll()
        'Refreshes all data connections
        'Refresh Data (like clicking Refresh All in the Data menu in Excel)
        Dim cn As WorkbookConnection
        Dim ocn As OLEDBConnection
        Dim sQryNew As String, sQryOld As String
        Dim sNewConn  As String, sOldConn  As String
        Dim bDone As Boolean
        Dim lCtr As Long
        Dim cnt As ADODB.Connection
        Dim rst As ADODB.Recordset
        Dim stDB As String, stConn As String, stSQL As String
        
        'Set database connection string. This connects excel with SQL database
        Set cnt = New ADODB.Connection
       
            
            stConn = "Provider=SQLOLEDB.1;Password=readonly;Persist Security Info=True;User ID=saodbc;Initial Catalog=Prod;Data Source=SQL"
        
        With cnt
            .Mode = adModeRead
            .CursorLocation = adUseClient
            .ConnectionString = stConn
            .Open
        End With
         
        Set rst = New ADODB.Recordset
         
        'The query is picked up from an excel cell from a worksheet called Runtime_info
        Let sQryNew = ThisWorkbook.Worksheets("Runtime_Info").Range("$B$9").Value
        
         
        MsgBox "Beginning data refresh ...", vbOKOnly + vbInformation
        
        ThisWorkbook.RefreshAll
        DoEvents

    --->>> from here the code below gives me an error Object doesn't support this action
      With ThisWorkbook.Connections("DataQuery").OLEDBConnection
            'Update the connection info if required
            Let sOldConn = .Connection
            If (sOldConn <> sNewConn) Then
                'Connection has changed so update it
                .Connection = sNewConn
            End If

            'Insert the new query if necessary
            Let sQryOld = .CommandText
            If (sQryOld <> sQryNew) Then
                'Query has changed so update it
                .CommandText = sQryNew
            End If
        End With

        
        MsgBox "Data refresh complete...", vbOKOnly + vbInformation
    End Sub

    Thursday, September 25, 2014 11:56 AM

All replies

  • Your connection name has likely changed. I don't see any code that sets the name of the connection.
    Thursday, September 25, 2014 1:14 PM
  • I have set the Data Connections in Excel using Data Connection

    Thursday, September 25, 2014 11:10 PM
  • Hi Jaggy99,

    >>from here the code below gives me an error Object doesn't support this action<<

    Do you mean the code below caused the issue?

    With ThisWorkbook.Connections("DataQuery").OLEDBConnection

    If so, please check the connections in current workbook:

    In addition, go through the code, the code below will cause an issue since you have not set the value of "sNewConn":

    Regards,

    George.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, September 26, 2014 5:56 AM
    Moderator
  • Hi George,

    Actually the previous IT guy has got this working using ODBC and works fine. I need the code to connect to sql using OLEDB and when I click refresh, excel should run the sql query and update the table on the worksheet with the new data. Below is the code which connects using ODBC which works fine but is messy.

    Option Explicit

    Public Sub RefreshAll()
        'Refreshes all data connections
        'Rfresh Data (like clicking Refresh All in the Data menu in Excel)
        'Then updates all pivot tables
        'Then updates headers on all sheets
        Dim wc As WorkbookConnection
        Dim sQryNew As String, sQryOld As String
        Dim bDone As Boolean
        Dim lCtr As Long
        Dim sFileName As String
        Dim iFileNum  As Integer
        Dim sParam1   As String
        Dim sDRIVER   As String
        Dim sSERVER   As String
        Dim sAPP      As String
        Dim sWSID     As String
        Dim sDATABASE As String
        Dim sParam7   As String
        Dim sUID      As String
        Dim sPWD      As String
        Dim sNewConn  As String
        Dim sOldConn  As String

        'First get the connection from the DSN parameter file

        sFileName = ThisWorkbook.Worksheets("Runtime_Info").Range("$B$7").Value
        iFileNum = FreeFile()
        Open sFileName For Input As iFileNum
          Line Input #iFileNum, sParam1
          Line Input #iFileNum, sDRIVER
          Line Input #iFileNum, sSERVER
          Line Input #iFileNum, sAPP
          Line Input #iFileNum, sWSID
          Line Input #iFileNum, sDATABASE
          Line Input #iFileNum, sParam7
          Line Input #iFileNum, sUID
          Line Input #iFileNum, sPWD
          Close iFileNum

        'Build the connection string from file information

        sNewConn = "ODBC;" & sDRIVER & ";" & sSERVER & ";" & sUID & ";" & sPWD & ";" & sAPP & ";" & sWSID & ";" & sDATABASE
        
        'Second assign the propery query
        Let sQryNew = ThisWorkbook.Worksheets("Runtime_Info").Range("$B$9").Value
        
        With ThisWorkbook.Connections("DataQuery").ODBCConnection
            'Update the connection info if required
            Let sOldConn = .Connection
            If (sOldConn <> sNewConn) Then
                'Connection has changed so update it
                .Connection = sNewConn
            End If

            'Insert the new query if necessary
            Let sQryOld = .CommandText
            If (sQryOld <> sQryNew) Then
                'Query has changed so update it
                .CommandText = sQryNew
            End If
        End With
        
        MsgBox "Beginning data refresh ...", vbOKOnly + vbInformation
        
        ThisWorkbook.RefreshAll
        DoEvents
        'Because RefreshAll may have background queries running we need
        'to make sure they have all run first before updating pivottables
        
        lCtr = 0
        Do
            DoEvents
            Let bDone = True
            For Each wc In ThisWorkbook.Connections
                DoEvents
                If (wc.ODBCConnection.Refreshing) Then Let bDone = False
            Next wc
            
            Let lCtr = (lCtr + 1)
            DoEvents
        Loop Until ((bDone And (lCtr > 300)) Or (lCtr > 100000))
        
        Set wc = Nothing
        
        'Now that the connections have refreshed update the pivottables and header
        DoEvents
        DoEvents
        DoEvents
        DoEvents
        RefreshAllPivotTables
        
        UpdateHeader
        
        MsgBox "Data refresh complete...", vbOKOnly + vbInformation
    End Sub


    Sunday, September 28, 2014 2:31 AM
  • Hi George,

    Did you get a chance to look at the code below and update it to use OLEDB?

    Regards,

    JAG

    Wednesday, October 1, 2014 10:23 AM
  • Hi All,

    Can you please share the general OLEDB code to connect an excel worksheet with SQL database please.

    Wednesday, October 22, 2014 10:50 AM