Export data from word file in an excel with a macro

[mathon]

Hi,

I have word file with many data groups which look for example like this:

Test Name: Test User Interface

Linked Defects:
Defect ID LinkedEntityID Defect:Summary
414 1019 Button is too small

Test Name: Test Performance

Linked Defects:
DefectID LinkedEntityID Defect:Summary
324 1039 Performance is bad.

....

I want to create an excel list from that input with the following columns and the corresponding data:
TestName DefectID LinkedEntityID Defect:Summary


Can anybody give me help here how I have to define the word macro in an easy way to solve this problem? - Im working already for a long time on that.

Many thanks in advance.
Regards
pat

 

[white605]

Try excel's Data>Import>External Data. Play around with the delimited with prompt and see if you can get what you want in the result.
wjwjr

 

[SkipVought]

Hi,

Where does this Word file come from?

Is there any chance of another source, like where did the creator of the Word file get the info?

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[mathon]

unfortunately it does not work with the import external data. the word file is generated from a document generator which i use in another program.

is there not the possibility to write an easy macro to import the corresponding word data to an excel sheet?

 

[Gavona]

1. You only need a macro to help with this if you are going to repeat the operation on multiple occaisions.
2. Solve the problem without a macro (in steps)
3. Record each step (Tools, Macro, Record)
4. Edit the recorded code
5. Post back to Forum707 if you need help and advice with the macro

This forum can help you with step 2.

I suggest that you load the data into Excel and do all your work in that application. It may well be that you can miss out the word step and load the generated document direct into excel.

Likliehood is that the document will load initially into a single column. That is fine and Data,Text to columns will almost certainly resolve that issue.

I would follow the following steps:
1. Load into excel.
2. Highlight column A and choose Data,Filter,Autofilter
3. Filter for blanks
4. Highlight the blank rows and delete them (Edit,Delete, EntireRow
5. If you have other totally unwanted rows delete these in the same way. Remove the autofilter (Data,Filter,Autofilter).
6. With your data in column A, Insert a column to the left so your data is now in ColumnB.
7 ColumnA will be used to hold the TestName.
8 In cell A2 make a formula
=if(Left(B2,9)="Test Name",B2,A1)
9. Copy this formula down column A
10. In A1 put the formula =B1
11. Highlight (select) column A,
Edit,Copy
Edit,PasteSpecial,Values
12. Select ColumnB
13 Data, Autofilter
14. Do a custom autofilter StartsWith "Test Name"
15. Select these rows and delete them.
16. Select column A & use Edit,Replace to remove "Test Name: " and replace it with nothing
17 Select Column B and use text to columns to split it up. 18. Insert A new Row 1 and populate it with some headings.

19. Repeat each step with macro recorder on, examining the code and editing it. Personally I would do one step at a time in this way and join them together at the end. Add comments to explain what each step of the macro is doing.
20. Post in the VBA forum the code you create and what help you need.

Hope this gets you started

Gavin

 

[mathon]

the problem is that i cannot load the word document in an excel sheet, it does not work with Data - Import External Data - Import Data because in that case I would have to to choose a data source.

how can I load the word document in excel? :-/

 

[Gavona]

What format is the export from the report writer in? If it is straight text then simply File,Open and point at the file. (You may need to alter the Files of Type to "All" in order that you can see the file).

Alternative2, in word, File,SaveAs,Type Text. Then open with excel.

Alternative3, in word, Ctrl-A to select
Ctrl-C to copy

Open a blank workbook

Click in cell A1
Ctrl-v to paste

Alternative4
Data,Import.....

Alternative5
Search Help for Import a text file

Gavin

 

[Gavona]

Using Import Data, when asked to select a data source simply point at your file. Remember to change the "Files of Type" to "All", which is first on the list
Still don't know why you are using Word at all. Use the output from the report writer directly.

Gavin

 

[white605]

If others can help with code to
scan the worksheet and delete the rows that contain:

"Linked" or "Defect ID" or "Test Name"
or are completely blank

And you save your existing Word Document to a text file the following macro will format the data before hand and leave you with a worksheet with what "i think" you want.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/20/2007 by Joe
'

'
    Workbooks.OpenText Filename:="C:\test.txt", Origin:=1254, StartRow:=1, _
        DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(20, 1), Array(37, 1) _
        ), TrailingMinusNumbers:=True
End Sub

I did this often a long time ago importing from sdf type files into dbf type files with simple xbase prg that used a Case statemnt inside a Do While Loop that did just what you are looking for.

Would be very interested to see examples of the code that treats rows and columns in xcel like records and fields.
wjwjr

 

[dcompto]

If the formatting of your Word document is consistent, you could easily workaround the problem with several Search and Replace passes (and, of course, turn on the macro recorder if you have more than one document to process). Based on the example you gave, I was able to perform the following which gave the desired end result:

#1
Search for: [type 2 spaces]
Replace with: ^t
Click Replace All

#2
Search for: ^t[type 1 space]
Replace with: ^t
Click Replace All

#3
Search for: ^t^t
Replace with: ^t
Click Replace All
Repeat clicking Replace All until it says it has made 0 replacements

#4
Search for: Test Name:[type 1 space]
Replace with: [nothing]
[Note: Be sure the "Replace with:" window is empty!]
Click Replace All

#5
Search for: ^p^pLinked Defects:^pDefectID^tDefectID^tLinkedEntityID^tDefect:Summary^p
Replace with: ^t
Click Replace All

#6
Search for: ^p^p
Replace with: ^p
Click Replace All

#7
Search for: .^p
[Note: The . is a period]
Replace with: ^p
Click Replace All

Now, copy and paste your data into A2 in Excel.
Then, add your column labels in Row 1.
Done.