Macro - MailMerge issues

[xenona]

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
'----------------------------------------------------------------