开发者

VBA: Copy lines from text file into Word doc

Usually I create my macro's be recording the feature and then tweaking them accordingly, so I unfortunately have no idea of how to go about achieving this operation.

I have a number of textfiles that are archives of accounts; each file has at least 30k+ lines in, and hundreds if not thousands of accounts in each.开发者_运维技巧 The first line in the document has the account number, and each account is divided by a unique string of text.

The goal is to have a macro that looks in the text file, finds the account number, then copies all of the lines beneath it until it reaches the unique dividing string, and paste them into the active word document for viewing.

I don't imagine I'll get a concrete answer out of such a vague question, but any pointers that can be offered would be appreciated.


here's a quick & dirty one to illustrate the principles .... let the text file be called "accounts.txt" with the following content:

Account 1
item 1
item 2
item 3
=========
Account 2
item 4
item 5
=========
Account 3
item 6
item 7
=========

Now let's look at some very basic VBA code making use of Open As and Line Input and a loop construct ....

Sub GetAccountFromTextFile(FileName As String, Accnt As String)
Dim MyLine As String, State As String

    Open FileName For Input As #1
    State = "Searching"               ' we could make this much simpler but 
                                      ' want to illustrate the different stati
                                      ' the loop is reaching

    Do While Not (EOF(1) Or State = "End")

        Line Input #1, MyLine         ' read next line from text file

                                      ' now process as function of
                                      ' content and current state

        If State = "Reading" And MyLine = "=========" Then
            State = "End"

        ElseIf MyLine = "Account " & Accnt Then
            Selection.InsertAfter "Account " & Accnt & vbCrLf
            State = "Reading"

        ElseIf State = "Reading" Then
            Selection.InsertAfter MyLine & vbCrLf
        End If

    Loop
    Close #1

End Sub

you call this by another sub

Sub test()
    GetAccountFromTextFile "C:\Documents and Settings\MySelf\Desktop\accounts.txt", 1
    GetAccountFromTextFile "C:\Documents and Settings\MySelf\Desktop\accounts.txt", 3
    GetAccountFromTextFile "C:\Documents and Settings\MySelf\Desktop\accounts.txt", 2
End Sub

Start test() from anywhere in a Word Doc, and the following will be pasted into the document:

Account 1
item 1
item 2
item 3
Account 3
item 6
item 7
Account 2
item 4
item 5

Now you can be very creative in your main sub (maybe a dialog form) on how to get file name and account number before you can call the account getter, and you will need to modify the conditions for finding the account number and the seperation pattern within the getter. Not very sophisticated but should be enough to get you going.

Good luck MikeD

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜