Hi,
I've been using the following script for several months to browse for and import a text file into excel. The instrument I use to generate the data file can now save it as an excel file rather than a text file. I'm trying to modify the script to allow for import of the excel file format instead of the tab-delimited text file. I've changed the file filter to show xls files (not shown in following script), but it doesn't import the data. Do I need to re-define 'Sep' somehow for excel files?
Thanks!
[vba]Public Sub ImportTextFile(FName As String, Sep As String)
Dim Rw As Long
Dim WholeLine As String
Dim WS As Worksheet
Dim txt As Variant
Application.ScreenUpdating = False
'On Error GoTo EndMacro:
Set WS = Sheets("IMPORT")
Rw = WS.Cells(Rows.Count, 1).End(xlUp).Row()
If Rw <> 1 Then Rw = Rw + 1
Open FName For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
txt = Split(WholeLine, Sep)
WS.Cells(Rw, 1).Resize(, UBound(txt) + 1) = txt
Rw = Rw + 1
Wend
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1
End Sub
Public Sub DoTheImport()
Dim FName As Variant
Dim Sep As String
FName = Application.GetOpenFilename _
(filefilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
Worksheets("START HERE").Range("A22") = FName
Worksheets("START HERE").Range("A21") = "FILEPATH"
Worksheets("SUMMARY TABLE").Range("B6") = FName
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If
'chr(9) defines tab as delimiter
Sep = Chr(9)
ImportTextFile CStr(FName), Sep
End Sub
[/vba]
Reply With Quote
