customit
03-19-2010, 05:21 AM
We are upgrading from Act 6 to Act 2010, the old Act used word templates and seached through the document to find anything with [[word]] and replaced that with the data in the field... simple... however the new version uses mail merge fields, and the conversion process provided by Sage Act 2010 within the program fails stating the documents are corrupt (rubbish!!!). we have about 300 templates each with atleast 100 fields in... so not an easy task to just recreate and manually replace each field with the new mailmerge type in word...
this is where i have had a little experiance with VBA and knowing i can connect the to sql database have written a little script to find the old fieldname within [[]] and replace it the the new mailmerge field... this is the theory...
1. word seaches document extracts word(s) between [[]]
2. connects to sql act database, go to field table and finds field name word found from 1.
3. cross reference this to another column in the same sql table to find a value.
4. cross reference the above value in another table to get the new mailmerge field name
5. uses all the above values to create the mailmerge field and replace what word found in 1. with the new field.
6. move on to next [[]] find
below is the vba script so far.... but i cant get it to get the information from sql, i think it connects ok, but i dont really understand what to do next... i have searched around but i dont think im using the correct statement with the SELECT command. anyway the script is what i have writen so far but i need some expert help!!!!
Sub Convert_V6_to_V12()
'
' This will convert act v6 data fields into sql mail merge fields
' Providing when the database was updated the field names are the same
'
Dim rplOLD As String
Dim rplNEW As String
Dim strSQLFINDFIELD As String
Dim strSQLFINDTBL As String
Dim strSQLTBLID As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim rplMM As String
Dim bFound As Boolean
Dim objWdRange As Range
Set objWdRange = ActiveDocument.Content
Dim oCon As ADODB.Connection
Dim qdfCurr As DAO.QueryDef
'
'Connect to Act 2010 SQL Database
'
Set oCon = New ADODB.Connection
oCon.ConnectionString = "DRIVER={SQL Native Client};SERVER=Localhost\ACT7;UID=ACTREADER;PWD=password123;"
oCon.Open
' Find Field Name between [[ ]]
With objWdRange.FIND
.ClearFormatting
.Replacement.ClearFormatting
.MatchWildcards = True
.Text = "(\[\[)(*)(\]\])"
.Replacement.Text = "\2"
bFound = .Execute(Replace:=wdReplaceOne)
If bFound Then
rplOLD = objWdRange.Text
MsgBox rplOLD
' Connect to SQL Act Database
Set qdfCurr = CurrentDb.QueryDefs("TempQuery")
If Err.Number = 3265 Then ' 3265 is "Item not found in this collection."
Set qdfCurr = CurrentDb.CreateQueryDef("TempQuery")
End If
' Use the found Act v6 Field Name to find Act v12 SQL Field Name
strSQLFINDFIELD = "SELECT TBL_SYSCOLUMN.COLUMNNAME FROM TBL_SYSCOLUMN WHERE TBL_SYSCOLUMN.DISPLAYNAME=""" & rplOLD & """;"
qdfCurr.SQL = strSQLFINDFIELD
DoCmd.OpenQuery "TempQuery"
' Use the found Act v12 SQL Field Name to find Matching Act v12 SQL Table ID
strSQLFINDTBL = "SELECT TBL_SYSCOLUMN.TABLEID FROM Blueforce WHERE TBL_SYSCOLUMN.COLUMNNAME=""" & strSQLFINDFIELD & """;"
' Use the found Act v12 SQL Table ID to find Act v12 SQL Table Name
strSQLTBLID = "SELECT TBL_SYSTABLE.TABLENAME FROM Blueforce WHERE TBL_SYSTABLE.TABLEID=" & strSQLFINDTBL & ";"
' Run the SQL statements to create Variables to use later...
DoCmd.RunSQL strSQLFINDFIELD
DoCmd.RunSQL strSQLFINDTBL
DoCmd.RunSQL strSQLTBLID
' Combine SQL statement variables to create new SQL Mail Merge Field
rplNEW = strSQLTBLID & "." & strSQLFINDFIELD
' Create Word Mail Merge Field
strSQL1 = "Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _ "
strSQL2 = """DOCVARIABLE [ACTFIELD]&rplNEW& """
strSQL3 = " _, PreserveFormatting:=True"
rplMM = strSQL + strSQL2 + strSQL3
' Replace the found Act v6 Field with new Act v12 SQL Mail Merge Field
objWdRange.Text = rplMM
oCon.Close
End If
End With
End Sub
this is where i have had a little experiance with VBA and knowing i can connect the to sql database have written a little script to find the old fieldname within [[]] and replace it the the new mailmerge field... this is the theory...
1. word seaches document extracts word(s) between [[]]
2. connects to sql act database, go to field table and finds field name word found from 1.
3. cross reference this to another column in the same sql table to find a value.
4. cross reference the above value in another table to get the new mailmerge field name
5. uses all the above values to create the mailmerge field and replace what word found in 1. with the new field.
6. move on to next [[]] find
below is the vba script so far.... but i cant get it to get the information from sql, i think it connects ok, but i dont really understand what to do next... i have searched around but i dont think im using the correct statement with the SELECT command. anyway the script is what i have writen so far but i need some expert help!!!!
Sub Convert_V6_to_V12()
'
' This will convert act v6 data fields into sql mail merge fields
' Providing when the database was updated the field names are the same
'
Dim rplOLD As String
Dim rplNEW As String
Dim strSQLFINDFIELD As String
Dim strSQLFINDTBL As String
Dim strSQLTBLID As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim rplMM As String
Dim bFound As Boolean
Dim objWdRange As Range
Set objWdRange = ActiveDocument.Content
Dim oCon As ADODB.Connection
Dim qdfCurr As DAO.QueryDef
'
'Connect to Act 2010 SQL Database
'
Set oCon = New ADODB.Connection
oCon.ConnectionString = "DRIVER={SQL Native Client};SERVER=Localhost\ACT7;UID=ACTREADER;PWD=password123;"
oCon.Open
' Find Field Name between [[ ]]
With objWdRange.FIND
.ClearFormatting
.Replacement.ClearFormatting
.MatchWildcards = True
.Text = "(\[\[)(*)(\]\])"
.Replacement.Text = "\2"
bFound = .Execute(Replace:=wdReplaceOne)
If bFound Then
rplOLD = objWdRange.Text
MsgBox rplOLD
' Connect to SQL Act Database
Set qdfCurr = CurrentDb.QueryDefs("TempQuery")
If Err.Number = 3265 Then ' 3265 is "Item not found in this collection."
Set qdfCurr = CurrentDb.CreateQueryDef("TempQuery")
End If
' Use the found Act v6 Field Name to find Act v12 SQL Field Name
strSQLFINDFIELD = "SELECT TBL_SYSCOLUMN.COLUMNNAME FROM TBL_SYSCOLUMN WHERE TBL_SYSCOLUMN.DISPLAYNAME=""" & rplOLD & """;"
qdfCurr.SQL = strSQLFINDFIELD
DoCmd.OpenQuery "TempQuery"
' Use the found Act v12 SQL Field Name to find Matching Act v12 SQL Table ID
strSQLFINDTBL = "SELECT TBL_SYSCOLUMN.TABLEID FROM Blueforce WHERE TBL_SYSCOLUMN.COLUMNNAME=""" & strSQLFINDFIELD & """;"
' Use the found Act v12 SQL Table ID to find Act v12 SQL Table Name
strSQLTBLID = "SELECT TBL_SYSTABLE.TABLENAME FROM Blueforce WHERE TBL_SYSTABLE.TABLEID=" & strSQLFINDTBL & ";"
' Run the SQL statements to create Variables to use later...
DoCmd.RunSQL strSQLFINDFIELD
DoCmd.RunSQL strSQLFINDTBL
DoCmd.RunSQL strSQLTBLID
' Combine SQL statement variables to create new SQL Mail Merge Field
rplNEW = strSQLTBLID & "." & strSQLFINDFIELD
' Create Word Mail Merge Field
strSQL1 = "Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _ "
strSQL2 = """DOCVARIABLE [ACTFIELD]&rplNEW& """
strSQL3 = " _, PreserveFormatting:=True"
rplMM = strSQL + strSQL2 + strSQL3
' Replace the found Act v6 Field with new Act v12 SQL Mail Merge Field
objWdRange.Text = rplMM
oCon.Close
End If
End With
End Sub