Re: [libreoffice-users] Re: Batch for extracting lines into new files in CALC

On 01/18/2012 03:08 PM, Andreas Säger wrote:
> Am 18.01.2012 18:13, Andreas Säger wrote:
>
> > Connect a registered Base document to the spreadsheet. A connection to
> > an indexed dBase copy might be more performant.
> > Create a parameter query: SELECT * FROM "Table" WHERE "Column"= :p
>
> Use the parameter query for testing if you get the wanted records for 
> a given number :p.
> Store the following Basic code somewhere in the global library 
> container (aka "My Macros").
> Modify the 6 constants on top of the code. Registered name of the 
> database, table name, column name, first ID to substitute, last ID to 
> substitute and the target path.
> The code works without further modifications but with any type of 
> database such as address books, csv, spreadsheets, dBase, MySQL, 
> HSQLDB, MS Access, MS servers, Oracle servers, anything connectable 
> with a Base document.
>
> > Sub Import2xls
> > Const cDataSource = "Chargen"
> > Const cTableName = "MAT"
> > Const cColumnName ="ID"
> > Const cStartID = 405
> > Const cEndID = 407
> > Const cPath = "/tmp/"
> >
> > Dim addr as new com.sun.star.table.CellRangeAddress
> > sQuery = "SELECT * FROM """& cTableName &"""WHERE """& cColumnName 
> > &"""="
> > ' print squery
> > Dim xlsprop as new com.sun.star.beans.PropertyValue
> > xlsprop.Name = "FilterName"
> > xlsprop.Value = "MS Excel 97"
> >
> > for i = cStartID to cEndID
> >     doc = 
> > StarDesktop.loadComponentFromURL("private:factory/scalc","_blank", 0, 
> > Array() )
> >     dbx = doc.DatabaseRanges
> >     dbx.addNewByName("Import2xls", addr)
> >     dbr = dbx.getByName("Import2xls")
> >     dsc() = dbr.getImportDescriptor()
> >     for each prop in dsc()
> >         if prop.Name = "SourceType" then prop.Value = 
> > com.sun.star.sheet.DataImportMode.SQL
> >         if prop.Name = "SourceObject" then prop.Value = sQuery & i
> >         if prop.Name = "DatabaseName" then prop.Value = cDataSource
> >     next
> >     dbr.ReferredCells.doImport(dsc())
> >     doc.storeAsURL(convertToURL(cPath & format(i, 
> > String(len(cEndID),"0")) &".xls"), Array(xlsprop))
> >     doc.close(True)
> > next
> > End Sub
Nice. Thank you for the tip. I shall remember it for next round.
Keep the good work.

--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted