Populating Excel from Word - Help! 4

[yodandy]

So for my internship that started monday at a local college, I told them im not really into programming, more of the MIS networking type stuff

so the first thing they have me do is a coding assignment, sigh

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 frickin 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? I took a good 4 hours today just brushing up on VB, and she wants it done in a few weeks, but I think I can get it done a little sooner, idk...

 

[Bong]

Your best bet is to turn on the Macro Recorder in the Word document and do some of the steps (even all). Then see what the produced code looks like (Alt-F11). Chances are good that you'll see what you need to do.

_________________
Bob Rashkin

 

[WinblowsME]

Here's something for you to play with.

' Controls on Word Document:
'    ----------------------------
'    Control Type -> Control Name
'    ----------------------------
'    Textbox1     -> txtFirstName
'    Textbox2     -> txtLastName
'    Button       -> cmdSend

Private Sub cmdSend_Click()
   Dim xl_app As Object, xl_wb As Object, xl_sheet As Object

   Set xl_app = CreateObject("Excel.Application")
   Set xl_wb = xl_app.Workbooks.Add
   Set xl_sheet = xl_wb.Worksheets(1)
   
   xl_sheet.Cells(1, 1) = txtFirstName
   xl_sheet.Cells(1, 2) = txtLastName

   xl_app.Visible = True   
   'xl_app.DisplayAlerts = False
   'xl_wb.SaveAs ("C:\Documents and Settings\WinblowsME\Desktop\TEST.xls")
   'xl_wb.Close
   'xl_app.Quit
   
   'Set xl_sheet = Nothing
   'Set xl_wb = Nothing
   'Set xl_app = Nothing
End Sub

 

[fumei]

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

Started doing what?????

WinblowsME has given something, but it assumes you are using two textboxes, and a commandbutton. That is, you are using ActiveX controls.

Are you? Will you? You state nothing at all on what you may be doing, or wanting to do.

"fill out a word document" - that is meaningless. Fill out by:

- typing as paragraphs?
- typing into table cells?
- typing into formfields?
- typing into ActiveX controls (as WinblowsME has)?
- are you using bookmarks?

Give us more of an idea of what you are doing please. WHERE, and in WHAT form, is the stuff in Word?

faq219-2884

Gerry
My paintings and sculpture

 

[mintjulep]

And why are you entering information in a Word doc if it needs to go into an Excel spreadsheat?

 

[fumei]

Hi mintjulep, I think I can possibly answer that, although the OP may give other reasons.

People often want electronic actioning forms to look like their paper forms. So they quite often try and make their paper forms (Word documents) have electronic functionality.

I have seen this many many times.

faq219-2884

Gerry
My paintings and sculpture

 

[SkipVought]

We REALLY need to know what your objective is.

The term Form means different things to different people.

In VBA sense, it means a Control Form (Userform).
In Word sense, it means a document that has Forms controls for data entry and all other text is LOCKED.
Or it might mean something else.

Could users enter data, to be used in various tyes of documents, in a TABLE in some other application like Excel? If so, then the documents could be popluated using the MailMerge feature. I did this many years ago in order to populate an assortment of personalized children's books.

Skip,

_[red][/red]

 

[yodandy]

Im doing this for a small branch of the college who are older, and used to doing their work order forms via paper. """People often want electronic actioning forms to look like their paper forms. So they quite often try and make their paper forms (Word documents) have electronic functionality."""

Bingo...thats why its being filled out in word first, so whoever goes to fill a new one out, isnt bombarded with excel in their face right away, since the majority of them are physical plant workers who probably have never seen excel in their life.

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...

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

Thanks for the help so far! Let me know if im still being too vague...

 

[SkipVought]

Hi,

You could FORMAT an Excel Sheet to look EXACTLY like the Word Document (using merge cells for NON-DATA ENTRY cells). It took me about 10 minutes to get the sheet 85% there VISUALLY.

Doing it ALL in a single application will greatly resuce the complexity.

You'll want to have a Button to ADD the data to Sheet1.

HOWEVER, there is a caveat to consider. Normally data entry application like this have FOUR basic features...
[tt]
Inquiry - Show me what's in the database (sheet1) bases in one or more key values
Add - Add a row to the database (sheet1)
Change - Based on the Inquiry (current row in the database), change one or more non-key values in the row.
Delete - Based on the Inquiry (current row in the database), delete or make unavaiable the row.
[/tt]
This is not a tirvial task. Think about it, rather than, "fugeddaboudit!" ;-)


Skip,

_[red][/red]

 

[fumei]

This is not a tirvial task.

Really????

Oooooooooh. Tirvial sounds complicated.


Just kidding Skip. yodandy, Skip is absolutely correct though. It is one thing to have something look a specific way, it is quite another to make it work a specific way.

faq219-2884

Gerry
My paintings and sculpture

 

[SkipVought]

Hear about the dyslexic, agnostic, insomniac?

Layed awake all night wondering...

"Is there really a dog?"

Skip,

_[red][/red]

 

[SkipVought]

It would be like a car with ONE forward gear.

Pretty much useless!

Skip,

_[red][/red]

 

[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...causing all kinds of problems

I know this is all probably super easy to you, and probably frustrating as there are MUCH easier ways to doit, but as of now, I have to continue down this route...any nudges in the right direction would be useful

 

[fumei]

Try using GetObject first, to get Excel. That way if there is an instance of Excel open, you will use that. If you get an error with GetObject, then you use CreateObject.

Also, you need to properly close (quit) Excel, then destroy the Excel object (if that is what you want to do). In your code above you create the instance, but you never destroy it.

faq219-2884

Gerry
My paintings and sculpture

 

[SkipVought]

"...but she said this shouldnt be that difficult..."

hehehehehehehehehehe........

TBC......

Skip,

_[red][/red]

 

[fumei]

Now Skip, be nice and let other kids play with your toys. It is cruel to whack people over the head with reality.

Ummm

but she said this shouldnt be that difficult...

I sympathize, but I tend to say to people who tell me that...then YOU do it. Or..can you give actual empirical evidence for that statement?

In any case, try using GetObject, and then with some error handling, you can switch to CreateObject if required. Be sure to properly close, quit and destroy the approriate objects. Try doing a search for this. There are lots of examples.

Good luck.

faq219-2884

Gerry
My paintings and sculpture

 

[SkipVought]

Oh, I know.

Just calculating the probabliity......Hmmmmmmmmmmmmmmmm????

Skip,

_[red][/red]

 

[fumei]

You crack me up.

faq219-2884

Gerry
My paintings and sculpture

 

[PHV]

excel instances stay open even after I close them
Use FULL qualified excel objects, eg replace this:
lastrow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
with either this:
lastrow = xlApp.ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
Or this:
lastrow = xlSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[yodandy]

Thanks PHV, i actually tried that about an hour ago and checked your post just now, wish I would have seen it sooner

thanks everyone for the help! im done w/this part, but im sure ill be back for more!

thanks again!