Import File macro problem

[KrogerKommandeur]

Hello all,

I am trying to use the code below in a macro to import a file by first opening the Open File dialog box, allowing the user to select the file that they wish to use and then passing the file path to the Connection property of the Add Method. I have gotten run-time error 1004, which says that

Excel cannot find the text file to refresh this data range.

I think that I do not exactly understand how to get the actual file pathname from the GetOpenFileName method. Does anyone know how to do this? I've looked around and the big suggestion is to create a user-defined type called OpenFileName, but I do not really understand how to implement this.

Can someone lend me a hand?

Sub ImportFile()
'set up of list of file filters
Filt = "Text Files (*.txt),*.txt," & _
"Comma Separated Files (*.csv),*.csv," & _
"All Files (*.*),*.*"

'Display *.txt by default
FilterIndex = 4

'Set the dialog box caption
Title = "Select a File to Open"

FileName = Application.GetOpenFileName(FileFilter:=Filt, _
FilterIndex:=FilterIndex, Title:=Title)

MsgBox FileName

With ActiveSheet.QueryTables.Add(Connection:="TEXT;FileName", Destination:=Range("A2&quot)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 1)
.Refresh BackgroundQuery:=False
End With
Columns("A:A&quot.EntireColumn.AutoFit
Columns("B:B&quot.EntireColumn.AutoFit
Columns("C:C&quot.EntireColumn.AutoFit
Columns("D&quot.EntireColumn.AutoFit
Columns("E:E&quot.EntireColumn.AutoFit
End Sub

Wally -
The Gahndi that eats

 

[SkipVought]

Hi,

Think that your error is right here...

With ActiveSheet.QueryTables.Add(Connection:=FileName, Destination:=Range("A2"))

or, if it needs to have "TEXT;"...

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FileName, Destination:=Range("A2"))

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KrogerKommandeur]

Thank you, Skip!

The second solution worked great. The Add method apparently needs to know exactly what the format of the datasource is before it moves on. Point is it worked fine.

Thanks!

Wally -
The Gahndi that eats