OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Macro - MailMerge issues

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
xenona
Newbie
Newbie


Joined: 05 Apr 2012
Posts: 1

PostPosted: Thu Apr 05, 2012 2:12 am    Post subject: Macro - MailMerge issues Reply with quote

Hello,

I am trying to merge odt+csv and i have one issue that is killing me. The problem is that the csv file name is dynamic. When i use a static name like "data.csv" it does work, tho when i change the names of the files(ODT name = csv name) it throws an SQL error. I am using GetFileName(ThisComponent.Title) to retrieve the names. I noticed one thing, if i add data fields manually they appear on the table in the Datasource browser, though when i apply the macro it just inserts the table, does not show any fields. Linking the code of a few macros that i think might have issues.

It throws an error at oRowSet.Execute() Syntax error in SQL expression.

'----------------------------------------------------------------
Private Sub RegisterDataSource(sDir as String, sDBName as String)
Dim oDBContext as Object, oDBSource as Object, oSources() as Object
Dim Args(6) as new com.sun.star.beans.PropertyValue
Dim sDirectory as String, i as Integer, sPath as String
Set oDBContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
oSources = oDBContext.getElementNames()
For i = 0 To Ubound(oSources)
If oSources(i) = sDBName Then
oDBContext.revokeObject(oSources(i))
End If
Next
oDBSource = oDBContext.createInstance()
args(0).Name = "FieldDelimiter"
args(0).Value = ","
args(1).Name = "StringDelimiter"
args(1).Value = """"
args(2).Name = "DBName"
args(2).Value = sDBName
args(3).name = "HeaderLine"
args(3).Value = True
args(4).Name = "DecimalDelimiter"
args(4).Value = "."
args(5).name = "ThousandDelimiter"
args(5).Value = ""
args(6).Name = "Extension"
args(6).Value = "csv"
oDBSource.Info = args
sDirectory = F_Replace(sDir, "\", "/")
oDBSource.URL = ConvertToURL("sdbc:flat:" & sDirectory & "/" & sDBName & ".csv")
sPath = sDirectory & "/" & sDBName & ".odb"
oDBSource.DatabaseDocument.storeAsUrl(ConvertToUrl(sPath), Array())
oDBContext.registerObject(sDBName, oDBSource)
End Sub

'----------------------------------------------------------------

'----------------------------------------------------------------
Private Sub DataToFields(oDBConnection as Object, sDBName as String, oDoc as Object, sTable as String)
Dim db as Object, oDBrow as Object, oEnum as Object, oThisField as Object
Dim oMaster as Object, sFieldName as String, sPresentation as String
Dim fields()
fields()= GetFieldNames(oDoc, sDBName, sTable)
oDBrow = SimpleQuery(oDBConnection,sDBName,fields(),sTable)
oEnum = oDoc.getTextFields.createEnumeration() 'Enumerate text fields.
While oEnum.hasMoreElements
oThisField = oEnum.nextElement
If oThisField.SupportsService("com.sun.star.text.TextField.Database") Then 'Its a DB field.
oMaster = oThisField.getTextFieldMaster() 'Get text field master.
If oMaster.DataBaseName = sDBname Then 'The field is from the current database.
sFieldName = oMaster.DataColumnName 'Get the field name.
If oDBrow.Columns.hasByName(sFieldName) Then 'Current DB row has this field name.
sPresentation = oDBrow.Columns.getByName(sFieldName).getString(1) 'Get desired presentation string.
oThisField.CurrentPresentation = sPresentation 'Set the presentation.
EndIf
Endif
EndIf
Wend
oDoc.Modified = True
End Sub
'----------------------------------------------------------------

'----------------------------------------------------------------
Private Function GetFieldNames(oDoc as Object, sDBName as String, sTable as String)
Dim fieldNames() as String, fields() as Object, oEnum as Object, oThisField as Object
Dim oMaster as Object, c as Integer
fields = oDoc.getTextFields()
oEnum = fields.createEnumeration()
c = 0
While oEnum.hasMoreElements
oThisField = oEnum.nextElement()
If oThisField.SupportsService("com.sun.star.text.TextField.Database") Then
oMaster = oThisField.getTextFieldMaster 'Get text field master.
If oMaster.DataBaseName = sDBname And _
oMaster.DataTableName = sTable Then 'The field is from the current database.
ReDim Preserve fieldNames(c)
fieldNames(c) = oThisField.TextFieldMaster.DataColumnName
c = c + 1
End If
End If
Wend
GetFieldNames = fieldNames()
End Function
Private Function GetFileName(sNameExt as String) as String
GetFileName = Left(sNameExt, InStr(sNameExt,".")-1)
End Function

'----------------------------------------------------------------
Function SimpleQuery(oConnection as Object, sDBName as String, fields() ,sTable)
Dim sSql as String, oRowSet as Object, oDBrow as Object, c as Integer
sSql = "SELECT "
For c = 0 to uBound(fields)
sSQL = sSql & """" & fields(c) & """"
If c <> uBound(fields) Then
sSql = sSql & ", "
Else
sSql = sSql & " FROM " & sTable

EndIf
Next
oRowSet = createUnoService("com.sun.star.sdb.RowSet")
oRowSet.activeConnection = oConnection
oRowSet.Command = sSql
oRowSet.execute()
oRowSet.First() 'Set oRowSet to the first record. Change to Last if you want the last one.
oDBrow = oRowSet.createResultSet()
SimpleQuery = oDBrow
End Function
'----------------------------------------------------------------
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group