Export Document Variables from report

[xlbo]

Wrote this today to solve a major headache involving updating lots of reports that are slightly different but use the same base variables. The following code dumps a list of document variables with their formulae to a text file. The name and formula are seperated by a : so you can import the text file into excel and use : as the seperator and get a nice neat list of variable name and formula in seperate columns. It could also be sued to auto update variables in other reports as well (but that's a task for next week !). anyway, here's the code - hope it's helpful:

Sub ListVariables()
Dim MyVar As Variant, LogFile As String, LogFileInt As Integer

LogFile = "Full Path & Name of log file go here.txt"
LogFileInt = FreeFile
[COLOR=green]'prep text file[/color]
Open LogFile For Output As #LogFileInt
Print #LogFileInt, "Variable Log"
Print #LogFileInt, "------------"
Print #LogFileInt,

Print #LogFileInt, "Header:"
[COLOR=green]'loop through document variables[/color]
For Each MyVar In ThisDocument.DocumentVariables
    [COLOR=green]'check to see if it is user created[/color]
    If MyVar.IsDataProviderObject = False And UCase(Mid(MyVar.Formula, 2, 4)) <> "NAME" Then
        [COLOR=green]'Print to text file[/color]
        Print #LogFileInt, MyVar.Name & ":" & MyVar.Formula
    End If
Next MyVar

Print #LogFileInt,
End Sub

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[xlbo]

sure I posted this as a tip - nevermind - it's a tip not a question for anyone who is confused !! ;-)

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[blom0344]

Geoff,

Thanks for posting the code.

Similar examples of VBA code can be found here:

http://www.islink.com/isi_library_downloads_BO_vba.htm#LISTOBJ

Ties Blom
Information analyst
tbl@shimano-eu.com