trying to write a tab-delimited file
 

I have a straightforward spreadsheet holding 13 columns and 10-120 rows.
Using excel 2007.

I need to alter the values in a column and save the sheet to a tab-delimited
file with a unique filename. This process needs to be repeated for several
columns. To automate the process I open the file with a filenumber and using
'for' loops use print # to write the data. The problem I have is writing the
TAB into the file. I have tried all sorts of ways, ranging from the Tab
argument (which just puts spaces to next print zone) to trying to get the hex
character (0x09) to print.

Is the only way write a tab-delimited file to use the workbook.saveas method
with filetype = xltext?

The code I want to use is of the form:

For i = 1 To 8
For j = 1 To 11
vVal = myArray(i, j)
Print #FileNumber, RTrim(vVal);
Print #FileNumber, <<<TABcharacter
Next j
Print #FileNumber, Spc(1)
Next i

where TABcharacter is 0x09

Am I missing something obvious here??? I hope someone can help, its very
frustrating.

trying to write a tab-delimited file
 

I would start a new workbook and copy the range to that workbook (as values),
then use excel's File|SaveAs to save the text file as tab delimited.

Then I don't have to worry about adding any double quotes to fields that need
it.

But if you want, maybe something like:

Option Explicit
Sub testme()

Dim myArray(1 To 8, 1 To 11) As Long
Dim i As Long
Dim j As Long
Dim FileNumber As Long
Dim vVal As Long

For i = 1 To 8
For j = 1 To 11
myArray(i, j) = i * j
Next j
Next i

FileNumber = FreeFile
Close FileNumber
Open "C:\myfile.txt" For Output As FileNumber

For i = 1 To 8
For j = 1 To 11
vVal = myArray(i, j)
Print #FileNumber, RTrim(vVal); vbTab;
Next j
Print #FileNumber, ""
Next i

Close FileNumber

End Sub

Dave Salt wrote:

I have a straightforward spreadsheet holding 13 columns and 10-120 rows.
Using excel 2007.

I need to alter the values in a column and save the sheet to a tab-delimited
file with a unique filename. This process needs to be repeated for several
columns. To automate the process I open the file with a filenumber and using
'for' loops use print # to write the data. The problem I have is writing the
TAB into the file. I have tried all sorts of ways, ranging from the Tab
argument (which just puts spaces to next print zone) to trying to get the hex
character (0x09) to print.

Is the only way write a tab-delimited file to use the workbook.saveas method
with filetype = xltext?

The code I want to use is of the form:

For i = 1 To 8
For j = 1 To 11
vVal = myArray(i, j)
Print #FileNumber, RTrim(vVal);
Print #FileNumber, <<<TABcharacter
Next j
Print #FileNumber, Spc(1)
Next i

where TABcharacter is 0x09

Am I missing something obvious here??? I hope someone can help, its very
frustrating.

--

Dave Peterson