Opening a text file

[MARK858]

I have the following code that allows the user to select a .txt file from his chosen location but I am stuck as how to get the click on the open button to do the final open on the file.

Any advice would be welcomed.

Public DI As String
Public DIName As String
Public DIPath As String

Sub RunText_Click()
Dim wsName As String
Call GetImportFilename
End Sub
 
Sub GetImportFilename(Optional strTitle As String)
    Dim i As Integer
    If Len(strTitle) = 0 Then strTitle = "..."
    DI = Application.GetOpenFilename("Text Files (*.txt), *.txt,All Files (*.*),*.*", , strTitle, , False)
End Sub

The end objective, in case it makes a difference is to open the .txt file, have excel put in the delimiter based on spaces then import/paste the file into sheet 2 of the active sheet. But 1 stage at a time, so all I am looking for at present is how to get the click on the open button working

 

[pbornemeier]

After the DI= statement add:

Workbooks.OpenText Filename:=DI

There are a lot of options with the Opentext method: Workbooks.OpenText(Filename, Origin, StartRow, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, TextVisualLayout, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers, Local) Check out the help file.

The file will be added as a new worksheet. You should turn on the macro recorder and open the text file using excel then see what options are recorded.

 

[MARK858]

Thanks for the suggestion Phil. l am on my phone now so I will have a play this evening . I should be ok after I get past this stage as I have knowledge of getting text files. the problem. I have had is our our IT department has restricted access to the C drive for some users which has meant having to hard code for each user which as you can imagine is a pain. i Will post back if I get stuck

 

[pbornemeier]

If you have to customize for each user, you can use ENVIRON("username") to return the name of the user which is logged in. The other environment variables are available as well.

 

[MARK858]

Hi again, I knew I had to hit a problem eventually. With the first three text files I need to convert there are 2 that are fine but with the third the data comes way out of line

Is there a way to use the GetOpenFilename method but to add set parameters rather than the pre-sets so I can just code one button(hopefully) differently.

I need to get it so the delimiters are set at

Origin:=xlWindows, _
        StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0 _
        , 1), Array(4, 1), Array(13, 1), Array(19, 1), Array(44, 1), Array(49, 1), Array(52, 1), _
        Array(59, 1), Array(65, 1), Array(73, 1), Array(78, 1), Array(86, 1), Array(96, 1), Array( _
        100, 1), Array(107, 1), Array(116, 1), Array(118, 1), Array(128, 1))

If so...how (especially the syntax).

Any help/ideas most welcome (before I blow up our IT dept. for being pains).

If it is not possible please let me know so I can explore other options for this file

 

[pbornemeier]

If there is something that distinguishes the file, you can open it as a single field, analyze it then close it and open it using the desired set of array statements.

FieldInfo:=Array(0, 2) will load an each row of the file into one cell in column A

 

[MARK858]

Phil, sorry if I seem a bit stupid but this is my first attempt at opening text files by code this way.
All the text files have different names (in this case the offfending file is called "CAPACITY") so I hope that covers

something that distinguishes the file

.

What I don't understand is the syntax of altering the opentext method.
Can you possibly do an highlighted alteration on my code in the original post to show how to set it for "Space" delimiter as I get various error messages including that it is "not optional"?
I have tried various things, including trying to adapt from the XL help page i.e.

Workbooks.OpenText filename:="CAPACITY", _
    dataType:=Space, tab:=False

but as yet I haven't got anything to work.

Could you also elaborate a bit on how to open it as a

single field

using OpenText?

I assume is the same theory as when you open a text file manually or hard code it in code without the opentext method.

Sorry for being such a nuisance

 

[pbornemeier]

This code should open a workbook and put all of the data in a single column

    Workbooks.OpenText Filename:=DI, _
        Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(0, 2), _
        TrailingMinusNumbers:=True

You could then count the spaces in it and generate something like the next statement depending on the number of columns that exist.

This code opened one of my files in a space delimited format it will probably not be right for your files. The file was one with 6 columns separated by a space. It appears as if the FieldInfo Array statements are one for each column with the first position denoting the column number and the second postion denoting the type of data (xlColumnDataType). 1 corresponds to xlGeneralFormat.

From Excel Help:
XlColumnDataType can be one of these XlColumnDataType constants.
xlGeneralFormat General
xlTextFormat Text
xlMDYFormat MDY date
xlDMYFormat DMY date
xlYMDFormat YMD date
xlMYDFormat MYD date
xlDYMFormat DYM date
xlYDMFormat YDM date
xlEMDFormat EMD date
xlSkipColumn Skip Column

    Workbooks.OpenText Filename:=DI, Origin:=437, StartRow:=1, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote _
        , ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:= _
        False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), TrailingMinusNumbers:=True

The best way to get a feel for what you need to do it to turn on the macro recorder and open a few files undre different contidions. You can then see what code was generated for each file and adapt it as necessary.

 

[MARK858]

Cheers Phil for your time.I. am on my phone at the moment so I will have a look tonight but looking at. it I think I will be fine. with what. you have put here. thanks for time

 

[MARK858]

Phil, just want to say a big thanks for your time, patience and explanations. After playing around with it last night and having a light bulb coming on today I have it all working as I want it.

When I have finished incorporating this into the current project I will have a look at opening a file using ENVIRON("username"), but I will have a bit of a surf around first for that.
Thanks again