squaredealb
01-23-2008, 08:35 AM
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!
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
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!
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