Macro to copy Base table to Calc

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER

Macro to copy Base table to Calc

Postby MiziaQ » Sat Nov 10, 2012 2:53 pm

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("com.sun.star.sdb.DatabaseContext")
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
  While ResultSet.next
 
           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

         
   Wend
End If

End Sub
OpenOffice 3.1 on Windows XP
MiziaQ
 
Posts: 2
Joined: Sat Nov 10, 2012 2:49 pm

Re: Macro to copy Base table to Calc

Postby Villeroy » Sat Nov 10, 2012 4:02 pm

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 com.sun.star.beans.PropertyValues
   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
      Endif
      oDesc(i) = oPrp
   Next
   oDBRange.getReferredCells.doImport(oDesc())
exit sub
exitErr:
'raise API-error
   error err
End Sub

It is by far easier because it uses the built-in import feature. The method takes a com.sun.star.sheet.DatabaseRange as import target (can be a single cell defined in Data>Define...), the registered source name, the com.sun.star.sheet.DataImportMode (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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27713
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to copy Base table to Calc

Postby MiziaQ » Mon Nov 12, 2012 6:42 pm

What would be the SourceType and SourceObject? Could you give an example? Thanks!
OpenOffice 3.1 on Windows XP
MiziaQ
 
Posts: 2
Joined: Sat Nov 10, 2012 2:49 pm

Re: Macro to copy Base table to Calc

Postby Villeroy » Mon Nov 12, 2012 7:53 pm

Source type is one of http://www.openoffice.org/api/docs/comm ... 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 com.sun.star.sheet.DataImportMode.TABLE and the source is "Table1".
You could also use com.sun.star.sheet.DataImportMode.SQL 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27713
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to External Data Sources

Who is online

Users browsing this forum: No registered users and 1 guest