How to tell spaces between values in saved text file from theoriginal xls file

[peng.gong]

Hi,

I want to save an excel xls file as the format of "formatted text
(space delimited)".
My question is given the excel file (from which, i know the width of
each column, and the value of each range), how do I know the number of
spaces between any two neighboring values in the saved text file? (I
want to be very specific about that number) For example, if in the
original excel file, range("A1") = "0.123" and range("B1") = "abc",
what would be the number of spaces between the "0.123" and "abc" in
the text file?

For column width I can get in vba (columns(1).width), but I guess the
number of spaces apparently also depends on the width of the specific
content within a cell. For example, for a cell with the content
"0.123" or "abc", the width of the content could be (the width of a
single digit * 4 + the width of a decimal point) or (the width of a
letter * 3). It should also depend on the font/format/size of that
cell. After I know the width of the column and the width of the
content, the number of spaces between neighboring values should be a
function of the difference in the widths in excel. But I can't find
any websites/books which would give me this kind of information.

Thanks a lot, greatly appreciate any help or tips.

Just to clarify, I am more interested in the answer to my question
above, rather than a workaround such as saving the file first, then
manually increase/decrease the spaces in the text file.

 

[Jean-Yves]

Hi,
You need a string variable with fixed lenght, then write each column value
at the position you want
Maybe you can use this as a basis :

Sub OjtWriteCorrectionList()
Dim TempLine As String
Dim fileNum As Integer
Dim strFileName As String
Dim strMonth As String
Dim strYear As String
Dim strDays As String
strYear = InputBox("Enter a year, format YYYY", , "2007")

strMonth = InputBox("Enter a Month number between 1 and 12")
If strMonth = "" Then
Application.ScreenUpdating = True
Exit Sub
End If
If (strMonth < 1) Or (strMonth > 12) Then
MsgBox "Invalid Month number"
Application.ScreenUpdating = True
Exit Sub
End If
'strMonth = MonthName(strMonth)
strFileName = ThisWorkbook.Path & "\Correction " & strYear & " " & strMonth
& ".csv"

On Error GoTo 0
fileNum = FreeFile 'get a freefile number
Open strFileName For Output As #fileNum

Do While rec.EOF = False
TempLine = Space(63)
Mid(TempLine, 1, 3) = "155"
Mid(TempLine, 5, 4) = rec.Fields("Payroll").Value
Mid(TempLine, 52, 6) = "030,00" 'strDays
Mid(TempLine, 59, 2) = Format(strMonth, "00")
Mid(TempLine, 62, 2) = Right(strYear, 2)
Print #fileNum, TempLine
Loop

Close #fileNum

MsgBox "Export OJTI done in " & strFileName, vbInformation, "Operation
successfully completed"

End Sub

 

[peng.gong]

Thanks a lot, but it doesn't seem to answer my original question:
given the excel file,
how do I know the number of spaces between any two neighboring values
in the saved text file (space delimited)?

I ask this because originally the text file is created in the
following way:

1. autofit all columns in the xls file
2. make all columns wider by same magnitude in the code:
Columns(iCols).ColumnWidth = Columns(iCols).ColumnWidth + iColFit
3. create the text file in the code:
Activeworkbook Saveas ... Format:=xlTextPrinter

As i have many files to save, i want to use low lever file I/O to
create the text file using commands such as
"CreateTextFile" and "WriteLine". But the problem is i want to make
sure the text files created in the old way and new way
are exactly the same. As i need to specify the number of spaces
between neighboring values in the new way, this goes back
to my original question: how do i know the number of spaces between
values in the text file given the xls file? (the number could
depend on the contents of cells in the xls file, as autofit is used in
the old way)

Thanks.