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
Formatting Preferences from VBA (Read 8224 times)
YaBB Newbies


Posts: 5
Formatting Preferences from VBA
21.02.08 at 20:54:54
Is there a way I can call the formatting preferences from my VBA app without going to the SQLXL Ribbon?
Back to top
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Re: Formatting Preferences from VBA
Reply #1 - 22.02.08 at 22:12:29
You can.
Use this call to open the formatting preferences sheet:
The formatting preferences are the only 2 preferences that are still kept in the sqlxl_settings.xls spreadsheet. Other settings have been moved into the windows registry. The formatting preferences however are basically cell formattings so it is more useful to keep these in the Excel sheet.
To modify them just use the usual Excel VBA syntax to modify or retrieve the settings options.
Getting a reference to the Column Heading Format setting use:
Dim cell as Range
Set cell = workbooks("sqlxl_settings.xls").Worksheets(1).Range("D4")

Getting a reference to the Column Body Format setting use:
Dim cell as Range
Set cell = workbooks("sqlxl_settings.xls").Worksheets(1).Range("D5")

The following macro will open the formatting preferences and get a reference to both settings. Insert your code where I left the comments. At the end any changes will be applied and the settings file is closed.
Note that this will flicker the screen. When you don't want your user to see a flickering screen use: Application.ScreenUpdating = False before the macro starts. Ensure you set it to = True again after you have finished. Excel will not update its screen when it is set to false and you won't be able to interact with Excel in that state!
Sub FormattingSettings()
    Dim cell As Range
    Set cell = Workbooks("sqlxl_settings.xls").Worksheets(1).Range("D4")
    'Do something with the Column Heading Format setting
    Set cell = Workbooks("sqlxl_settings.xls").Worksheets(1).Range("D5")
    'Do something with the Column Body Format setting
    Application.Run "SQLXL_Settings.xls!thisworkbook.Apply"
    Application.Run "SQLXL_Settings.xls!thisworkbook.OK"
End Sub

Back to top

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