View Full Version : Importing and formatting text files
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
01-15-2012, 03:17 AM
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
01-19-2012, 12:11 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.