Using data in a text or ini file in a Template as the data source

[cyril]

I am fairly new to creating Word Templates using VBA code and User forms but I have created a series of templates for my organisation and I have used the data for the authors, phone, email etc by coding for that directly in the template macro itself. It would be smarter if I used a single data source for this information such as a pipe delimiter text or ini file or even a table within a document/spreadsheet etc. I have been trying to research the best way to handle this in VBA templates so that the source data is held once but can be used over multiple templates.

Can anyone assist by getting me started on how best to tackle this process for the Microsoft Word Templates?

[Logit]

.
In WORD itself, you can choose to save a document as a TEMPLATE. Is there another design that you are speaking of ?

[cyril]

.
In WORD itself, you can choose to save a document as a TEMPLATE. Is there another design that you are speaking of ?

Yes I have set up templates but I have 5 and copied this code to insert the author details into a list box etc but I think it would be smarter if I kept the author details separate to the template by having the data in a source document such as a delimiter file (ie text or .ini) and then I only have to maintain the author details in that location if they change instead of having the code maintained separately in the 5 templates.

I have tried to research how to use data held in an external data source but get a lot of different processes and just want something that is easy to understand and put together.

[Paul_Hossler]

If I'm understanding you correctly

Not much error checking, and the INI would probably have to be on shared server

Two different ways - singlely and a deliminated string

The second might be the one that works best for you (not sure)

Option Explicit

Private Declare Function GetPrivateProfileString Lib "kernel32" _
    Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long
Private Declare Function WritePrivateProfileString Lib "kernel32" _
    Alias "WritePrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpString As Any, ByVal lpFileName As String) As Long


Public Const IniFileName As String = "C:\Users\Public\Documents\TemplateDefaults.ini"

Sub Demo()
    Dim v As Variant
    Dim i As Long
    IniFileString("Authors", "Author01") = "Tom"
    IniFileString("Authors", "Author02") = "Bill"
    IniFileString("Authors", "Author03") = "Mary"

    IniFileString("Departments", "Dept01") = "Sales"
    IniFileString("Departments", "Dept02") = "Production"
    IniFileString("Departments", "Dept03") = "Sourcing"

    IniFileString("Products", "Food") = "Hot Dog;Chili;Chips;Cookies"
    IniFileString("Products", "Drinks") = "Coke;Pepsi;Tab;Coffee;Tea"

    MsgBox IniFileString("Authors", "Author02")
    MsgBox IniFileString("Departments", "Dept03")

    v = Split(IniFileString("Products", "Food"), ";")
    For i = LBound(v) To UBound(v)
        MsgBox v(i)
    Next i

    v = Split(IniFileString("Products", "Drinks"), ";")
    For i = LBound(v) To UBound(v)
        MsgBox v(i)
    Next i
End Sub

Property Let IniFileString(ByVal Sect As String, ByVal Keyname As String, ByVal Wstr As String)
    Dim cntChar As Long
    cntChar = 0
  
    If Len(Sect) = 0 Or Len(Keyname) = 0 Then
        MsgBox "Section Or Key To Write Not Specified !!!", vbExclamation, "INI"
        Exit Property
        
    Else
        cntChar = WritePrivateProfileString(Sect, Keyname, Wstr, IniFileName)
  End If
End Property




Property Get IniFileString(ByVal Sect As String, ByVal Keyname As String) As String
    Dim cntChar As Long
    Dim RetStr As String * 128
    Dim StrSize As Long
    
    cntChar = 0
    IniFileString = vbNullString
    
    If Len(Sect) = 0 Or Len(Keyname) = 0 Then
        MsgBox "Section Or Key To Read Not Specified !!!", vbExclamation, "INI"
        Exit Property
    Else
        RetStr = Space(128)
        StrSize = Len(RetStr)
        
        cntChar = GetPrivateProfileString(Sect, Keyname, "", RetStr, StrSize, IniFileName)
    
        If cntChar > 0 Then IniFileString = Left$(RetStr, cntChar)
    
    End If
End Property

[cyril]

If I'm understanding you correctly

Not much error checking, and the INI would probably have to be on shared server

Two different ways - singlely and a deliminated string

The second might be the one that works best for you (not sure)

Option Explicit

Private Declare Function GetPrivateProfileString Lib "kernel32" _
    Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long
Private Declare Function WritePrivateProfileString Lib "kernel32" _
    Alias "WritePrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpString As Any, ByVal lpFileName As String) As Long


Public Const IniFileName As String = "C:\Users\Public\Documents\TemplateDefaults.ini"

Sub Demo()
    Dim v As Variant
    Dim i As Long
    IniFileString("Authors", "Author01") = "Tom"
    IniFileString("Authors", "Author02") = "Bill"
    IniFileString("Authors", "Author03") = "Mary"

    IniFileString("Departments", "Dept01") = "Sales"
    IniFileString("Departments", "Dept02") = "Production"
    IniFileString("Departments", "Dept03") = "Sourcing"

    IniFileString("Products", "Food") = "Hot Dog;Chili;Chips;Cookies"
    IniFileString("Products", "Drinks") = "Coke;Pepsi;Tab;Coffee;Tea"

    MsgBox IniFileString("Authors", "Author02")
    MsgBox IniFileString("Departments", "Dept03")

    v = Split(IniFileString("Products", "Food"), ";")
    For i = LBound(v) To UBound(v)
        MsgBox v(i)
    Next i

    v = Split(IniFileString("Products", "Drinks"), ";")
    For i = LBound(v) To UBound(v)
        MsgBox v(i)
    Next i
End Sub

Property Let IniFileString(ByVal Sect As String, ByVal Keyname As String, ByVal Wstr As String)
    Dim cntChar As Long
    cntChar = 0
  
    If Len(Sect) = 0 Or Len(Keyname) = 0 Then
        MsgBox "Section Or Key To Write Not Specified !!!", vbExclamation, "INI"
        Exit Property
        
    Else
        cntChar = WritePrivateProfileString(Sect, Keyname, Wstr, IniFileName)
  End If
End Property




Property Get IniFileString(ByVal Sect As String, ByVal Keyname As String) As String
    Dim cntChar As Long
    Dim RetStr As String * 128
    Dim StrSize As Long
    
    cntChar = 0
    IniFileString = vbNullString
    
    If Len(Sect) = 0 Or Len(Keyname) = 0 Then
        MsgBox "Section Or Key To Read Not Specified !!!", vbExclamation, "INI"
        Exit Property
    Else
        RetStr = Space(128)
        StrSize = Len(RetStr)
        
        cntChar = GetPrivateProfileString(Sect, Keyname, "", RetStr, StrSize, IniFileName)
    
        If cntChar > 0 Then IniFileString = Left$(RetStr, cntChar)
    
    End If
End Property

Thanks Paul I can see what you are doing there. That is very helpful. Thank You!!!