# Linking a form to an Excel file



## jstevenson06 (Nov 7, 2005)

I am hoping someone on this board can help me with a new project I am heading up.

I need to create some kind of form (either in Adobe, Word, Excel, etc.) that, after the fields were filled out and submitted, the data would update a dynamically linked Excel file.

For example, my form might have three fields: Name, Service and Request. My Excel file would also have three columns titled Name, Service and Request. When a user filled out the form (entering their name, their service and their request) and they hit submit, those fields from their form would auto fill into the Excel spreadsheet.

Does that make sense? Is this even possible? Any guidance or help you can provide would be greatly appreciated.


----------



## OBP (Mar 8, 2005)

Yes this is quite possible, it would obviously be simpler if you just used Excel with an Excel Form.
Although you could use Word or Access for the Form, or Access for the whole thing.


----------



## jstevenson06 (Nov 7, 2005)

It's relief to hear that this can be done.

I can definitely create the form in Excel and I know how to do this. But I don't know how to link the form to the Excel file so that it will update whenever a user fills out the form.

Can anyone provide me some direction here?

Thanks again for your help.


----------



## jimr381 (Jul 20, 2007)

Which version of Excel are you using and what do you think of as a form?


----------



## jstevenson06 (Nov 7, 2005)

I am using Excel 2003. I have attached a very quick form that I put together just for testing purposes. I basically used Microsoft's Petty Cash Template (downloaded from their site) and changed the fields. Is this the right kind of form to be using?


----------



## OBP (Mar 8, 2005)

You create the Excel sheet and the Form and I expect one of the Excel gurus will create some VBA for you.


----------



## jimr381 (Jul 20, 2007)

Have you looked into "Data">>"Form" from the menu bar. What you posted was moreso like a report or something you would use for a print out. You could also make an Access database with a form and have excel do a query to pull data from the Access database. Why are you using Access instead of excel?


----------



## bomb #21 (Jul 1, 2005)

I'm stuck on this bit:

When a user filled out the form (entering their name, their service and their request) and they hit submit, those fields from their form would auto fill into the Excel spreadsheet.

I assume there are multiple users. What do you mean by "they hit submit"? Do you mean a button is pressed & the request details then update in a separate workbook somewhere central somehow? (on a network drive, perhaps?)


----------



## jimr381 (Jul 20, 2007)

I am still leaning towards him really needing a simple access database.


----------



## OBP (Mar 8, 2005)

:up:


----------



## slurpee55 (Oct 20, 2004)

Well, boys, we all would use Access (  ) , but, really, there are people in this world who don't/can't/refuse to use it.
This can be done in Excel - say, create a pop-up form for one sheet which sends the data to another worksheet, although I spent about 3 hours this morning (home sick, nothing else to do) trying to find a good example with no luck. But that's why we have bomb and rollin and firefytr.
Actually there is one thing I found that rollin did here:
http://forums.techguy.org/business-applications/633424-excel-input-sheet.html
- it is called transfer with form.xls. 
Deleting one line of code makes the form stay visible, and it is easy to change where the data goes from A1, A2 and A3 to anothersheet!A1, anothersheet!B1 and anothersheet!C1, but how do you make it go to the next row?


----------



## Zack Barresse (Jul 25, 2004)

Of course this can be done. And yes, it sounds like Access would be the way to go. What we would need to know regardless of the application used is the entire scope of the work, which I have not seen a clear definition of. _What_ users? _Where_ would these users be? The same network? The same computer? The same _file_? Where exactly would you like the data to go? Would you like verification of duplication or some sort of redundancy check(s)? Will this only be you on one computer? Will the users be filling this out online? By submitting, as bomb asked, what is entailed in that exactly? What should happen when a user 'submits' something??


----------



## bigbird9 (Oct 23, 2007)

Try this site. I wanted to do the same thing a few weeks ago. With no visual basic expierence, I carefully changed the examples headers to my own.

http://www.contextures.on.ca/xlUserForm01.html


----------



## Zack Barresse (Jul 25, 2004)

@ bigbird9: How does a userform solve the OPs problem?


----------



## bigbird9 (Oct 23, 2007)

OPs?


----------



## OBP (Mar 8, 2005)

Original Poster


----------



## bigbird9 (Oct 23, 2007)

Ah! JStevenson asked for: "I need to create some kind of form (either in Adobe, Word, Excel, etc.) that, after the fields were filled out and submitted, the data would update a dynamically linked Excel file." Isn't this a user form?


----------



## Zack Barresse (Jul 25, 2004)

bigbird9 said:


> Ah! JStevenson asked for: "I need to create some kind of form (either in Adobe, Word, Excel, etc.) that, after the fields were filled out and submitted, the data would update a dynamically linked Excel file." Isn't this a user form?


Yes, yes it does. Thank you for the clarification. I was actually thinking the OP (indeed, Original Poster,  ) was thinking of something else. We should wait and see what they want.


----------



## slurpee55 (Oct 20, 2004)

Yeah, anybody heard from the OP lately? I want to see how this works out. 
Interestedly (is that a word?)....Loche


----------



## bomb #21 (Jul 1, 2005)

slurpee55 said:


> Yeah, anybody heard from the OP lately? I want to see how this works out.
> Interestedly (is that a word?)....Loche


It is now.


----------



## slurpee55 (Oct 20, 2004)

This coming from someone who quotes Father Ted?!? Not sure about that


----------



## Zack Barresse (Jul 25, 2004)

We've never been sure about Andy. 

Okay, /hijack.


----------



## jstevenson06 (Nov 7, 2005)

Thank you for all of the repsones. My apologies for the delay as I've been out of the office.

Let me try to add some detail to my OP.

First of all, the folks who will use the resulting file do not want to use Access, even though I know this would be the easiest solution.

We have users in various offices all over the world who all have access to the network drive where the excel spreadsheet file is stored. What I need to do is create a form (using Adobe, Excel, HTML, I'm not sure?) for these users to fill out when they have a service request. There will be about 15 fields or so on this form and each field would need to be populated by the user. 

Once the form is filled out, the user could hit a submit button (or something equivilent) and the data they had filled in on the form would auto-fill an Excel spreadsheet I mentioned above (the column headings would correspond to the fields on the form) which is stored on a network drive. In other words, I'd like the data that is entered in the form by the user "to go" into my Excel file.

Is this feasibile and if so, how? Please let me know if you have more questions. I appreciate everyone's attention and help on this.


----------



## Rollin_Again (Sep 4, 2003)

What you are wanting can easily be done. Do you want the entry form to be displayed by clicking on a stand alone executable or do you want the form to be embedded inside another Excel Workbook or other Office application? A stand alone executable shouldn't be a problem and I can help you create it using Visual Basic but once the program is compiled into an *.exe* file you will not be able to make changes without re-compiling the program. You are probably better of embedding the form inside another Workbook and having it displayed automatically when the workbook is opened.

Will the network drive be mapped the same way on each users PC?

Regards,
Rollin


----------



## jstevenson06 (Nov 7, 2005)

I would think embedding the form into either Excel or another Office app would be our best bet. I'm sure changes will need to be made to it every now and then so I don't want to be tied to any one format. Can you help me create this?

And yes, the network drive is mapped the same way on all of the user's machines.

Thanks again.


----------



## Zack Barresse (Jul 25, 2004)

Rollin, I'm thinking an add-in stored on the network drive, installed on each machine (not copied from the network when installed), update information on server, either into separate files (i.e. txt, xls) if the possibility of multiple users logging on at once, or into a singular file. I'll hold off if you're working on this. Let me know if you need a hand with anything.


----------

