Help using FileDialog with OpenText

[Fredriksson]

I am trying to import a text file into a specific worksheet. Since the name
and location of the file changes every month, I am trying to use the
FileDialog method. After the user has selected the file, I would like to use
OpenText method to import the file. In the file, Columns 1 through 11 need
to be specified as type TEXT and Column 12 can be general. I am having a
having a hard time trying to understand the correct way writing the syntax
for the FieldInfo in the OpenText.

Any suggestions would be helpful.

 

[Dave Peterson]

I think using the application.getopenfilename would be more straight forward.
And if you record a macro when you import a typical text file, you'll see the
code to specify the fieldinfo parm.

Text files can vary--they can be delimited or fixed width. So any guess that
matched your requirements would be pretty amazing.

But in general, your recorded code after the tweaking would look like:

Option Explicit
Sub Testme01()

Dim myFileName As Variant
Dim DestCell as Range
Dim TextWks as worksheet

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

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Set DestCell = worksheets("sheet1").range("a1")

Workbooks.OpenText Filename:=myFileName '....rest of recorded code here!

set TextWks = activesheet

'this'll wipe out any existing stuff on the destcell's worksheet
textwks.cells.copy _
destination:=destcell

textwks.parent.close savechanges:=false

End Sub