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
How to prevent running the macros (Read 2779 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
How to prevent running the macros
31.10.05 at 10:11:54
 
A customer asked this question today:
 
Question:
I have a workbook with macros. When I open the workbook these macros execute automatically.  
Now, when I am not connected to the database, the macros will fail. How can I prevent the macros from running when I am not connected to the database?
 
Answer:
Your code probably looks something like this:
 
Code:
Public Sub Workbook_Open
    MyMacro
End Sub
 


 
There are several ways you can solve the problem that SQL*XL gives errors when you are not connected to the database.
 

  • You can program a message box to ask whether the macros should be executed:
     
    Code:
    Public Sub Workbook_Open
        if Msgbox("Do you want the macros to run?", vbYesNo) = vbYes then
    	  MyMacro
        end if
    End Sub
     
    
    

  • Probably a better solution is to test for the database connection. You can decide to only run the code when the user is connected to the database.
    Code:
    Public Sub Workbook_Open
        if SQLXL.Database.Connected = True then
    	  MyMacro
        end if
    End Sub
     
    
    

  • Again a better solution would be to add a macro to connect to the database. Record a macro when you connect to the database and name it MyConnection. You can add a call to this routine to the macro. If you do not wish the password to be hard coded you can change the macro to take the password a parameter. The following code prompts for the password before connecting to the database.
     
    Code:
    Public Sub Workbook_Open
        Dim strPassword as String
        strPassword = InputBox("Enter the password")
        If Not strPassword = vbNullString then
    	  MyConnect strPassword
            if SQLXL.Database.Connected = True then
    		MyMacro
    	  end if
        end if
    End Sub
     
    
    

     
Back to top
 
« Last Edit: 31.10.05 at 10:21:03 by Gerrit-Jan Linker »  

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