Importing a text file into spreadsheet

[Guest]

I have a text file that I receive once a month and the filename is not the same every month. I created a macro to import the text file, however, since the filename is not the same, I added some code to use an Open dialog box to select the file. I set fileToOpen as the filename that I open. I now need to have the macro import "fileToOpen". This is the code that Excel came up with for the importing of the text file. I need to tell it to import "fileToOpen". I just don't know how to do this. Any help would be great. Thanks. Matt (I tried replacing the two filenames with fileToOpen, but that didn't work. I may not have done it right though.

With ActiveSheet.QueryTables.Add(Connection:=
"TEXT;D:\JAN2004_MOMHP0000_135608.TXT", Destination:=Range("A1")
.Name = "JAN2004_MOMHP0000_135608
.FieldNames = Tru
.RowNumbers = Fals
.FillAdjacentFormulas = Fals
.PreserveFormatting = Tru
.RefreshOnFileOpen = Fals
.RefreshStyle = xlInsertDeleteCell
.SavePassword = Fals
.SaveData = Tru
.AdjustColumnWidth = Tru
.RefreshPeriod =
.TextFilePromptOnRefresh = Fals
.TextFilePlatform = xlWindow
.TextFileStartRow =
.TextFileParseType = xlDelimite
.TextFileTextQualifier = xlTextQualifierDoubleQuot
.TextFileConsecutiveDelimiter = Fals
.TextFileTabDelimiter = Fals
.TextFileSemicolonDelimiter = Tru
.TextFileCommaDelimiter = Fals
.TextFileSpaceDelimiter = Fals
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1
.Refresh BackgroundQuery:=Fals
End With

 

[Beto]

I have a text file that ...

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;D:\JAN2004_MOMHP0000_135608.TXT", Destination:=Range("A1"))
.Name = "JAN2004_MOMHP0000_135608"

Hi,
First question: FileToOpen includes the path?

if it does try with:
"TEXT;" & FileToOpen, Destination....

Otherwise, add the path manually like:
"TEXT;D:\" & FileToOpen, Destination....

I don't know, buy the .Name property may not be needed to be changed, it
must be name assigned to the table, but it'll look better if you change
it. So if the path is not included in FileToOpen use:
.Name = Left(FileToOpen,1,len(FileToOpen)-4)
the "-4" is to strip the extension, just like the example.

Regards,