Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Connect when opening the workbook (Read 1975 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Connect when opening the workbook
28.04.10 at 09:57:49
 
Connect when opening the workbook
 
Quote:
I think I've got it mostly figured out.  The only remaining challenge is
that when I open my workbook all of my cells show errors since I'm not
connected to the database.  I have to establish the connection, and then
click a cell on each row and hit enter to get it to refresh.  Since we have
about 500 rows of data, I'm wondering if I can make it easier for the user.

I did find one posting in your forum where someone asked about doing an
autologon to the database (launched by a macro button in the ribbon).  I
tried recording my own macro  but even though I connected to the DB
successfully, the macro showed blank.  I tried a few times with the same
results.  Is there a way I can hard code the username/password into the
connection string?  I know it's not advisable, but in our instance
functionality is more important than security.  

 
Yes, recording a macro is an easy way to do this. You don't specifically need a button. Let me explain.
 
First, you get errors in the cells because when you open your workbook and when you have not connected to the database yet, SQL*XL cannot retrieve the current values.  
 
To connect to the database automatically is very easy to automate. You don't need to compromise the security by hardcoding username and password.  
  • Ensure the macro recorder is enabled.  
    Open the SQL*XL program preference and switch the macro recorder on in the general options
  • Start the Excel macro recorder from the tools menu
  • Connect to the database as you do usually in SQL*XL
  • Stop the macro recorder
  • Inspect the code (Press Alt-F11 to jump to the VB editor and open module1 where usually the macro is stored)

 
Run the macro a few times to see that is works. Manually disconnect and then run the macro again to connect to the database.  
If you don't want your username and password to be hard coded just look for any instance of your username and remove it. You can write a few lines of code to prompt for the username and password. That may look like this:
 
Edited:
Note that in SQL*XL 5.2 the syntax changed slightly. The ConnectionType parameter became read only and instead the ConnectionType is passed as a parameter to the Connect routine. For further information see:
http://www.oraxcel.com/projects/sqlxl/help/vba/SQLXL/Database/index.html

 
Original macro:

Sub Macro1()
    SQLXL.InitialiseSQLXL
    SQLXL.Database.ConnectionType = litSQLXLADO
    SQLXL.Database.Connect UserName:="sa", PassWord:="12345", DBAlias:="master", ConnectionString:="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Data Source=VM-SQL2005XP\SQLEXPRESS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=VM-SQL2005XP;Use Encryption for Data=False;Tag with column collation when possible=False", AllowTransactions:=True
End Sub

 
Added prompts for username and password:

Sub Macro1()
    Dim strUser as String
    Dim strPasswd as String
 
    SQLXL.InitialiseSQLXL
    SQLXL.Database.ConnectionType = litSQLXLADO
 
    strUser = InputBox("Enter user name:", "User name")
    strPasswd = InputBox("Enter password:", "Password")
 
    SQLXL.Database.Connect UserName:=strUser, PassWord:=strPasswd, DBAlias:="master", ConnectionString:="Provider=SQLOLEDB.1;Persist Security Info=False;Data Source=VM-SQL2005XP\SQLEXPRESS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=VM-SQL2005XP;Use Encryption for Data=False;Tag with column collation when possible=False", AllowTransactions:=True
End Sub

 
Note that I also removed the username from the connection string.
 
To automate things further, you can make the code wake up when you open the workbook.
In the VB editor open the Thisworkbook module and type:

Private Sub Workbook_Open()
    Connect_Macro
End Sub

I have assumed here that you renamed your recorded macro to Connect_Macro.
 
Another tweak would be to see whether there is already a connection in place. You can use the technique:
 
if SQLXL.Database.Connected = False then
 ...add connect code here...
end if
 
Another thing you could do is ask whether the user wants to connect in the first place. Your code may look something like this:
 
if SQLXL.Database.Connected = False then
  if msgbox("You are not connected to the database. Do you want to connect now?", vbYesNo) = vbYes
   ...add connect code here...
  end if
end if
Back to top
 
« Last Edit: 30.07.14 at 13:52:57 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1