• Some users have recently had their accounts hijacked. It seems that the now defunct EVGA forums might have compromised your password there and seems many are using the same PW here. We would suggest you UPDATE YOUR PASSWORD and TURN ON 2FA for your account here to further secure it. None of the compromised accounts had 2FA turned on.
    Once you have enabled 2FA, your account will be updated soon to show a badge, letting other members know that you use 2FA to protect your account. This should be beneficial for everyone that uses FSFT.

Populating Excel with data from Word - help!

Y

yodandy

n00b
Joined
Jan 13, 2006
Messages
35
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
 
It might not be the answer you want, but a better method might be to use PDF files and then submit the data into a MySQL database.

I don't know what all the forms are, but the one almost looks like a support desk ticket type of thing... If you didn't mind web only something like hesk or another support desk package might work better.

This site might prove helpful in creating a solution with pdf files http://www.planetpdf.com/

Enjoy
AMDbuilder
 
I'm not sure about hitting a Word doc as a datasource, but I've done plenty of reading/writing XLS files using ODBC calls and Access-like SQL calls. It's very hokie, and can get frustrating when you're troubleshooting through various cell datatypes, actual stored field data, etc.

I'd *strongly* recommend using an application (web likely) to have the users submit data. You can do data/field checks a lot easier than trying to write the application to anticipate anything that the user would submit. Dumping the data to a DB would be ideal, and you can then pull whatever you want into XLS, CSV, and just about any other form or report page.
 
thanks for the suggestions, but she really does need it done in Office's VBA language, its not a big college by any means, so it wont get that huge as far as the spreadsheet goes, they are just used to office and yea she needs it done in vba

and vba experts who can pass on their knowledge here?
 
So, what are you allowed to do this in (Excel, Word, Access)? I'm not sure if you're sticking with them using Word and pushing data to an Excel file (I've done the reverse before, it's not very pretty). If you can, I'd do it all in Excel so you can create the interface and data all in the same application, rather than having to pass it between something like Word to Excel. I'd have to look into it, but I think it is possible to create an Excel object in Word to create/write data to a Workbook.
 
You see... the problem you have here is that Word is a word processor and Excel is a spreadsheet. You're trying to use Excel as a database and then have Word as a front-end for that. This is the Wrong Way of solving the problem - that somebody started doing it this way in the past is no excuse to continue down this path. There's a very good reason they didn't finish - anyone stupid enough to start a project this way would be nowhere near smart enough to make it work.

If you must use MS Office for this, scrap Excel & Word and use Access - at least it's intended to do the job.
 
Well, my supervisor said if it was too difficult/stupid to do in excel, access would be ok. However, she still wants to be able to use this certain form as a .doc, so they can grab this .doc off the server, fill it out, email it to the head, and they can click a button on the access DB they have on their end, and push certain info from word it into the DB.

Im sure im being too vague, but let me know if this is even possible...
 
yodandy said:
However, she still wants to be able to use this certain form as a .doc, so they can grab this .doc off the server, fill it out, email it to the head, and they can click a button on the access DB they have on their end, and push certain info from word it into the DB.
Click to expand...
So this application is supposed to have two layers of administration - content pushers, and content approvers. Add some security and user rights to the list, and it becomes more apparent that there are some deeper business rules that a pure Word/Excel/Access solution does not make sense.

It sounds more like the backend technology decisions are being made based purely on a few front-end desired features. And the more I read about the flow and requirements, the more I think a separate app and DB is the way to go.

Perhaps a public-facing submittal form (webpage) for general users, and a sub-folder that checks user accounts for access which houses the admin pages. Page rights could alternatively/additionally be stored in the DB, and checked on page load to ensure users only see/do what they are allowed to. Features, such as "Exporting data to Word/Excel", could be added to the app (perhaps clicking a button that would prompt the user with a standard "Save As" dialog box). Who knows, just thinking in text here.

Users could still use Word/XLS files for passing info on what data to change/shift/adjust in the app's interface, but the backend is not dependent on the version of Office that everyone is running.

Some scenarios that you will gain or can be avoided are:
- Users having direct access to the database itself (ie: "Oops, can you please undelete that Access file on the server?")
- Quirks related to the specific version of Office being used at each client's machine
- Feature-adds and new business rules
- Schedule reports to be regularly generated (such as weekly analysis reports being automatically e-mailed to someone)
- Open up the data to be made available to other applications

I hope this helps!!
 
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...


Code: 
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 :)
 
You're not killing the objects before you end the sub, so they stay in limbo.

Add this to the bottom of your sub:

Code: 
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
 
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!
 
You must log in or register to reply here.
Back
Top