Linker IT Software
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 the macro recorder helps coding SQL*XL VBA (Read 5983 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
How the macro recorder helps coding SQL*XL VBA
27.11.05 at 08:15:06
I frequently receive questions on how to automate certain tasks in which SQL*XL is used. The answer is most of the times: "use the Excel macro recorder to record your tasks and then tweak the code a little to suit your specific needs." In this post I want to tell you how to record a macro with Excel and how you can look at the code and make changes.
  • Start the macro recorder.
    • To start the macro recorder use the following Excel menus:
      Tools | Macro | Record New Macro
    • Excel presents a dialog window where you need to enter the name of the macro (default: Macro1), a shortcut key (optional), where to store the macro (default: ThisWorkbook) and a description (optional). I suggest you the default settings and just press OK.
    • Now you return to Excel and there is a stop button displayed in a floating toolbar.
    • Perform the actions in Excel that you want to record in the macro. If you use SQL*XL actions they will be recorded too in the macro.
    • When you are finished you can press the Stop button.

  • Inspect the code and make changes
    The Excel VBA code is displayed in the so called Visual Basic Editor.  
    • You can open the Visual Basic Editor (VBE) using the following Excel menu (Shortcut is Alt-F11): Tools | Macro | Visual Basic Editor
    • Choose your Visual Basic Project (.xls file) from the list of VBA projects in the top left list.
    • Open the modules list
    • Your macro will be recorded in Module1. Double click Module1 to show it in the editor.
    • Your code will look something like this:
      Sub Macro1()
      ' Macro1 Macro
      ' Macro recorded 27-11-2005 by Gerrit-Jan
          ActiveCell.FormulaR1C1 = "hello world"
      End Sub

Back to top
« Last Edit: 27.11.05 at 08:33:04 by Gerrit-Jan Linker »  

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