Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Word form to collect data for Excel spreadsheet

    A month or more ago, I started a thread in the General Productivity forum: http://windowssecrets.com/forums/sho...small-business). I got lots of good information and suggestions about how to introduce some modern automation into a small, old-fashioned bakery business. The owner, his manager, and I have spent a few hours discussing the possibilities. Right now, they're collecting a small amount of data about special orders in an Excel spreadsheet. (That's a step up from the sticky-note pads they once used!) They use Excel online to collect basic information: Customer's name (FName, LName), phone number, items ordered. Pretty simple stuff.

    Just this week I learned that what they also want is a printable form for each order. They want to print these forms four pages to a standard US Letter sheet of paper. That size will be ideal for these small items. They will cut apart the four small forms and tape the form to the appropriate order. Thus, this form will become a label to identify the customer and the finished product.

    Now, about this "form"I've built a data-collection in Word 365 (or 2013). The form has fields to capture the customer's first and last name, phone number, and email address; date order taken and date promised; and text fields for up to five special-order items.

    Here's what I hope to do
    • Link this data to columns on a paired Excel spreadsheet.
    • Enable printing the Word data-collection forms as described above.


    First question: Is this even possible? Here's the scenario: The employee enters the data for a new order in the Word form, then clicks or touches a control that executes code to send the order as a new record to the spreadsheet. The form now needs to refresh to permit taking another new order. At days's end, let's say we've taken eight new orders, each from a different customer. The orders appear on rows 2 thru 9 on the spreadsheet. (Row 1, of course, is the header row.) Finally, we'll use two sheets of paper to print the eight labels that we'll tape to each order.

    I know I'll need code to effect all this (if it can be done at all), but I'm don't know where to begin trying to build such a system. Can anyone here point me in the right direction, please. Of course, I'm open to any alternate ideas.

    FWIW, I've attached the Word data-collection form.
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Caesar,

    Why not just build a form right in Excel to collect the data and place it in the database. You can easily have a "Report Sheet" in Excel that will print out what you want from the Selected Record with the push of a button. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    Thanks, RG. I'm looking for anything that will work, and simpler is better. A "Report Sheet" is a totally new thing to me. I'll need to read up on all that—looks very interesting!

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Ceaser,

    I agree with RG; Excel is the way to go especially when you already have your data tucked into a spreadsheet. Here is a workbook that will do what you want to do. The Order sheet has the orders with the fields you specified (image 1). Click on the "Create Forms" button and the forms will be created on the "Form" sheet (image 1). The forms are sized to fit 4 on a sheet of paper and outlined for easy trimming. The spreadsheet will handle up to 16 orders at a time but can be expanded if needed. The form sheet also has a clear button for your convenience.

    HTH,
    Maud

    Image 1
    Ceaser1.png

    Image 2
    Ceaser2.png

    Code:
    Public Sub CreateForm()
    '--------------------------------------
    'DECLARE AND SET VARIABLES
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("Order Sheet")
    Set ws2 = Worksheets("Form")
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    If LastRow > 17 Then LastRow = 17
    Ticket = 1
    Row = 4
    col = 2
    '--------------------------------------
    'CREATE FORM
    For I = 2 To LastRow
        For J = 1 To 18
            ws2.Cells(Row, col) = ws1.Cells(I, J):: Row = Row + 1
        Next J
    '--------------------------------------
    'DETERMINE LOCATION OF NEXT FORM
        Ticket = Ticket + 1
        Select Case Ticket Mod 2
            Case 1
                col = 2
                Row = Row + 3
            Case 0
                col = 5
                Row = Row - 18
        End Select
    Next I
    End Sub
    Attached Files Attached Files

  5. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    Caesar3 (2015-04-27),RetiredGeek (2015-04-24)

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    Thanks for picking up the "Heavy Lifting"!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 652 Times in 594 Posts
    "My back is a disaster area", Dr. Zachery Smith, Lost in Space, 1964

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Danger, Will Robinson, Danger!
    RobbieTheRobot.jpg
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    In this Forum, no one can hear you scream.

  10. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Zeddy,

    Pop your ear out the window. It's only 8,000 miles+ between us and unfortunately, recent events show that I have a big mouth!

  11. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    Maud, I can't thank you enough—this is absolutely wonderful! I'd like to shake your hand, but we're two states apart! I apologize for not responding sooner—I was distracted by other events.

    I hope you won't mind if I come back with questions about the sheet and the code. I've used VBA several times, but always with help from people like you. My first question: Let's say it's a new day, and the employee gets six new orders. Now the Order Sheet has 14 records. At the end of the day, we want to print forms for just those six new orders. If we click on the Create Forms button, we'll create forms for all 14 orders. How can we restrict the form creation to just the six new records? This question ties into the following.

    We do want to retain a record of all orders. My project addresses a long-standing problem. As the holiday season approaches, every year the client can expect a regular customer to call and ask, "What did I order last year?" To date, the business has taken orders on hand-written lists. (They've even used sticky notes!) Of course, such records are difficult to maintain and hard to refer to. We could keep all records on the Order Sheet, but that will become increasingly awkward over time. Perhaps we could solve the problem I raised in the previous paragraph by moving and appending "old" records to a third worksheet (Sheet 3) for archiving ... ?

    A quick question: Why is column H hidden on the Order Sheet?
    Last edited by Caesar3; 2015-04-27 at 22:25.

  12. #11
    Lounger
    Join Date
    Feb 2011
    Posts
    28
    Thanks
    0
    Thanked 10 Times in 8 Posts
    Rather than creating the forms on the fly, you could just restrict the print area of the Forms sheet by adding some code like this before the End Sub in the CreateForm macro:
    Code:
    '--------------------------------------
    'SET PRINT AREA
        FormsToPrint = Round(LastRow / 2, 0)
        ws2.PageSetup.PrintArea = "$A$1:$F$" & FormsToPrint * 21
    having declared FormsToPrint = 1 in the first section.

  13. The Following User Says Thank You to unclehewie For This Useful Post:

    Maudibe (2015-04-28)

  14. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Ceasar,

    Good question about column H. If you look at the form, starting at first name and counting down, you will notice that the line with "Order information" is the 8th line. On the Order sheet, column H is the 8th column. So when the data is read across each record, it is taking the data in the column sequentially and writing to the rows sequentially. Column H is blank and it writes nothing to the cell adjacent to the Order information. Now , you can unhide it if you like and put something in that column and it will write to the order information line if you like.

    What you want to do with creating forms using records that have been appended can easily be done. I will make some adjustments. Also, to keep a record grouping the clients together on a third page, I will apply a class that I wrote and posted in a previous thread. I have some great ideas that will add a few bells and whistles to the project. Give me a couple of days to complete it.

    Maud

  15. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Unclehewie,

    You make a good point of selecting which forms to print. I was looking at it from the point of view by selecting which records to send to the forms. I will incorporate your thoughts into the revision.

    Thanks,
    Maud

  16. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    Maud, I've skimmed the post that you linked in your most recent post. I've been exposed to words such as module and class, but I don't fully understand what's going on there. I'll make time to study that post later. Meantime, I'm at the place of business this morning to put your current item to work. The client hasn't seen it yet, but I'm sure they're going to like it. I'll really look forward to seeing (hearing?) the bells and whistles! Thank you!

  17. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Caeser,

    Attached is the revised workbook. Added features are:

    1. Form Creation: You can control the number of forms to be created by selecting a number of records (<=16) by highlighting the items in any column. For example highlighting 10 consecutive last names then clicking the Create Forms button will create forms for only those records in the selection.

    2. Time Stamping: the record will be stamped with the date that the form was created.

    3. Form Print button: Following Unclehewie's lead, only the completed forms will be printed.

    4. Group Log sheet that will group by last name, order date, or employee. Selection is made by radio buttons and will auto update.

    Hope this meets your requirements.

    Maud

    Ceaser3.png
    Attached Files Attached Files

  18. The Following User Says Thank You to Maudibe For This Useful Post:

    Caesar3 (2015-05-01)

Page 1 of 2 12 LastLast

Posting Permissions

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