|< Day Day Up >|
This next example shows how to do the same thing without involving Microsoft Access. It will look similar to the Access version, though. Open Excel and get into a new Workbook. Rename Sheet1 to Connection Info by double-clicking on the Sheet Name tab, right-click on Sheet2, select Delete, and do the same for Sheet3.
Next, in cell A1 type username, and in cell A2 type password. For cell B1, go to Format Cells, go to the Protection tab, and uncheck the box for Locked. For cell B2 go to Format Cells, and on the Protection tab, uncheck the box for Locked, but check the box for Hidden. Still for Cell B2, go to the Font tab, and for Color select White (or whatever background color you are using on your worksheet). Go to Tools Macros Visual Basic Editor, or press Alt+F11. Right-click on the VBAProject of your workbook and select Insert Module. It defaults to Module1; select Insert Procedure, and enter the code in Example 7-4.
Example 7-4. Procedure to pull data from SQL Server into Excel
Public Sub OpenSQLWriteExcel( ) Dim adocn As ADODB.Connection Dim adoconnrs As ADODB.Recordset Dim adors As ADODB.Recordset Dim adofld As ADODB.Field Dim ConnString As String Dim xlwb As Excel.Workbook Dim xlws As Excel.Worksheet Dim xlrng As Excel.Range Dim x As Integer Dim UserName As String Dim Password As String UserName = Sheets("Connection Info").Range("B1").Value Password = Sheets("Connection Info").Range("B2").Value Set adoconnrs = New ADODB.Recordset adoconnrs.Open "C:\Documents and Settings\All Users\" & _ "Documents\SQLConn.XML" adoconnrs.MoveFirst While Not adoconnrs.EOF ConnString = ConnString & _ adoconnrs.Fields(0).Value & " = '" & adoconnrs.Fields(1).Value & "';" adoconnrs.MoveNext Wend adoconnrs.Close ConnString = ConnString & "User ID = '" & UserName & "';" ConnString = ConnString & "Password = '" & Password & "';" Set adocn = New ADODB.Connection adocn.ConnectionString = ConnString adocn.Open Set adors = New ADODB.Recordset adors.Open "pubs.dbo.Authors", adocn adors.MoveFirst Set xlwb = ActiveWorkbook Set xlws = xlwb.Worksheets.Add xlws.Name = "Data" x = 1 For Each adofld In adors.Fields xlws.Cells(1, x).Value = adofld.Name x = x + 1 Next adofld Set xlrng = xlws.Range("A2") xlrng.CopyFromRecordset adors xlws.Columns.AutoFit adors.Close adocn.Close Set xlrng = Nothing Set xlws = Nothing Set xlwb = Nothing Set adoconnrs = Nothing Set adocn = Nothing Set adors = Nothing End Sub
This doesn't use the Excel Application object, nor does it pass the username and password as parameters. Instead, that information comes from the worksheet. Keep in mind that if you put a username and password into the worksheet, you should take adequate precautions to protect it. One way to do this is to go to View Toolbars Visual Basic and click on the Toolbox button to bring up the list of controls that you can put on the worksheet. Next, click on the Command button and drag it onto the worksheet. Go to the properties of the button and make the caption Get SQL Data.
You also have to make the button work after the sheet is protected. Right-click on the button, go to Format Control, select the Protection tab, and uncheck the box for Locked. Next, right-click the button again and select View Code. Enter the following code:
Private Sub CommandButton1_Click( ) Call Module1.OpenSQLWriteExcel Sheets("Connection Info").Range("B2").Value = "" End Sub
This code calls the procedure that you put in Module1, and after it runs, it erases the password that you entered. The final step is to click on the button that says Exit Design Mode on the Visual Basic toolbar. Close both the Toolbox dialog and the Visual Basic dialog, click off of the button, and go to Tools Protection Protect Sheet. Enter a password and validate it on the next dialog box.
Once these steps are complete, the spreadsheet ensures that no one else can see the password that you typed. It is the same color as the background and is hidden in the formula bar. Notice that when you click the button it clears out the password after it runs. This ensures that you don't save the workbook with the password in it. The workbook should look similar to Figure 7-1, and after you type in your username and password and click the button, you should get a worksheet that looks like Figure 7-2.
Figure 7-1. The Excel worksheet used to run the code to pull the data from SQL Server and place it in a new worksheet
If you attempt to press the button again, you receive an error message saying that you cannot rename a worksheet the same name as an existing sheet. If you delete the data sheet and press the button again, you receive another error message because the code deletes the password each time it is called.
The last line of the XML file tells SQL Server to use integrated security, which means that if you have your SQL Server set up to allow you to use your Windows login instead of using SQL Server Security, it ignores the username and password that you entered. This is important because if you leave this line in and test the worksheet, you will not receive the errors noted above. I suggest leaving this line in if you are using integrated security because you can avoid having to put a place for username and password. However, if you want to force a user to put in her username and password, go into the XML file, delete the entire line that sets Integrated Security to SSPI, and save the file.
Figure 7-2. The resulting worksheet when you press the button
|< Day Day Up >|