Custom variables with Latex and Excel

I often have to write up engineering calculations for my work. Often I use LaTeX because of its excellent handling of equations, references, and custom variables. My strategy is to do the calculations in Excel, then create custom variables in a variables.tex file that contain all the values from my spreadsheet that will appear in the write up. I do this because if there is an error in my calculation, I can just change the value in one place and it will be corrected everywhere in the document where it appears.

Unfortunately, I sometimes have to deal with 50 or 100 values. Manually changing those even in only one place can quickly become time consuming, so I wrote the following Excel macro to automatically write the variables.tex file for me when I save the spreadsheet.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Const texFileName As String = "variables.tex"
Dim FileNum As Integer
Dim curLabel As String
Dim curValue As String
FileNum = FreeFile ' next file number
Open texFileName For Output As #FileNum ' creates the file if it doesn't exist
' Loop through rows and write the variable file line by line
For i = 1 To 100
Set curLabelCell = Worksheets("Sheet1").Cells(i, 1)
curLabel = curLabelCell.Value
Set curValueCell = Worksheets("Sheet1").Cells(i, 2)
curValue = curValueCell.Value

' write the information at the end of the text file
If curLabel <> "" Then
Print #FileNum, "\newcommand{\" + curLabel + "}{" + curValue + "}"
Print #FileNum,
End If

Next i
Close #FileNum ' close the file
End Sub

In my spreadsheet, the first column holds the variable name and the second column holds the value. It works beautifully and saves the variables.tex file in the same directory as the spreadsheet.
EDIT: I have ported the macro to the Calc module of OpenOffice.org


sub writeVariablesFile
rem ----------------------------------------------------------------------
rem define variables
dim oSheet, sLabel, sValue, iNum1, oCellL, oCellV
iNum1 = FreeFile
Open "variables.tex" for Output as #iNum1
oSheet = thisComponent.Sheets(0)
c = 0
For r = 0 to 100
oCellL = oSheet.getCellByPosition(c,r)
oCellV = oSheet.getCellByPosition(c+1,r)
sLabel = oCellL.String
sValue = oCellV.String
If sValue <> "" then
Print #iNum1,"\newcommand{\" + sLabel + "}{" + sValue + "}"
Endif

Next r
Close #iNum1
rem -------

end sub



Then, to associate that macro with the save event, I followed the instruction found here. Navigate to Tools->Customize->Events. Select the desired event. Click the macro button. Select the desired macro.