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
精彩评论