Using Open...As # to Create Text File

Posted on 1998-09-22
Last Modified: 2010-04-30
I have a text file that I have to create on the PC using Excel v8. This file is then transported to a Unix machine where it is analysed by a current programming system.

The text file consists of 3 different types of data: a header, data records, and, finally, a footer. Each of these different data types has a different total character length. I.e. the text file should have the following structure:

Data Record#1
Data Record#2
Data Record#n

Rather than have the text file consist of one long string, each of these different data types should be delimited by a Carriage Return. However, there is to be no delimiting within each of the different data types.

The problem that I have is that, although I can create the text file OK on the PC, when it is viewed on Unix, the Carriage Return appears as ^M. E.g. in Unix, it appears as:

Data Record#1^M
Data Record#2^M

Data Record#n^M

How do I get rid of this? Or can I? Personally I believe that it would not be possible to get a carriage return between the different types without something having to appear in Unix; after all, you can't get something for nothing.

I have tried all different configurations of using the Open method for creating these files but the same thing (or worse in the second case below) happens in either case.

Below is a dummy sample indicating the type of coding that I have used to generate this text file:

Private Type HeaderData
    sH1 As String * 10
    sH2 …
    sCR as String * 1 'Carriage Return
End Type

Private Type RecordData
    sD1 As String * 3
    sD2 …
    sCR as String * 1 'Carriage Return
End Type

Private Type FooterData
    sF1 As String * 5
    sF2 …
    sCR as String * 1 'Carriage Return
End Type

Public Sub WriteFile()
    Dim Header As HeaderData '40 characters long
    Dim Data As RecordData '315 characters long
    Dim Footer As FooterData '55 characters long
    Dim File As String 'The full pathname of the .TXT file to be created
    'Populating the .sCR for each data type with Chr(13)
    'This worked out the worst as there were also (315-40) ^@ characters at the end of the Header record before commencing the Data record
    Open File For Random As #1 Len = Len(Data)
    Put #1, 1, Header
    Put #1, 2, Data
    Put #1, 3, Footer
    Close #1
    'Not including the .sCR data type elements in the Print # statements
    Open File For Output As #1
    With Header
        Print #1, .sH1, .sH2,....
    End With
    With Data
        Print #1, .sD1, .sD2,...
    End With
    With Footer
        Print #1, .sF1, .sF2,...
    Close #1
End Sub
Question by:Noggy
  • 4
  • 3
LVL 12

Expert Comment

ID: 1436080
<Ctrl M> is a <LF>. Your Unix system only needs the <CR> instead of the <CR>/<LF> pair that PRINT # supplies by default. You can inhibit the automatic <CR>/<LF> by ending your PRINT # statement with a semicolon (;). This will block *both*. What you need is to explicity place a <CR> into each line and *then* block the automatic <CR>/<LF>.


PRINT #1, .sH1, .sH2, ..., vbCR ;

This will place an explicit <CR> on the line and block the auto <CR>/<LF>.

The file will look a little weird if you do a DOS TYPE command - all of the text will rapidly scroll on one line. This is actually correct as there are no <LF>'s in the data stream.



Author Comment

ID: 1436081
I tried this out but it actually came out worse: at the end of every entry, there were spurious quantities of spaces before actually putting in the carriage return. In addition, the carraige return also still appeared as ^M when viewed in Unix.

I have since found that, in Unix, there is a Shell command called DOS2UNIX where such anomalous characters are converted into the ISO Unix character set.

However, this can only be done on the Unix station. I would still like to know if there is any way of achieving the correct result on a normal PC.

Expert Comment

ID: 1436082
Put #1, ,Header
For i = 1 to RecordCount
Put #1, ,DataRecord(i)
Put #1, ,Footer

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.


Author Comment

ID: 1436083
Sorry, but that's the same as the first method that I tried; the only difference being that you have not stipulated the record numbers that the records are Put at. But, believe me, I've tried this also.
LVL 12

Expert Comment

ID: 1436084
The spurious spaces have to be comming from the vars you're saving. The PRINT # statement doesn't invent chars. Try using just the LF instead of the CR and see if that helps.


Author Comment

ID: 1436085
I'm sorry, Mark, but the spurious spaces weren't from the vars that I was using. I discovered this by just remming out the ",vbCR;" and, lo and behold, the spurious spaces had gone.

As to just using ",vbLF;" instead, I will have to give it a go later. However, I will not be able to try this out myself as I do not have access to a Unix logon to view the results. At the moment, my users are using the Unix DOS2UNIX shell command instead. Do you know if there's a PC equivalent of this shell command? Not that I would expect it to come from Microsoft even if one should exist! :-)
LVL 12

Accepted Solution

mark2150 earned 50 total points
ID: 1436086
Your're using a *COMMA* between the last var and the vbCr! Use a ";" and the spaces will vanish!


Author Comment

ID: 1436087
Aha. But I do have to say it: You didn't have "; vbCR" in your first proposed answer, you had ", vbCR;" instead.

I shall accept your answer even though I haven't been able to test it yet. I know the sort of ball park area that needs to be looked at. Thanks.

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article ( for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question