Opening a text file using VB

LandDownUnder

New Member
Joined
Aug 27, 2002
Messages
2
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
L

Legacy 98055

Guest
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

New Member
Joined
Aug 27, 2002
Messages
2
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.
 
L

Legacy 98055

Guest
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
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>
 

Forum statistics

Threads
1,148,270
Messages
5,745,787
Members
423,973
Latest member
man_this_is_hard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top