Apache OpenOffice Community Forum

Posted: **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

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

Posted: **Sat Nov 10, 2012 4:02 pm**

Try this:

Code: Select all

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.

Posted: **Mon Nov 12, 2012 6:42 pm**

What would be the SourceType and SourceObject? Could you give an example? Thanks!

Posted: **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.

Apache OpenOffice Community Forum

Macro to copy Base table to Calc

Macro to copy Base table to Calc

Re: Macro to copy Base table to Calc

Re: Macro to copy Base table to Calc

Re: Macro to copy Base table to Calc