This topic tries
to answer the question, how SQL*XL can connect to the database without going through the connection dialog each time and
type the username and password.
The technique described here will let you record a macro. You can execute the
macro to connect to the database. At the end of this topic you will find the
information how to link the macro to a new toolbar button. That could be a
convenient way to connect to the database. There are other variants possible as
well. You could link it to a menu item, or make SQL*XL connect whenever you
start Excel.
Steps:
Start Excel
open a new workbook
start the macro recorder (Tools|Macro|Record New)
Name the macro: ConnectToDB and save it in the current workbook
use the SQL*XL Connection dialog to connect to the
database
stop the macro recorder
save the workbook as ConnectToDB.xls It is possible to open this .xls
at any time and go to Tools|Macros|Run and run the ConnectToDB macro.
To make it easier, you can make a toolbar button.
Go to View|Toolbars|Customize
From categories, select macros
Select the custom button item
Drag it anywhere in an existing toolbar (or you could
have created a new toolbar earlier for it) (Do not create
it in the SQL*XL menu or toolbar as these are recreated each time SQL*XL
starts. )
Leave the Customize dialog open and right click on
the new button
Give it a new name and button image (optional)
Right click and go to Assign Macro
Choose your ConnectToDB macro Now, when you start Excel the next time,
this new button is already visible. The interesting thing is that your
ConnectToDB.xls file is not yet open... When you click the button, Excel will
open the .xls and execute the macro. Executing the macro will make SQL*XL to
load into Excel and connect to the database. Could it be easier?
Variations on this theme are: - load the .xls at startup of Excel
- connect to the database whenever you start Excel - convert the macro
workbook into an addin
If you need information on any of these, please
let me know.