Import text file without specifying a path macro won't work

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have been asked to write a macro that will import a text file into Excel (Office 2000) so that I can manipulate the data later. I used the macro recorder, and got a general idea of what I need to do, combined with topics in this forum. I put together this little macro, but I can't get it to work. The first part works great; it lets me choose the text file to import. The second part throws a "Run Time Error 1004: Application Defined or or object-defined error". The directory I am trying to pull data from is C:\Macros\Datafiles, and usually more than one text file exists in that directory, hence the need for the user to select the file. I have been playing around with statements for the past few days but cannot fix the problem. Can somebody please help? TIA

Sub GetFile()
Dim fName As String
fName = Application.GetOpenFilename(FileFilter:="Text Files (*.txt),*.txt")
If fName <> "False" Then

With ActiveSheet.QueryTables.Add(Connection:=fName, Destination:=Range("A1"))
.Name = "DATA"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "="
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2)
.TextFileFixedColumnWidths = Array(8, 13, 7, 11, 16)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End If
End Sub
 
Does it ned to open as a database?
The "Connection:=fName" bit is trying to connect
to a database, which a text file is not.

Do you just need to open the file if so try,
Workbooks.OpenText Filename:=fileToOpen...) There are some
more fields you can fill in here, but that is the basics.
 
Here's an update. I had a senior moment, and was using the wrong set of parameters, which got me past my original issue. I was originally having trouble connecting to my fName. Now, I am throwing an error in the ".Refresh BackgroundQuery:=False" statement. I tried changing it to "True" but that did not work either. Any ideas anyone? Thank you. Here is a snippet

Sub GetFile(
Dim fName As Strin
fName = Application.GetOpenFilename(FileFilter:="Text Files (*.txt),*.txt"
If fName <> "False" The

With ActiveSheet.QueryTables.Add(Connection:="TEXT; C:\Macros\Datafiles\" & fName, Destination:=Range("A1")
.Name =
"TBJ20151270
.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 = Fals
.TextFileCommaDelimiter = Fals
.TextFileSpaceDelimiter = Fals
.TextFileOtherDelimiter = ":
.TextFileColumnDataTypes = Array(1, 1, 1
.Refresh BackgroundQuery:=Fals

End Wit
End I
End Sub
 
You should already have the path stored in fName, you
don't need to add C:\Macros\DataFiles again. Try just
using fName
-----Original Message-----
Here's an update. I had a senior moment, and was using
Click to expand...
the wrong set of parameters, which got me past my original
issue. I was originally having trouble connecting to my
fName. Now, I am throwing an error in the ".Refresh
BackgroundQuery:=False" statement. I tried changing it
to "True" but that did not work either. Any ideas anyone?
Thank you. Here is a snippet.
Sub GetFile()
Dim fName As String
fName = Application.GetOpenFilename(FileFilter:="Text Files (*.txt),*.txt")
If fName <> "False" Then

With ActiveSheet.QueryTables.Add(Connection:="TEXT;
Click to expand...
C:\Macros\Datafiles\" & fName, Destination:=Range("A1"))
 
Back
Top