+ Reply to Thread
Results 1 to 19 of 19

Local web form to excel spreadsheet

  1. #1
    Registered User
    Join Date
    11-30-2011
    Location
    West Yorkshire, England
    MS-Off Ver
    Excel 2003
    Posts
    14

    Local web form to excel spreadsheet

    Ladies and gentlemen of Excel forum,

    I have been tasked with the creation of a web form that will be hosted on our local network that automatically updates a shared workbook.

    The web form is extremely simple no more than 7 input fields, however one input field does need to have a drop down box where the options can be altered via the target spreadsheet.

    I envisage the target spreadsheet to have two worksheets, one for the content and the other to define the values.

    I gladly welcome any suggestions.
    Last edited by ChrisSoulier; 01-09-2012 at 10:05 AM. Reason: Solved! (Thanks to Kyle123 once again)

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,180

    Re: Local web form to excel spreadsheet

    hi Chris,

    Have you made the web form? How are you updating the spreadsheet from it?

  3. #3
    Registered User
    Join Date
    11-30-2011
    Location
    West Yorkshire, England
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Local web form to excel spreadsheet

    Hi Kyle123,

    Thanks again for such a swift response.

    I haven't made the web form yet.

    The current set-up is that I update the target spreadsheet manually, with each line of information sent to me by email from nearly 20 different sources. My vision is that everyone in my team has access to a form via Internet Explorer/Firefox where they can fill out the information that they would normally email to me for it to automatically populate in excel.

    I have uploaded an example of the 'ITEM TRACKER.xls' (target spreadsheet), and the 'ITEMTRACKER-Web form.jpg' (web form preview screenshot) this is not the final version but it should give a much clearer idea of my goal.
    Attached Images Attached Images
    Attached Files Attached Files

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,180

    Re: Local web form to excel spreadsheet

    Ah ok, is this an infopath/sharepoint combo?

    If so, not really my forte, I've never used infopath and my sharepoint is somewhat limited at best.

    Sorry

  5. #5
    Registered User
    Join Date
    11-30-2011
    Location
    West Yorkshire, England
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Local web form to excel spreadsheet

    I don't believe so. If you have any ideas how you would set up a solution to this problem I think it would be a great starting block, then I can work from there to see if it works in my environment.

    Let's say, how would I do this just on my personal computer?

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Local web form to excel spreadsheet

    I think you would normally have some form of database on the web site.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  7. #7
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,180

    Re: Local web form to excel spreadsheet

    If you were set on having this as a webform, then it can be done quite simply....... but

    1. All users must be running Internet Explorer
    2. All users must have access to the spreadsheet
    3. It's generally not a recommended approach.....

    If you can't meet criteria 1 & 2, you either need to utilise sharepoint or you need a server side solution and that feels overkill for what you are doing.

    I've attached 2 docs, the first is an html document, the second is the Excel Database, you'll need to save this somewhere and then edit the path in the html document to reflect where it is saved. (you can use notepad for this)

    This should get you up and running, I've written the script in VBscript so it is very similar to Excel VBA.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-30-2011
    Location
    West Yorkshire, England
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Local web form to excel spreadsheet

    Hi Kyle,

    Thank you, that's brilliant. Only issue is that each entry populates on line 2 of the spreadsheet, and overwrites what has already been input.

    Is there a simple change I can make to the VBscript to locate the next available clear line?

  9. #9
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,180

    Re: Local web form to excel spreadsheet

    Try that:

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    11-30-2011
    Location
    West Yorkshire, England
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Local web form to excel spreadsheet

    Hi Kyle,

    I'm afraid that I can't get that bit to work. If I try it with a blank 'SampleDB' it doesn't insert anything, and if I try it with a 'SampleDB' with something already in it nothing changes either.

  11. #11
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,180

    Re: Local web form to excel spreadsheet

    hmmm works for me. Try it with the sample I uploaded, it needs to have 4 column headers.

    Just a quick one, you've refreshed the browser after saving your changes right?

  12. #12
    Registered User
    Join Date
    11-30-2011
    Location
    West Yorkshire, England
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Local web form to excel spreadsheet

    Hi Kyle - Apologies scratch that didn't update the path when I copied in the new code (rookie mistake).

    It seems to be working absolutely fine now. Thank you very much!

    I will be working on testing this with increasing numbers of people to see if we get any conflicts but for now I see this as completely solved. I owe you two now Kyle! Thanks again.

  13. #13
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,180

    Re: Local web form to excel spreadsheet

    no probs.

    Just remember as I mentioned above - this only works on internet explorer.

  14. #14
    Registered User
    Join Date
    06-25-2013
    Location
    michigan
    MS-Off Ver
    Excel 2003
    Posts
    1

    Post Re: Local web form to excel spreadsheet

    please help with html for and getting it to communicate with excel...

    or guide me to step-by-step, how-to.... thanks

  15. #15
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,162

    Re: Local web form to excel spreadsheet

    Whosmyqt,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  16. #16
    Registered User
    Join Date
    08-02-2013
    Location
    Utah, USA
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Local web form to excel spreadsheet

    Try this http://blog.caspio.com/tech_tips/for...ta-into-excel/.
    They have ways to get web form data into Excel.
    Hope it helps

  17. #17
    Registered User
    Join Date
    04-03-2014
    Location
    us
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Local web form to excel spreadsheet

    Quote Originally Posted by Kyle123 View Post
    Try that:

    Please Login or Register  to view this content.


    Kyle, Could you give us an example of using radio buttons and check boxes with this code?

    Thanks!

  18. #18
    Registered User
    Join Date
    04-01-2017
    Location
    florence
    MS-Off Ver
    office 2016
    Posts
    1

    Re: Local web form to excel spreadsheet

    I have a similar task as well. Ive created the sheet, however using the sample you suppled may not work.could you assist?

  19. #19
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,180

    Re: Local web form to excel spreadsheet

    What doesn't work? Did you read the restrictions of this that I posted above?

    Ps you need to start your own thread for this - we can't help you here

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1