Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do Import data tab delimited text?
Hi all, I am trying to write a macro that imports tab delimited data in two
columns. I am not that savvy with writing macros, so I copied the code from a website. http://www.cpearson.com/excel/imptext.htm but I need to tweak it so that it can import tab delimited data, I don't need to select the delimiter, it will always be tabbed in this case. Here's what I have (2 macros), can I do this in one? Public Sub DoTheImport() Dim FName As Variant Dim Sep As String FName = Application.GetOpenFilename _ (filefilter:="Text Files(*.dat),*.dat,All Files (*.*),*.*") If FName = False Then MsgBox "You didn't select a file" Exit Sub End If Sep = InputBox("Enter a single delimiter character.", _ "Import Text File") ImportTextFile CStr(FName), Sep End Sub **************************************** Sub ImportTextFile(FName As String, Sep As String) Dim RowNdx As Integer Dim ColNdx As Integer Dim TempVal As Variant Dim WholeLine As String Dim Pos As Integer Dim NextPos As Integer Dim SaveColNdx As Integer Application.ScreenUpdating = False 'On Error GoTo EndMacro: SaveColNdx = ActiveCell.Column RowNdx = ActiveCell.Row Open FName For Input Access Read As #1 While Not EOF(1) Line Input #1, WholeLine If Right(WholeLine, 1) < Sep Then WholeLine = WholeLine & Sep End If ColNdx = SaveColNdx Pos = 1 NextPos = InStr(Pos, WholeLine, Sep) While NextPos = 1 TempVal = Mid(WholeLine, Pos, NextPos - Pos) Cells(RowNdx, ColNdx).Value = TempVal Pos = NextPos + 1 ColNdx = ColNdx + 1 NextPos = InStr(Pos, WholeLine, Sep) Wend RowNdx = RowNdx + 1 Wend EndMacro: On Error GoTo 0 Application.ScreenUpdating = True Close #1 End Sub Thanks in advance. Rob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show text data in excel pivot table | Excel Discussion (Misc queries) | |||
When I import a csv file text that contained a + gets scrambled | Excel Discussion (Misc queries) | |||
import data from a text file | Excel Discussion (Misc queries) | |||
Import Data Keeps asking for Password | Excel Discussion (Misc queries) | |||
import external data from changing file name | Excel Worksheet Functions |