Hey guy's im new here and my second question here starts off quite difficult.
My programs : Office 2010
My skills: limited ( i've been searching the web for quite some time now and leand a view things from what i've read)
For my work i want to create ( i was asked to do ) an database of machine testing specs.
They have 200/300 word documents with an table in it with the specs.
I want so import this info from the word document into an excel spreadsheet. ( easy data comparison and so on )
I've already asked the question how to automate the proces of exporting the data from word to an excel file ( in the word
and excel help forum )
I think those guys can help me, but you guy's to ( its more excel then word vba ) so i'm asking the same here.
I've got an working macro for data extraction form the word talbe in to excel via VBA.
'Imports cells (3,2) and (4,2) from Word document Tables 1-10
Dim wdDoc As Word.Document
Dim wdFileName As Variant
Dim TableNo As Integer 'number of tables in Word doc
Dim iTable As Integer 'table number index
Dim iRow As Long 'row index in Excel
Dim iCol As Integer 'column index in Excel
wdFileName = Application.GetOpenFilename("Word files (*.doc*),*.doc*", , _
"Browse for file containing table to be imported")
If wdFileName = False Then Exit Sub '(user cancelled import file browser)
Set wdDoc = GetObject(wdFileName) 'open Word file
TableNo = wdDoc.tables.Count
If TableNo = 0 Then
MsgBox "This document contains no tables", _
vbExclamation, "Import Word Table"
ElseIf TableNo > 10 Then
TableNo = 10
'Else TableNo is actual number of tables between 1 and 9
Range("A1") = "Table #"
Range("B1") = "Cell (3,2)"
Range("C1") = "Cell (4,2)"
For iTable = 1 To TableNo
'copy cell contents from Word table cells to Excel cells in column B and C
Cells(iTable + 1, "A") = iTable
Cells(iTable + 1, "B") = WorksheetFunction.Clean(.cell(3, 2).Range.Text)
Cells(iTable + 1, "C") = WorksheetFunction.Clean(.cell(4, 2).Range.Text)
Set wdDoc = Nothing
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This macro works for me, but it will only get the data from 1 file ( via selection ) ,
I want it to do so automaticly ( and for later document to ) and i thought that i've it save the data to an single excel file
is the best thing.
So: The extracted data from 1 word file is saved in 1 excel file.
And then later on
i import the data from all of my excel files in to 1 master (database) excel file.
Preferbly via VBA Macro
Since i've not used macros or vba before it thought that was the best for me. And easyer
And dont get it all in one file with a huge macro wich is difficult for me to understand multiple steps.
already made an seperate excel file with the cell names that contains the data i need.
And made an range (with titles i want to have)
So i can copy it into the macro ( easy )
The code above works, and when i copy my ranges and cell info it works.
But give's me an error on empty ( there i dont have the basic knowlegde to overwrite that )
I know it has something to do with the cleaning cell option. But dont have the knowlege ( also didnt have the time to find
out ) how to overcome that.
For you it sure will be an easy task.
I hope i've been clear enough of what i want, i've not then please ask.
Thanks in advance!!!
Edit: The macro contains a fault,, i've changed the first line :Dim wdDoc
and changed Word.Document to Object to make it working