trying to write a tab-delimited file

[Dave Salt]

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]

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]

Thanks very much Dave. I was sure there must be something somewhere like this
but trawling through the VBA Help,WEB and books did not point me to vBTab.
VBA Helps miscellaneous constants holds this and other usefull constants but
I would never have found it.

Ive tried the first method ("Saveas") but it creates a file that is about
94K and includes every row for a populated column, so many blank pages (with
Tabs).
The print method is far neater and more flexable.

Thanks again