Opening a text file using VB

[LandDownUnder]

I am trying to open a text file using the standard line;
Workbooks.OpenText filename:="DATA.TXT", _
dataType:=xlDelimited, tab:=True

Two problems;
1) The data file (DATA.TXT) will change from week to week. I would like the users to be able select each week which text file to open. Is there some means for the user to browse for a file or make a choice without actually having to type the full path and name in to an InputBox?

2) The The Text Import Wizard needs to be suppressed because the VB code will handle this step.

Any assistance will be greatly appreciated.

Regards
GM

 

[Legacy 98055]

Hi LandDown.
A dialog can easily be shown to allow the user to select a text file. Before continuing:
You say that VB will handle the parsing? Not the import wizard? Maybe you should open the file differently(Not use the OpenText method)
If you do not mind, please post the remainder of your code.
Tom

 

[LandDownUnder]

G'day Tom

Originally the user had to type the file name and path in to a cell who range name is DATAFile. I want to be able to get away from the user having to type file names and paths. The code that I have at the moment (see below) is still picking up the file from the named range and then the code handles the parsing.

I really want to be able to replace the Filename:=Range("DATAFile") section with an option for the user to select the file.

'Open the file specified at the named range "DATAFile"
Workbooks.OpenText Filename:=Range("DATAFile"), Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Comma:=True, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5,1))

Hope that this is enough for you to go on. Thanks heaps Tom.

 

[Legacy 98055]

Lacking a test file to open, I was unable to test this, but you should be fine. Brings up a dialog box to grab your files path/name.
You can make the dialog open to a specific folder by using ChDir below<pre>
Dim FileToOpen
'Type the directory where these files will be located here
'This is optional
'Example:
ChDir "CMyDataFiles"
FileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")
'if user cancelled then exit
If Not FileToOpen Then Exit Sub
'Open the file specified using the Get Open File dialog
Workbooks.OpenText Filename:=FileToOpen, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Comma:=True, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1))</pre>
Tom
This message was edited by TsTom on 2002-08-28 23:55

 

[Ivan F Moala]

On 2002-08-28 23:31, LandDownUnder wrote:
G'day Tom

Originally the user had to type the file name and path in to a cell who range name is DATAFile. I want to be able to get away from the user having to type file names and paths. The code that I have at the moment (see below) is still picking up the file from the named range and then the code handles the parsing.

I really want to be able to replace the Filename:=Range("DATAFile") section with an option for the user to select the file.

'Open the file specified at the named range "DATAFile"
Workbooks.OpenText Filename:=Range("DATAFile"), Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Comma:=True, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5,1))

Hope that this is enough for you to go on. Thanks heaps Tom.

Hi Landdownunder and welcome to the Board
Try this;

<pre/>
Sub Tester()
Dim TxtFileName As String

TxtFileName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If TxtFileName = "False" Then Exit Sub

On Error GoTo ErrOpen
Workbooks.OpenText Filename:=TxtFileName, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Comma:=True, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1))


Exit Sub
ErrOpen:

MsgBox Err.Number & vbCr & _
Err.Description & vbCr, _
vbMsgBoxHelpButton, "Error Open", Err.HelpFile, Err.HelpContext
End Sub
</pre>