Re: [users] Cut and Paste Cell Ranges from Calc to a Database

[Andrew Douglas Pitonyak]

Ludovic Coum�tou (aka Coume) wrote:
Andrew,
Any chance to have a look at the macro to do that? Or was it just a
teaser?
Thanks in advance
Ludovic
 

Here is how to read data from a Calc document. I assume that the first 
column is an integer and that the second and third columns are text 
data. Things are a bit trickier for dates, which use a special format.

Sub CalcAsDB()
 Dim s As String  'Temporary string.
 Dim sFileName$   'File name of the Calc document.
 Dim sURLCalc$    'URL of the Calc document.
 Dim oManager     'Connection driver manager.
 Dim oCon         'Connection object.
 Dim sSQL$        'SQL that is executed.
 Dim oResult      'Result from an SQL statement.
 Dim oStatement   'A created statement that can execute SQL.
 sFileName = "test_data"
 sURLCalc = "file:///andrew0/home/andy/" & sFileName & ".ods"
 oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
 oCon = oManager.getConnection("sdbc:calc:" & sURLCalc)
 oStatement = oCon.CreateStatement()
 sSQL = "SELECT * FROM """ & sFileName & """"
 oResult = oStatement.executeQuery(sSQL)
 s = s & CHR$(10)
 Do While oResult.next()
   s = s & "ID = " & CStr(oResult.getLong(1)) & _
           " Name = '" & oResult.getString(2) & "'" & _
           " Text = " & oResult.getString(3) & CHR$(10)
 Loop
 oCon.close()
 MsgBox s, 0, "Data from the Calc file"
End Sub
Connecting to your ODBC database will likely be simple as well. I have 
not made a connection using ODBC. Do you require a password? You could 
use something like the following:

 sUser$ = "user"
 sPass$ = "password"
 sURL$  = "sdbc:odbc:data_source_name_here"
 oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
 AppendProperty(oParms(), "user", sUser)
 AppendProperty(oParms(), "password", sPass)
 oCon = oManager.getConnectionWithInfo(sURL, oParms())
Are you able to connect to the ODBC SQL database?
--
Andrew Pitonyak
My Macro Document: http://www.pitonyak.org/AndrewMacro.sxw
My Macro Book: http://www.hentzenwerke.com/catalog/oome.htm
Free Info:  http://www.pitonyak.org/oo.php
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]