VBA Help...Almost There

Hey VBA gurus,

I have a quick VBA need and was wondering if you could help. Please read on from an earlier post to see what I'm shooting for. I'm very grateful to Kent Keller for submitting the code below. With what Kent has proposed here my next question is how can I format the .tit file to match the correct syntax for the .tit file. This is in reference to MDT 2005. Here is the post from earlier.

"For all of you VBA fluent users, is there anyone that could help me develop a simple program to do the following. From an excel worksheet I would like to take a column of attributes with a separate column of values for the attributes and write them into a .tit file (text file for importing into title blocks) with the appropriate syntax and format. I would think this to be an easy fix but for those VBA impaired I feel helpless."

=======Code from Kent=======

Public Sub MaketitFile()

On Error GoTo CloseFile:

Open "TestFile.tit" For Output As #1

Dim oXLUsed As Range
Set oXLUsed = Sheet1.UsedRange

Dim iMaxRow As Integer
iMaxRow = Sheet1.Rows(oXLUsed.Rows.Count).Row

Dim i As Integer
For i = 1 To iMaxRow
Write #1, Sheet1.Cells(i, 1).Value
Write #1, Sheet1.Cells(i, 2).Value
Next


CloseFile:
Close #1

End Sub

 

1) What is the correct syntax for the .tit file?
2) The text you want print to the text file (I use the Print
command -- it writes each data line as a line. The Write
command writes sequential data. Are you sure you want
sequential data? ) is the usually the .Text of the cell and not
the .Value. So your line might go:

Print #1 Sheet1.Cells(i,1).Text

Use the Format function to make the text look like what you
want as in:

Print #1 Format(Sheet1.Cells(i,1).Text, <format string>)

The listing you show would appear to make a text file that
has each data pair written out as one continuous stream
with nothing to distinguish where each value ended and the next value started. Surely that is not the correct format
that you are looking for.

 

The syntax is of the form

string
string
("attribute" "attribute value")
...

Were "string" equals a value that must be present in the .tit file independent of the excel spreadsheet, it is constant. The value "attribute" is the label for data collected in the first spreadsheet column and "attribute value" is a label for data collected in the second spreadsheet column. The quotations and parenthesis are required syntax. Each row in the spreadsheet would equal each line in the .tit file. How do I incorporate the quotations, parenthesis, and string values into the .tit file upon executing the vba script? The following is an example of a valid .tit file.

1:1 ! Required string
6312_d_border ! Required string
("P_N" "40001234")
("NOM" "CASTING, TOP, FRAME,")
("REV" "-")
("DRAWN_BY" "RBH")
("TODAYS_DATE" "2004-12-02")

 

Ok, those quotes make it touchy. Each quote that you want
printed must be nested between quotes.

Public Sub MaketitFile()
On Error GoTo CloseFile:
Open "TestFile.tit" For Output As #1
Dim oXLUsed As Range
Set oXLUsed = Sheet1.UsedRange
Dim iMaxRow As Integer
iMaxRow = Sheet1.Rows(oXLUsed.Rows.Count).Row

Dim strAttNum As String
Dim strAttVal As String
Dim strLine As String

Dim i As Integer
For i = 1 To iMaxRow
strAttNum Sheet1.Cells(i, 1).Text
strAttVal Sheet1.Cells(i, 2).Text
strLine = "(""" & strAttNum & """ """ & strAttVal & """)"
'MsgBox (strLine)
Print #1, strLine
Next
CloseFile:
Close #1
End Sub