none
excel auto refresh without password in connectionstring RRS feed

  • Question

  • An Excel file gets data into pivot table using a connection file form SQL2012 due to SQLOLEDB.
    The excel file must be auto refreshed, without opening it.
    For now I'm using an vbs script with 'xlBook.RefreshAll', but it asks me the DB password.
    This file must be delivered to a customer, so no passwords may be included in the connection string.
    Q: how to auto refresh an Excel file connection without giving the password. (without hardcoded passowrd) ?

    Tuesday, October 20, 2015 2:51 PM

Answers

  • I thought you wanted to do the opposite, but if you are fine keeping everything exposed, try the sample code below.

    Sub ADOExcelSQLServer()
         ' Carl SQL Server Connection
         '
         ' FOR THIS CODE TO WORK
         ' In VBE you need to go Tools References and check Microsoft Active X Data Objects 2.x library
         '
         
        Dim Cn As ADODB.Connection
        Dim Server_Name As String
        Dim Database_Name As String
        Dim User_ID As String
        Dim Password As String
        Dim SQLStr As String
        Dim rs As ADODB.Recordset
        Set rs = New ADODB.Recordset
         
        Server_Name = "SQL-SERVER\SQL" ' Enter your server name here
        Database_Name = "AdventureWorksLT2012" ' Enter your database name here
        User_ID = "" ' enter your user ID here
        Password = "" ' Enter your password here
        SQLStr = "SELECT * FROM [SalesLT].[Customer]" ' Enter your SQL here
         
        Set Cn = New ADODB.Connection
        Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
        ";Uid=" & User_ID & ";Pwd=" & Password & ";"
         
        rs.Open SQLStr, Cn, adOpenStatic
         ' Dump to spreadsheet
        With Worksheets("sheet1").Range("a1:z500") ' Enter your sheet name and range here
            .ClearContents
            .CopyFromRecordset rs
        End With
         '            Tidy up
        rs.Close
        Set rs = Nothing
        Cn.Close
        Set Cn = Nothing
    End Sub
    
    

    Of course you can reference other objects like a TextBox or a Cell on a specific Sheet.

        With Sheets("Sheet1")
               
                server = .TextBox1.Text
                table = .TextBox4.Text
                database = .TextBox5.Text
               
               
                If con.State <> 1 Then
           
                    con.Open "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";Integrated Security=SSPI;"
                    'con.Open
           
                End If
    
                'DO YOUR WORK HERE . . .
    
        End With


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Wednesday, October 21, 2015 1:40 PM

All replies

  • Hi B-Blase,

    >>Q: how to auto refresh an Excel file connection without giving the password. (without hardcoded passowrd) ?<<

    Based on my understanding, the SQL server provided two possible modes: Windows Authentication mode and mixed mode. If we choose the SQL Server Authentication, the SQL server always need us to provide the password to connect it.

    You can get more detail about SQL server authentication from link below:
    Choose an Authentication Mode

    To enable other user to connect the SQL server without prompt, I suggest that you using Windows Authentication mode.

    >>The excel file must be auto refreshed, without opening it.
    For now I'm using an vbs script with 'xlBook.RefreshAll', but it asks me the DB password.<<

    There is no way we can refresh the connection without opening the workbook. When we using Excel object model(xlBook.RefreshALL) to refresh the data, the workbook already opened before. For example, we need to get the xlBook(workbook instance) using Workbooks.Open method.

    I suggest that you check the "Refresh data when opening the file" checkbox to refresh the data when opening the workbook. Here is an helpful link for your reference:
    Refresh connected (imported) data

    Regards & Fei

     


    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.

    Wednesday, October 21, 2015 3:22 AM
    Moderator
  • The best way would (probably) be to request the user name and password in a message box.  If you store the password anywhere in Excel (in VBA code, in a Sheet/Cell and white it out, etc.) it can be detected relatively easily.  Of course any password protected VBA module can be cracked pretty easily.  Yeah, I think telling the user, and prompting him/her during each login process is the best way.  Think of how you login to this site, or how you login to your email account.

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Wednesday, October 21, 2015 3:23 AM
  • Hi, thanks for answare, But by it I see my Q was not clear.
    The Q is: I refresh an Excel file data connection (import data, pivot) using SQL Server Authentication.
    This file must go to a client without any password. But I have to save the password into Excel file so it can refresh automatically (by VB script), How can I refresh an Excel file and give it to a client without the password embedded?

    Wednesday, October 21, 2015 7:49 AM
  • I thought you wanted to do the opposite, but if you are fine keeping everything exposed, try the sample code below.

    Sub ADOExcelSQLServer()
         ' Carl SQL Server Connection
         '
         ' FOR THIS CODE TO WORK
         ' In VBE you need to go Tools References and check Microsoft Active X Data Objects 2.x library
         '
         
        Dim Cn As ADODB.Connection
        Dim Server_Name As String
        Dim Database_Name As String
        Dim User_ID As String
        Dim Password As String
        Dim SQLStr As String
        Dim rs As ADODB.Recordset
        Set rs = New ADODB.Recordset
         
        Server_Name = "SQL-SERVER\SQL" ' Enter your server name here
        Database_Name = "AdventureWorksLT2012" ' Enter your database name here
        User_ID = "" ' enter your user ID here
        Password = "" ' Enter your password here
        SQLStr = "SELECT * FROM [SalesLT].[Customer]" ' Enter your SQL here
         
        Set Cn = New ADODB.Connection
        Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
        ";Uid=" & User_ID & ";Pwd=" & Password & ";"
         
        rs.Open SQLStr, Cn, adOpenStatic
         ' Dump to spreadsheet
        With Worksheets("sheet1").Range("a1:z500") ' Enter your sheet name and range here
            .ClearContents
            .CopyFromRecordset rs
        End With
         '            Tidy up
        rs.Close
        Set rs = Nothing
        Cn.Close
        Set Cn = Nothing
    End Sub
    
    

    Of course you can reference other objects like a TextBox or a Cell on a specific Sheet.

        With Sheets("Sheet1")
               
                server = .TextBox1.Text
                table = .TextBox4.Text
                database = .TextBox5.Text
               
               
                If con.State <> 1 Then
           
                    con.Open "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";Integrated Security=SSPI;"
                    'con.Open
           
                End If
    
                'DO YOUR WORK HERE . . .
    
        End With


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Wednesday, October 21, 2015 1:40 PM