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
Auto connect and recalculate (Read 7889 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Auto connect and recalculate
02.10.09 at 09:56:20
 
Auto connect and recalculate
 
Quote:
Please specify HOW TO code the macro in the workbook to connect database and force calculation at launching of worksheet.

 
The best thing is not to code it yourself at all but let the macro recorder do the work. After recording the code, edit it if you want to to tailor it to your precise needs.
 
In Excel, start the macro recorder. Perform your actions. Login to the database. Recalculate your formulas. Then stop the recorder. It will have generated all the necessary code for you. If it has not recorded any SQL*XL actions it may be that you need to turn the macro recorder on in SQL*XL preferences. See the general section.
 
See also:
  How the macro recorder helps coding SQL*XL VBA
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1133075706
 Automatically connect to a database - Connect SQLOLEDB for SQLSERVER2000 in vb script  
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1213471017
 SQL*XL API Visual Basic for Applications Help
http://www.oraxcel.com/projects/sqlxl/help/vba/index.html
Back to top
 
« Last Edit: 02.10.09 at 10:00:29 by Gerrit-Jan Linker »  

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




Posts: 75
Connect and recalculate: VBA code
Reply #1 - 29.07.11 at 09:48:43
 
Connect and recalculate: VBA code
 
Recording a connect and recalculate macro results in the following code:
 
Edited:
Note that in SQL*XL 5.2 the syntax changed slightly. The ConnectionType parameter became read only and instead the ConnectionType is passed as a parameter to the Connect routine. For further information see:
http://www.oraxcel.com/projects/sqlxl/help/vba/SQLXL/Database/index.html

 
Code:
Sub Connect_Recalc()
  SQLXL.InitialiseSQLXL
  SQLXL.Database.ConnectionType = litSQLXLADO
  SQLXL.Database.Connect UserName:="Admin", PassWord:="", DBAlias:="BIBLIO.MDB (c:\BIBLIO.MDB)", ConnectionString:="Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=c:\BIBLIO.MDB;Mode=Share Deny None;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=4;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
  SQLXL.mnuCalculateAllFunctions_Click
End Sub
 


 
The large connection string doesn't look too tidy. You can edit the macro code of course:
 
Sub Connect_Recalc()
  Dim strDbAlias as String
  Dim strConn as String
 
  strDbAlias = "BIBLIO.MDB (c:\BIBLIO.MDB)"
  strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=c:\BIBLIO.MDB;" & _
                 "Mode=Share Deny None;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:" & _
                 "Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=4;" & _
                 "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"
 
  SQLXL.InitialiseSQLXL
  SQLXL.Database.ConnectionType = litSQLXLADO
  SQLXL.Database.Connect UserName:="Admin", PassWord:="", DBAlias:=strDbAlias, _
                                      ConnectionString:=strConn, AllowTransactions:=True
  SQLXL.mnuCalculateAllFunctions_Click
End Sub

 
Whether all the options in the connection string are necessary I don't know. Probably not. Howver the macro recorder recorded it and precisely this string was used for the connection when I recorded it.
 
To make this code run automatically when you open a workbook, add a call to it from Workbook_Open in ThisWorkbook object of your XLS file.
 
Sub Workbook_Open
  Connect_Recalc
End Sub
Back to top
 
« Last Edit: 30.07.14 at 13:50:37 by Gerrit-Jan Linker »  

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