Saturday, 8 December 2012

How To Import Data from a Text File using Excel VBA Macro

Excel VBA Macro To Import Data from a Text File and Keep in One Cell
 Sub ImpTxtFile()
 Dim A As Integer
 Dim B As Integer

    With ActiveSheet.QueryTables.ADD(Connection:= _
    "TEXT;C:\Documents and Settings\Administrator\Desktop\code1.txt" _
    , Destination:=Cells(1, 1))
    .Refresh BackgroundQuery:=True
    End With

For A = 1 To 100
Cells(1, 1) = Cells(1, 1) & Cells(A, 1)
Next A

For B = 2 To 100
Cells(B, 1).Clear
Next B

End Sub

'Macro to Join all the imported text from all rows to first row'
'Pull out the search item from the cell'
Sub JoinRows()
Dim A As Integer
Dim B As Integer

For A = 1 To 100
Cells(1, 1) = Cells(1, 1) & Cells(A, 1)
Next A

For B = 2 To A
 Cells(B, 1).Clear
Next B

Cells(4, 2) = Mid(Cells(1, 1), InStr(Cells(1, 1), "True") - 10, 30)
End Sub

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts