PDA

View Full Version : changing imported file type



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

Norie
01-23-2008, 08:44 AM
You can't open a Excel file line by line like that.

That method is only really for text files.

squaredealb
01-23-2008, 11:44 AM
Okay, thanks Norie. I will look into other means of moving the data into my analysis template.