automate opening a .txt file

[James]

I am importing data into excel from a .txt file, I would like a macro that
when it runs it will ask me to locate the .txt file it wants to open. The
goal is to have excel open a .txt file.

 

[Pete_UK]

If you open a .txt file in Excel you will be taken into the Text
Import Wizard and you can specify how Excel will treat each column of
data it imports. So, it might be better to record a macro while you do
this once, then the macro will record the steps you take to define
your data. You might also want to adjust column widths and formats
etc. as part of the import process. You may need to edit the macro
slightly so that you can specify a different filename each time you
run the macro.

Hope this helps.

Pete

 

[James]

Pete,
I tried your suggestion and it works for that file. I found something on
the message board from Dave Peterson his code is as follows:

Option Explicit
Sub testme()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files,
*.Txt", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later"
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName

End Sub

It does exactly what I want it to do, but it does not prompt me for the
Import Wiazrd. That is what this code needs.

 

[James]

This is what I was looking for, for anyone else that is intrested. It will
prompt you to the import wizard.

Sub ImportTextFile()

Dim DestBook As Workbook, SourceBook As Workbook
Dim DestCell As Range
Dim RetVal As Boolean

' Turn off screen updating.
Application.ScreenUpdating = False

' Set object variables for the active book and active cell.
Set DestBook = ActiveWorkbook
Set DestCell = ActiveCell

' Show the Open dialog box.
RetVal = Application.Dialogs(xlDialogOpen).Show("*.txt")

' If Retval is false (Open dialog canceled), exit the procedure.
If RetVal = False Then Exit Sub

' Set an object variable for the workbook containing the text file.
Set SourceBook = ActiveWorkbook

' Copy the contents of the entire sheet containing the text file.
Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)).Copy

' Activate the destination workbook and paste special the values
' from the text file.
DestBook.Activate
DestCell.PasteSpecial Paste:=xlValues

' Close the book containing the text file.
SourceBook.Close False

End Sub