Workbooks.OpenText Help Needed

[Programmer1974]

I am working with Excel 97 and I want to be able to open a comma delimited text file and set the format for all rows to be "Text" verses "General". Below is the code that I am using to open the file, but I am not having much luck finding the code to set it to "Text". Any ideas???

Dim Infile
Infile = "C:\DATA.PRN"

Workbooks.OpenText FileName:= _
Infile, Origin:=xlWindows, _
StartRow:=1, DataType:=Excel.XlTextParsingType.xlDelimited, Comma:=True

 

[Zathras]

Something like this, perhaps?

Dim Infile As String
Dim SheetName As String

    SheetName = "DATA.PRN"
    Infile = "C:\" + SheetName
    Workbooks.OpenText FileName:= Infile, _
        Origin:=xlWindows, _
        StartRow:=1, _
        DataType:=Excel.XlTextParsingType.xlDelimited, _
        Comma:=True
    Workbooks(SheetName).ActiveSheet.UsedRange.NumberFormat = "@"

 

[Programmer1974]

Thanks.

But when I add this code, I am getting a subscript out of range error with this code, Run time error '9'. My guess would be the "UsedRange". Does this need to be defined some how?

 

[Zathras]

No, "UsedRange" is a built-in property.

The problem is somewhere with the line starting

  Workbooks(SheetName)

Did you copy the code as posted? The SheetName it is looking for must be in the string variable SheetName.

Set a break point on that line and take a look at what is in the variable.

It works just fine for me as posted.

 

[Programmer1974]

Thanks Zathras. I had a type-o that I didn't see yesterday. This is now working. This did bring in the file as Text, but Excel still dropped leading zeros on columns that I don't want formating to be done on. Any thoughts on how I can get around this issue?

 

[PHV]

Take a look at the FieldInfo parameter of the OpenText method.
Have you just tried the macro recorder ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Zathras]

I've never found a satisfactory solution for preserving leading zeros when opening a .csv file. All I do is format the column with a custom format after the fact.

For example if you want to see a six character sequence with leading zeros like 000123, then use a custom format of six zeros: 000000

Remember the difference between the CONTENTS of a cell and the FORMAT of the cell. Even when cell appears to have 000123 in it (after applying the custom format) the contents is still just a number: 123. This is true even if the format of the cell is Text. You can use the CELL function to determine whether a given cell is being processed as Number or Text (or some other thing).