ScottSmith
New Member
- Joined
- Mar 8, 2003
- Messages
- 3
I want to import a delimited text file with a macro. Using the record function gives me all the code to do it, but, that doesn't give me the flexibilty to open different file names. The file name is hard coded in.
I've tried the: Application.GetOpenFilename and set that equal to a variable but kept getting an Run-Time Error 1004 (application/object defined error) When I subsituted the File path name for the variable
Here are the 2 code variations I've tried:
This is generated from the record function:
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\Scott\Desktop\Table.txt", Destination:=Range("A1"))
.Name = "Table"
.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 = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "/"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
I then modified this code myself: BOLD is where I inserted the variables for the file
Dim FullPath As String, FilePath As String, Filename As String
'Get a text file name
FullPath = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please selec text file...")
If strFullPath = "False" Then Exit Sub
'Splits FullPath into path and file name
Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT")
strFilePath = oFSObj.GetFile(strFullPath).ParentFolder.Path
strFilename = oFSObj.GetFile(strFullPath).Name
With ActiveSheet.QueryTables.Add(Connection:=FullPath, Destination:=Range("A1"))
.Name = Filename
.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 = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "/"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
End Sub
I've tried the: Application.GetOpenFilename and set that equal to a variable but kept getting an Run-Time Error 1004 (application/object defined error) When I subsituted the File path name for the variable
Here are the 2 code variations I've tried:
This is generated from the record function:
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\Scott\Desktop\Table.txt", Destination:=Range("A1"))
.Name = "Table"
.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 = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "/"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
I then modified this code myself: BOLD is where I inserted the variables for the file
Dim FullPath As String, FilePath As String, Filename As String
'Get a text file name
FullPath = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please selec text file...")
If strFullPath = "False" Then Exit Sub
'Splits FullPath into path and file name
Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT")
strFilePath = oFSObj.GetFile(strFullPath).ParentFolder.Path
strFilename = oFSObj.GetFile(strFullPath).Name
With ActiveSheet.QueryTables.Add(Connection:=FullPath, Destination:=Range("A1"))
.Name = Filename
.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 = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "/"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
End Sub