Combine Word Files into Excel

[Tofik]

Hi Guys, I need help. It is very important for me and if someone know how to do it please help.
1) Word files they are have the same info. And I need combine all Word files into one Excel File (only yellow lines ) see Example 2. I also add Word Document for you.

Loading Google Docs

Example 2:
In Excel File It should be as this.

 

[alansidman]

If you save your file as a .PDF, then you can import to Power Query and clean up as shown in the following Mcode. Then transfer to Native Excel.

let
    Source = Pdf.Tables(File.Contents("C:\Users\alans\Downloads\05-B92-4-883(1-6).pdf"), [Implementation="1.3"]),
    Table002 = Source{[Id="Table002"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table002,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",3),
    #"Kept First Rows" = Table.FirstN(#"Removed Top Rows",6),
    #"Promoted Headers" = Table.PromoteHeaders(#"Kept First Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"", Int64.Type}, {"Drawing No", type text}, {"Rev#(lf)No.", Int64.Type}, {"*** No", type text}, {"Material 1#(lf)Grade No", type text}, {"Material 2#(lf)Grade No", type text}, {"Thickness#(lf)mm", Int64.Type}, {"Wel#(lf)d#(lf)No.", Int64.Type}, {"Weld#(lf)Type", type text}, {"Penet#(lf)ration#(lf)Type", type text}, {"Welder#(lf)No.", type text}, {"WPS#(lf)No.", Int64.Type}, {"Weld Date", type date}, {"Acc", type text}, {"Rej", type text}, {"RT", type text}, {"UT", type text}, {"MT", type text}, {"PT", type text}})
in
    #"Changed Type1"

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Column1	Drawing No	Rev No.	*** No	Material 1 Grade No	Material 2 Grade No	Thickness mm	Wel d No.	Weld Type	Penet ration Type	Welder No.	WPS No.	Weld Date	Acc	Rej	RT	UT	MT	PT
2	1	AF-SS01-CIV-SSD-0083	1	05-B92-4-883	S275JR	S275JR	5	1	FW	N/A	SW-066	24	10/2/2020
3	2	AF-SS01-CIV-SSD-0083	1	05-B92-4-883	S275JR	S275JR	3	2	FW	N/A	SW-066	24	10/2/2020
4	3	AF-SS01-CIV-SSD-0083	1	05-B92-4-883	S275JR	S275JR	5	3	FW	N/A	SW-066	24	10/2/2020
5	4	AF-SS01-CIV-SSD-0083	1	05-B92-4-883	S275JR	S275JR	5	4	FW	N/A	SW-069	24	10/2/2020
6	5	AF-SS01-CIV-SSD-0083	1	05-B92-4-883	S275JR	S275JR	3	5	FW	N/A	SW-069	24	10/2/2020
Sheet1

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[Tofik]

The problem is

If you save your file as a .PDF, then you can import to Power Query and clean up as shown in the following Mcode. Then transfer to Native Excel.

let
    Source = Pdf.Tables(File.Contents("C:\Users\alans\Downloads\05-B92-4-883(1-6).pdf"), [Implementation="1.3"]),
    Table002 = Source{[Id="Table002"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table002,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",3),
    #"Kept First Rows" = Table.FirstN(#"Removed Top Rows",6),
    #"Promoted Headers" = Table.PromoteHeaders(#"Kept First Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"", Int64.Type}, {"Drawing No", type text}, {"Rev#(lf)No.", Int64.Type}, {"*** No", type text}, {"Material 1#(lf)Grade No", type text}, {"Material 2#(lf)Grade No", type text}, {"Thickness#(lf)mm", Int64.Type}, {"Wel#(lf)d#(lf)No.", Int64.Type}, {"Weld#(lf)Type", type text}, {"Penet#(lf)ration#(lf)Type", type text}, {"Welder#(lf)No.", type text}, {"WPS#(lf)No.", Int64.Type}, {"Weld Date", type date}, {"Acc", type text}, {"Rej", type text}, {"RT", type text}, {"UT", type text}, {"MT", type text}, {"PT", type text}})
in
    #"Changed Type1"

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Column1	Drawing No	Rev No.	*** No	Material 1 Grade No	Material 2 Grade No	Thickness mm	Wel d No.	Weld Type	Penet ration Type	Welder No.	WPS No.	Weld Date	Acc	Rej	RT	UT	MT	PT
2	1	AF-SS01-CIV-SSD-0083	1	05-B92-4-883	S275JR	S275JR	5	1	FW	N/A	SW-066	24	10/2/2020
3	2	AF-SS01-CIV-SSD-0083	1	05-B92-4-883	S275JR	S275JR	3	2	FW	N/A	SW-066	24	10/2/2020
4	3	AF-SS01-CIV-SSD-0083	1	05-B92-4-883	S275JR	S275JR	5	3	FW	N/A	SW-066	24	10/2/2020
5	4	AF-SS01-CIV-SSD-0083	1	05-B92-4-883	S275JR	S275JR	5	4	FW	N/A	SW-069	24	10/2/2020
6	5	AF-SS01-CIV-SSD-0083	1	05-B92-4-883	S275JR	S275JR	3	5	FW	N/A	SW-069	24	10/2/2020
Sheet1

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

The problem is, I havea lot of same Word documents nealy 4500 and they are in the same conditio, I just want create DataBase fast . To conver them into PDF it take so much time.
My have 1 day to create that. I saw a lot of videos on youtube but they are usless. I need another option.

 

[alansidman]

Good Luck. The only thing I can envision would be some VBA to loop through all 4500 docs (should all be in the same folder) and convert them to .pdf or .html files. Then use PQ to gang load them and combine them into one big file. You could have an issue though with row availabilty.

You may want to consider bringing them into Access instead. Access can accept Text files (not word). Again this would require you to convert your documents and write some code to load them all. There is no row limitation there, but a 2 Gig limitation.

This is the best I can offer. Maybe someone else can provide some alternative solutions. One day for a project like this is very constricting.