Populating Excel with data from Word - help!

[yodandy]

So for my internship that started monday at a local college, basically, im responsible for electronic-ifiying the paper work forms

They would fill out an word document, and i'd use some script / macro to populate certain cells in excel

Only programming experience i have is self taught HTML, and Intro to VB.net

Anyway, any code snippets that could help me out and get me started?

If it helps, here are the two documents

I need the forms from the word doc that are grey, to be inserted in the corresponding cells of the excel document, time and time again...(ive already got the VBA project in excel somewhat started, ha, barely)

http://www.mediafire.com/?f3nyyytdojd

Any help would be appreciated

[Ecniv]

If your users filli in the default text with the data, then you could use something like :

debug.print ActiveDocument.Fields(2).Result

Where fields(2) is the second field from the top.

If they over write it and erase it, you have no chance.


An alternative is some blank rows underneath each option, coloured so the user enters there. Then you'd need to reference the table and cells.

?activedocument.Tables(1).Cell(2,1).Range

This gives the whole range in that cell. This includes the return/carriage return/paragraph marker.

Which do you want to use and can you try to use them first, posting up your vba code.

[yodandy]

Alright, I appreciate all of the responses, but again, i LOVE the suggestions, but this project isnt that big, and its just a few dudes who want it this way, I asked her If i could do it another way, but she said this shouldnt be that difficult...

Instead of having excel take it from word, im just having word send it to excel...

This is what I came up with today...

Private Sub cmdSend_Click()

'Dim
Dim i As Integer
Dim lastrow As Integer
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook

        Set xlApp = CreateObject("Excel.Application")
        Set xlBook = xlApp.Workbooks.Open("C:\test.xls")
        Set xlSheet = xlBook.Worksheets(1)


                    
    'Selects first empty cell
    lastrow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    i = lastrow + 1

   'Adds data from .doc to first empty cell and corresponding cells
   xlSheet.Cells(i, 1) = txtWO
   xlSheet.Cells(i, 2) = txtDept
   xlSheet.Cells(i, 3) = txtReqDate
   xlSheet.Cells(i, 4) = txtReqBy
   xlSheet.Cells(i, 5) = txtDivChair
   xlSheet.Cells(i, 6) = txtContact
   xlSheet.Cells(i, 7) = txtDesc
   
   
        
 xlApp.Visible = True

End Sub

That takes the fields I want from word, Opens excel, adds them to the next blank row in excel, GOOD! yay

Im having trouble when excel is OPEN already with the workbook activated having it add the data, also, excel instances stay open even after I close them...any nudges in the right direction would be useful

[Ecniv]

Hi,

Well if you are in excel you could have done the form in excel too - save you loadsa trouble



I see in the code you are opening excel and making it visible, but you haven't cleared off your references to the application.

So you may want to :

'---- clear up references used
        Set xlSheet = nothing
        Set xlBook = nothing
        Set xlApp = nothing

Instead of creating it, assuming you may already have the sheet/excel open you can incorporate a check before.

'---- turn off error checking
'---- if an error occurs then there is no excel application open
'---- so create one and clear the error
'---- turn default error handling on
on error resume next
set xlApp = GetObject("Excel.Application")
if err.number<>0 then
    Set xlApp = CreateObject("Excel.Application")
    err.clear
end if
on error goto 0

When you have the application, you can loop through the open workbooks (collection) to see if the one you want to open already is open. I'll let you figure that out Then you can set the sheet to the sheet you wish as you are doing atm.

[yodandy]

Thanks for the help everyone, ive finished this part!

my major problem was not having this

lastrow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
replaced with this
lastrow = xlApp.ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

that was creating a bunch of excel instances and not allowing them to shut down afterwards for some reason...

im sure ill be back for more, thanks again!