Excel VBA – Get Data Record from SQL Server by using "Microsoft ActiveX Data Objects 6.1 Library"

Excel VBA – Get Data Record from SQL Server by using "Microsoft ActiveX Data Objects 6.1 Library"

Add "Microsoft ActiveX Data Objects 6.1 Library" Reference from the Excel File.      The Code is defined from the Open Excel File – Event Handler.    The Data Record on the SpreadSheet would be refreshed from the Database Table when Excel is opened.    The Practice is as similar as Excel Pivot Table Feature.    The File is the Tools for Reading the Record from SQL Server – Database Table.    It is not recommend to define the self value from that SpreadSheet Cell. The Value may be overwritten by Refreshing Value.    If the File is connected to Production Database,    the Fixed Logic and Password of Production Database Server would also be kept from the Excel File VBA Code – Connection String.      Private Sub Workbook_Open()        Cells(1, 1).Value = "Company ID"      Cells(1, 2).Value = "Company Name ( Eng )"      Cells(1, 3).Value = "Company Name ( Chn )"      Cells(1, 4).Value = "Company Number"      Cells(1, 5).Value = "BR Number"        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 = "1.2.3.4,12345"      Database_Name = "Company"      User_ID = "… …"      Password = "… …"      SQLStr = "SELECT [COMPANY_ID], [ENG_NAME], [CHN_NAME], [COMPANY_NUM], [BR_NUM] FROM [dbo].[COMPANY_TBL]"        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        With Worksheets("Sheet").Range("a2:z5000")        .ClearContents        .CopyFromRecordset rs      End With        Worksheets("Sheet").Columns("A:G").AutoFit        rs.Close      Set rs = Nothing      Cn.Close      Set Cn = Nothing      End Sub