Importing and formatting text files

[dyls]

Hi

I am trying to import a text file using the following:

Sub ImportTextFile()

Set myTextFile = Workbooks.Open("\My Documents\Surf Temps\SurfaceTemp.txt")

myTextFile.Sheets(1).Range("A1").CurrentRegion.Copy _
ThisWorkbook.Sheets(1).Range("A1")

myTextFile.Close (False)

End Sub

The only problem is that I get 3 columns of data jammed into 1. How do I make it so the spaces in the text file are read as new columns?

Thank you for any assistance.

[shrivallabha]

Welcome to VBAX.

Assuming that your data has space only as delimiter, this should work. Otherwise using macro recorder perform "Text To Columns" and then use that recorded code in this macro like:
Sub ImportTextFile()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set myTextFile = Workbooks.Open("\My Documents\Surf Temps\SurfaceTemp.txt")
myTextFile.Sheets(1).Range("A1").CurrentRegion.Copy _
ThisWorkbook.Sheets(1).Range("A1")

'Text to columns
ThisWorkbook.Sheets(1).Range("A1").CurrentRegion.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True

myTextFile.Close (False)

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

[wakdafak]

try this, hope it help you :yes

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ImportTextFile
' This imports a text file into Excel.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub ImportTextFile(FName As String, Sep As String)
Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer
Dim column 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)
If ColNdx = 16 Then
RowNdx = RowNdx + 1
ColNdx = 1
End If
Wend
RowNdx = RowNdx + 1
Wend
Columns("A:N").EntireColumn.AutoFit
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END ImportTextFile
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End Sub