Page 1 of 1

Macro to copy Base table to Calc

PostPosted: Sat Nov 10, 2012 2:53 pm
by MiziaQ
Hi Everyone,

I am using the following macro to copy my OOBase table to Calc. However, only the last entry shows up in the spreadsheet. Could you please help me fix the code so that all 90+ entries are copied to Calc? Many thanks in advance!

Code: Select all   Expand viewCollapse view
Sub drukuj_spis
Dim DatabaseContext as Object
Dim Datasource As Object
Dim Connecticus As Object
Dim Stm as Object
Dim Resultset as object

'Establish connection to Cities DB
DatabaseContext = createUnoService("")
Datasource = DatabaseContext.getByName("Kartoteki")
Connection = Datasource.GetConnection("","")

'Perform query
Stm = Connection.createStatement()
Resultset = Stm.executeQuery("SELECT * FROM Table1")

Dim sheet as Object
Dim cell as Object
Dim A as Integer
sheet = thisComponent.currentSelection.getSpreadsheet()

If Not IsNull(ResultSet) Then
  A = 0
           cell = sheet.getCellByPosition( 0, 1 )
           cell.String = ResultSet.GetString(1)

           cell = sheet.getCellByPosition( 1, 1 )
           cell.String = ResultSet.GetString(2)
           cell = sheet.getCellByPosition( 2, 1 )
           cell.String = ResultSet.GetString(3)
           cell = sheet.getCellByPosition( 3, 1 )
           cell.String = ResultSet.GetString(4)
           cell = sheet.getCellByPosition( 4, 1 )
           cell.String = ResultSet.GetString(5)
         cell = sheet.getCellByPosition( 5, 1 )
           cell.String = ResultSet.GetString(6)
           cell = sheet.getCellByPosition( 6, 1 )
           cell.String = ResultSet.GetString(7)
           cell = sheet.getCellByPosition( 7, 1 )
           cell.String = ResultSet.GetString(8)
           cell = sheet.getCellByPosition( 8, 1 )
           cell.String = ResultSet.GetString(9)
           cell = sheet.getCellByPosition( 9, 1 )
           cell.String = ResultSet.GetString(10)

         cell = sheet.getCellByPosition( 10, 1 )
           cell.String = ResultSet.GetString(11)
         A = A + 1

End If

End Sub

Re: Macro to copy Base table to Calc

PostPosted: Sat Nov 10, 2012 4:02 pm
by Villeroy
Try this:
Code: Select all   Expand viewCollapse view
Sub importRowSet(oDBRange, dbSourceName$, srcType%, src$ )
Dim oDesc(),i%,oPrp
'on error goto exitErr:
   oDesc() = oDBRange.getImportDescriptor() 'array of
   For i = 0 to ubound(oDesc())
      oPrp = oDesc(i)
      If oPrp.Name = "DatabaseName" then
         oPrp.Value = dbSourceName
      elseIf oPrp.Name = "SourceType" then
         oPrp.Value = srcType
      elseIf oPrp.Name = "SourceObject" then
         oPrp.Value = src
      oDesc(i) = oPrp
exit sub
'raise API-error
   error err
End Sub

It is by far easier because it uses the built-in import feature. The method takes a as import target (can be a single cell defined in Data>Define...), the registered source name, the (table, query or sql), the name of the table, query or the whole sql string.
The doImport method automatically resizes the given database range and all references (formulas, charts and more) according to the size of the imported record set.
Once the import is done, a simple oDBRange.refresh() can refresh the imported data.

Re: Macro to copy Base table to Calc

PostPosted: Mon Nov 12, 2012 6:42 pm
by MiziaQ
What would be the SourceType and SourceObject? Could you give an example? Thanks!

Re: Macro to copy Base table to Calc

PostPosted: Mon Nov 12, 2012 7:53 pm
by Villeroy
Source type is one of ... tMode.html (table, query or SQL SELECT statement).
Depending on the source type, the source is the name of a table, the name of a query or a full SELECT statement.
In your case the type is and the source is "Table1".
You could also use and SELECT * FROM "Table1"
Or you could store your SELECTion as "Query1" in your database and use DataImportMode.QUERY with "Query1"

Without a single line of code, you hit F4 and drag the icon of your your table or query into the sheet.