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
VBA Macro Recorder Still not working (Read 10379 times)
Twildey
YaBB Newbies
*


I Love SQL*XL

Posts: 7
VBA Macro Recorder Still not working
07.11.07 at 19:34:35
 
I followed the insturctions on how to enable the macro recording in previous posts, but it is still not working and is driving me crazy!!!  What can I do?
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: VBA Macro Recorder Still not working
Reply #1 - 07.11.07 at 21:23:04
 
To provide a step by step instruction I have performed a test that you can easily repeat below. I tested on a newly installed Windows XP PC with only Office 2007 installed and SQL*XL 4.2.4. I once started Excel and entered a registration key for the SQL*XL software. After that I have done the steps below.
 

  • Start Excel 2007
  • Click the SQL*XL tab on the ribbon
    SQL*XL loads up
  • Press the SQL*XL program preferences button.
    The SQL*XL program preferences screen shows.
  • Select category General and preference "Macro recording support"  
  • Change the value from Disabled to Enabled
    Note that it is best to pick the value from the drop down. When you enter it manually ensure to type it with initial capital. The settings are case sensitive!
  • Press Apply and Close to make the change effective and close the preferences screen.
  • To record a macro you must show the Developer tab in Excel 2007. In previous versions you can use the menu Tools - Macro - Record.
    To show the developer tab you must click the Excel button and in the Excel options in the popular category under top options for Excel, tick the show developer tab in the ribbon tickbox.
  • Allow SQL*XL to write the macros in the VBA projects. Note that this is all taken care of in previous versions of Office. However in Office 2007 there is extra security...
    Go to the Excel options again. Go to the Trust Center and press the Trust Center Options button. Change the option from "Disable all macros without notification" to "Disable all macros except digitally signed macros". SQL*XL is digitally signed. Tick the box to "Trust access to the VBA project object model". SQL*XL will need this to write the code into the macro. Note that if you do not set these settings SQL*XL will complain not being able to record the macro.
    After making the settings close the options windows.
  • Click the developer tab
    The developer ribbon is showing
  • Press the record macro button
    The record macro dialog is showing. Type the name of your macro or use the default name Macro1. Leave the setting "Store macro in" to This Workbook. Without this setting SQL*XL will not record the macro!!!
  • Press OK to start the macro recording.
  • Press the SQL*XL tab
    The SQL*XL ribbon shows
  • Press the connect button. We will record connecting to a database and query some data.
  • Press New connection
    The New database connection window shows
  • Start the database connection wizard
    The database connection wizard shows
  • Select database type: Text/CSV and press the next button
    It asks for the directory to look for text and CSV files
  • Type: c:\ and press the Next button
    SQL*XL tests the database connections and displays the results.
  • Select a successful connection. I selected theOLEDB (Text) connection.
  • Press the Finish button and the OK button to return to the Connection dialog.
    The connection dialog now shows the new connection in the connection history.
  • Simply press the OK button to connect to the database leaving the password field blank (username shows as admin).
  • Click the developer tab
    The developer ribbon is showing
  • Press the stop macro recording button
    The macro recording stops
  • Press Alt-F11. This is a Excel keyboard shortcut to go directly to the VBA editor where the macro is recorded.
    The VBA editor shows
  • In the VBAProject (Boo1) open the modules node and double click the Module1 module.
  • Observe the code of the recorded macro.  
    It will show something like this:
    Code:
    Sub Macro1()
    '
    ' Macro1 Macro
    '
    
    '
      SQLXL.InitialiseSQLXL
      SQLXL.Database.ConnectionType = litSQLXLADO
      SQLXL.Database.Connect UserName:="Admin", Password:="", DBAlias:="", ConnectionString:="Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=c:\;Mode=Share Deny None;Extended Properties=""text;HDR=Yes;FMT=Delimited"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=96;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False", AllowTransactions:=True
    End Sub
     
    
    

     
    Please note that the very long connection string is not created by SQL*XL but it is merely what Micorsoft's ADO decided to create. It works...

 
Please note that the above list is deliberately long since I have written out every step in detail. Also note that once the security etc has been specified and the option is set in the preferences that you can just start recording macros. You do not need to set all of the options again.
 
Also note that it is Microsoft that has made this soo difficult. In previous verions of SQL*XL macro recording was switched on by default but since you get soo many warnings if the security settings are not correct I disabled it by default. This is not to say that macro recording does not work anymore. On the contrary. It works beautifully and I recommend everyone to use it!
 
Enjoy!
Back to top
 
« Last Edit: 07.11.07 at 22:07:55 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Twildey
YaBB Newbies
*


I Love SQL*XL

Posts: 7
Re: VBA Macro Recorder Still not working
Reply #2 - 09.11.07 at 16:14:07
 
Thanks very much.  It worked!!!!
Back to top
 
 
  IP Logged
Pages: 1