excel read values from test file

Folks

I would like to understand how to import a txt file into excel with a macro

the values in the text file are 4 columns with a comma delimite

1,connection refused,7/10/13,10:01 am

also to be able to clear the excel sheet when the macro is re-run

thanks in advance!
rutgermonsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KimputerCommented:
Something like this?

Edit: "D:\testfile.txt"

Or add a prompt before it.


Sub Macro1()
ActiveWindow.Close

    Dim wbI As Workbook, wbO As Workbook
    Dim wsI As Worksheet

    Set wbI = Workbooks.Add
    Set wsI = wbI.Sheets("Sheet1") '<~~ Sheet where you want to import

    Workbooks.OpenText Filename:="D:\testfile.txt", Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:=True
    ActiveWorkbook.Sheets(1).Cells.Copy wsI.Cells
     
    ActiveWorkbook.Close SaveChanges:=False
End Sub

Open in new window

0
rutgermonsAuthor Commented:
Page seems to disappear
0
KimputerCommented:
There's no new workbook with the data you want?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

rutgermonsAuthor Commented:
nope, could u send me ur working solution perhaps (excel sheet)
0
Martin LissOlder than dirtCommented:
There are a lot of ways to read a file. Here's one.


Sub ReadFile()
Dim FF As Integer
Dim strData As String
Dim strParts() As String
Dim lngRow As Long

' A one-line heading is assumed.
lngRow = 1

' Get the first unused file number
FF = FreeFile

' Open the file for reading
Open "C:\temp\test.txt" For Input As #FF

' Read the reacords until end of file
Do While Not EOF(FF)
    ' Read one line at a time into strData
    Line Input #FF, strData
    ' Split the data at the comma
    strParts = Split(strData, ",")
    ' Place the data in columns A to D.
    Cells(lngRow, 1) = strParts(0)
    Cells(lngRow, 2) = strParts(1)
    Cells(lngRow, 3) = strParts(2)
    Cells(lngRow, 4) = strParts(3)
    lngRow = lngRow + 1
Loop

' Close the file
Close

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveCommented:
Have you considered a QueryTable:

Sub TXT_Import()
Dim ws As Worksheet, strFile As String
 
'Set ws = ActiveWorkbook.Sheets("Summary") 'to set using existing worksheet name
'or
Set ws = ActiveWorkbook.Worksheets.Add 'or create a new sheet

strFile = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please select text file...")
 
With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
     .TextFileParseType = xlDelimited
     .TextFileCommaDelimiter = True
     .Refresh
End With

End Sub

Open in new window


This will import a query table linked to the text file.
Right clicking the data and selecting 'refresh' will allow the data to be retrieved
(no macro required to refresh)
0
Martin LissOlder than dirtCommented:
I see that I missed the clearing part so here's an update.


Sub ReadFile()
Dim FF As Integer
Dim strData As String
Dim strParts() As String
Dim lngRow As Long


' A one-line heading is assumed.
lngRow = 1
ActiveSheet.UsedRange.Cells.Offset(1, 0).ClearContents
' If there are no headins then
'lngRow = 0
'ActiveSheet.UsedRange.Cells.ClearContents


' Get the first unused file number
FF = FreeFile

' Open the file for reading
Open "C:\temp\test.txt" For Input As #FF

' Read the reacords until end of file
Do While Not EOF(FF)
    lngRow = lngRow + 1
    ' Read one line at a time into strData
    Line Input #FF, strData
    ' Split the data at the comma
    strParts = Split(strData, ",")
    ' Place the data in columns A to D.
    Cells(lngRow, 1) = strParts(0)
    Cells(lngRow, 2) = strParts(1)
    Cells(lngRow, 3) = strParts(2)
    Cells(lngRow, 4) = strParts(3)
Loop

' Close the file
Close

End Sub

Open in new window


In any case I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.