?
Solved

How to write an excel macro using VBA to extract data from a text file

Posted on 2003-02-27
4
Medium Priority
?
8,441 Views
Last Modified: 2012-06-27
I am just a rookie when it comes to VBA. Can anyone tell me how I can write a macro for the following function:

1) Open text file from a specified list of file names stored in an excel sheet. i.e if filename reads 123 on the excel sheet, macro will open this file with .txt extension.

2) The following is what is stored in text file

11,008,20020819,02216,001,02718, 00002

There is well over 5000 of these lines with different numbers. The 6th group of digits, i.e 02718 needs to be read and copied into a cell in another Excel worksheet.

3) The macro will switch back and forth between text file and excel file until 500 lines of the text file is read or EOF.

4) At this point the macro will automatically save the excel file name as a number determined by a counter. So after every 500 lines read and copied, the macro will save the excel file.

5) The macro will then continue with the next 500 lines of the text file or unitil EOF.Steps 3) and 4) are repeated until all the 6th group of digits are read from the text file and copied into the excel sheet.

6) At this point, macro will close the text file and open the next text file that is in the list on excel sheet. Steps 3) and 4) are repeated again.

7) Macro will keep performing all the above steps until the list in excel sheet is completed.

I appreciate all the help I can get with this problem. Thanks for everyone's time.




0
Comment
Question by:indyeng
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 

Accepted Solution

by:
gakibbie earned 200 total points
ID: 8044346
This does what you ask, though you will have to change the paths to match where you want to pick up and deliver your files...  Hope this helps.


Sub Main()
    Dim lEnd As Long, lRow As Long
    Dim sPath As String, sData As String, sFile As String, sLine As String
    Dim F1 As Integer, iRow As Integer
    Dim aData() As String

'1) Open text file from a specified list of file names stored in an excel sheet. i.e if filename reads 123 on the excel sheet, macro will open this file with .txt extension.
    lEnd = Cells(1, 1).End(xlDown).Row
   
    For lRow = 1 To lEnd
        sPath = "C:\TextFiles\" & Cells(lRow, 1).Value & ".txt"
        If Dir(sPath) = "" Then GoTo Next_File 'If text file has no data goto next file
        F1 = FreeFile
        Open sPath For Input As F1
        i = 1 'Line Counter
        Application.StatusBar = "Reading data from " & TargetFile & sPath & " ..."
        While Not EOF(F1)
'2) The macro will switch back and forth between text file and excel file until 500 lines of the text file is read or EOF.
            If i Mod 10 = 0 Then Application.StatusBar = "Reading line #" & i & " in " & sPath & " ..."
            If i = 1 Or i Mod 500 = 0 Then
                If sFile <> "C:\TextFiles\File_" & Cells(lRow, 1).Value & "_" & i & ".xls" Then
                    iRow = 1
                    If i <> 1 Or i Mod 500 = 0 Then
'3) At this point the macro will automatically save the excel file name as a number determined by a counter. So after every 500 lines read and copied, the macro will save the excel file.
                        ActiveWorkbook.Save
                        ActiveWorkbook.Close
                End If
                    sFile = "C:\TextFiles\File_" & Cells(lRow, 1).Value & "_" & i & ".xls"
                    Workbooks.Add
                    ActiveWorkbook.SaveAs Filename:=sFile
                End If
            End If
'4) The macro will then continue with the next 500 lines of the text file or unitil EOF.Steps 2) and 3) are repeated until all the 6th group of digits are read from the text file and copied into the excel sheet.
            sTest = ActiveWorkbook.Name
            'Workbooks(1).Activate
            Line Input #F1, sLine
            aData = Split(sLine, ",")
            Cells(iRow, 1).Value = aData(5)
            iRow = iRow + 1
            i = i + 1
        Wend
'5) At this point, macro will close the text file and open the next text file that is in the list on excel sheet. Steps 3) and 4) are repeated again.
        ActiveWorkbook.Save
        ActiveWorkbook.Close
        Close F1
Next_File:
'6) Macro will keep performing all the above steps until the list in excel sheet is completed.
    Next
End Sub
0
 
LVL 44

Expert Comment

by:bruintje
ID: 9283618
This question has been classified as abandoned.  I will make a recommendation to the moderators on its resolution in the next round.  I would appreciate any comments by the experts that would help me in making a recommendation.


It is assumed that any participant not responding to this request is no longer interested in its final deposition.

If the asker does not know how to close the question, the options are here:
http://www.experts-exchange.com/help/closing.jsp

PLEASE DO NOT ACCEPT THIS COMMENT AS ANSWER

HAGD:O)Bruintje
EE Cleanup Volunteer
0
 
LVL 3

Expert Comment

by:crimper
ID: 9562796
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Answered by gakibbie

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

John Pullin
EE Cleanup Volunteer
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question