1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Populate Excel Form and Word Form from Excel Table

Discussion in 'Business Applications' started by jtraylor, Apr 11, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. jtraylor

    jtraylor Thread Starter

    Joined:
    Apr 11, 2012
    Messages:
    23
    Thanks for the help. You know i have several other forms where I used the VLOOKUP function and it never occred to me to use it in this form. I could have had this working for the last few months, go figure.
     
  2. jtraylor

    jtraylor Thread Starter

    Joined:
    Apr 11, 2012
    Messages:
    23
    cplmckenzie,
    I have a stupid question for you, where do i add the code to delete the worksheet and save it as a new filel? The way that i would set this is to delete the log sheet saving the file with the value of cell A2 for the packing lst as all packing lists are saved by the packing list number.
     
  3. jtraylor

    jtraylor Thread Starter

    Joined:
    Apr 11, 2012
    Messages:
    23
    I have found a sample of code that will allow opening a closed workbook but i do not know where to fit it into the code that we are working with. I also dont know how i would modify the existing code to populate the newly opened workbook instead of the sheet in the current workbook.

    1. 'opening workbook
    Workbooks.Open Filename:="c:\book1.xls"

    'your code

    'your code


    'below code for saving and closing the workbook
    Workbooks("book1.xls").Activate
    ActiveWorkbook.Save
    ActiveWorkbook.Close
     
  4. cplmckenzie

    cplmckenzie

    Joined:
    Apr 5, 2012
    Messages:
    50
    jtraylor,

    sorry for the delay, was in hospital till yesterday.

    That code would go in the 2012 Log code section....
    in particular at the end of the cmdFill Sub.

    The code in it's entirety follows...
    =========================

    Private Sub cmdFill_Click()
    Dim shInv As Worksheet

    Set shInv = ThisWorkbook.Sheets("PackingList")
    If Selection.Column <> 3 Or Selection.Value = "" Then
    MsgBox "There is nothing to export!" & Chr(10) & "Select a cell with data from column C," & Chr(10) & "and try again"
    Exit Sub
    End If
    rw = Selection.row
    With shInv
    .Cells(2, 1).Value = Cells(rw, 3).Value
    .Cells(9, 6).Value = Cells(rw, 4).Value
    .Cells(9, 7).Value = Cells(rw, 5).Value
    .Cells(12, 6).Value = Cells(rw, 6).Value
    .Cells(12, 7).Value = Cells(rw, 7).Value
    .Cells(9, 8).Value = Cells(rw, 8).Value
    .Cells(21, 4).Value = Cells(rw, 13).Value
    .Cells(8, 2).Value = Cells(rw, 14).Value
    .Cells(9, 2).Value = Cells(rw, 15).Value
    .Cells(10, 2).Value = Cells(rw, 16).Value & ", " & Right("00" & Cells(rw, 17).Value, 2)
    .Cells(11, 2).Value = Cells(rw, 18).Value
    .Cells(14, 6).Value = Cells(rw, 19).Value
    End With
    Cells(rw, "c").Select
    shInv.Select

    Worksheets("2012 Log").Delete
    ActiveWorkbook.SaveAs ("PackingListTemplateTEST.xls")

    End Sub

    ====================================================
     
  5. cplmckenzie

    cplmckenzie

    Joined:
    Apr 5, 2012
    Messages:
    50
    jtraylor,

    At this point, in my testing, all necessary worksheets and workbooks.

    So before I respond try testing the just posted cmdfill sub.

    See If all is saved as you want then post again.

    cplmckenzie
     
  6. cplmckenzie

    cplmckenzie

    Joined:
    Apr 5, 2012
    Messages:
    50
    jtraylor,

    ran test again. I believe that instead of using the ....

    ActiveWorkbook.SaveAs ("PackingListTemplateTEST.xls")

    We would programmatically open a VB SaveAs dialog box, which would open a feature like the "Excel Menu Command >File>Save As" giving you the option to save the currently populated packing list by its' packing list number.

    Is that what you are trying to accomplish?
     
  7. jtraylor

    jtraylor Thread Starter

    Joined:
    Apr 11, 2012
    Messages:
    23
    cplmckenzie,
    Thank you for your help. I was able to figure out how to populate my Packing List Template Workbook, from my Packing List Log workbook. If anyone knows how to add some additional code for me to perform a Save As function so that after the row of data is sent to the template, the the code will use the value of Cell A2 as the title of the new document and then save it in a specified file that would be great. Below is the code i am now using if anyone has a need to do the same thing.

    Code:
     
    Private Sub cmdFill_Click()
    Dim shInv As Worksheet
    Dim xNewApp As New Excel.Application
    Dim xNewWB As New Excel.Workbook
    Dim strFile As String
    Set shInv = ThisWorkbook.Sheets("PackingList")
    rw = Selection.row
    Range("C" & rw).Select 'to select column C of current row
        
       If Selection.Column <> 3 Or Selection.Value = "" Then
          MsgBox "There is nothing to export!" & Chr(10) & "Select a cell with data from column C," & Chr(10) & "and try again"
          Exit Sub
       End If
    rw = Selection.row
       '***You must change the file path and extension below as applicable***
       strFile = "c:\Users\Jimmie\Desktop\_Packing List Template.xls"
       Set xNewWB = xNewApp.Workbooks.Open(strFile)
          xNewApp.Sheets("Sheet2").Activate
          xNewApp.ActiveSheet.Cells(2, 1) = Cells(rw, 3).Value
          xNewApp.ActiveSheet.Cells(9, 6) = Cells(rw, 4).Value
          xNewApp.ActiveSheet.Cells(12, 7) = Cells(rw, 5).Value
          xNewApp.ActiveSheet.Cells(12, 6) = Cells(rw, 6).Value
          xNewApp.ActiveSheet.Cells(12, 7) = Cells(rw, 7).Value
          xNewApp.ActiveSheet.Cells(9, 8) = Cells(rw, 8).Value
          xNewApp.ActiveSheet.Cells(21, 4) = Cells(rw, 13).Value
          xNewApp.ActiveSheet.Cells(8, 2) = Cells(rw, 14).Value
          xNewApp.ActiveSheet.Cells(9, 2) = Cells(rw, 15).Value
          xNewApp.ActiveSheet.Cells(10, 2) = Cells(rw, 16).Value & ", " & Cells(rw, 17).Value & " " & Cells(rw, 18).Value
          xNewApp.ActiveSheet.Cells(14, 6) = Cells(rw, 19).Value
          xNewWB.Save
          xNewWB.Close
       
       Set xNewApp = Nothing
       Set xNewWB = Nothing
       
       Cells(rw, "c").Select
      
    End Sub
    
     
  8. cplmckenzie

    cplmckenzie

    Joined:
    Apr 5, 2012
    Messages:
    50
    jtraylor,

    try the modifications I made to the cmdFill sub.

    also note that in the modified code you will need to redo the file path to your needs..

    cplmckenzie
    =========================================================================

    Private Sub cmdFill_Click()
    Dim shInv As Worksheet
    Dim xNewApp As New Excel.Application
    Dim xNewWB As New Excel.Workbook
    Dim strFile As String
    'name of created sheet
    Dim strFile2 As String
    Dim suffix As String
    suffix = ".xls"
    'end of modification
    Set shInv = ThisWorkbook.Sheets("PackingList")
    rw = Selection.row
    Range("C" & rw).Select 'to select column C of current row

    If Selection.Column <> 3 Or Selection.Value = "" Then
    MsgBox "There is nothing to export!" & Chr(10) & "Select a cell with data from column C," & Chr(10) & "and try again"
    Exit Sub
    End If
    rw = Selection.row
    '***You must change the file path and extension below as applicable***
    strFile = "c:\test\_Packing List Template.xls"
    Set xNewWB = xNewApp.Workbooks.Open(strFile)
    xNewApp.Sheets("Sheet2").Activate
    xNewApp.ActiveSheet.Cells(2, 1) = Cells(rw, 3).Value
    xNewApp.ActiveSheet.Cells(9, 6) = Cells(rw, 4).Value
    xNewApp.ActiveSheet.Cells(12, 7) = Cells(rw, 5).Value
    xNewApp.ActiveSheet.Cells(12, 6) = Cells(rw, 6).Value
    xNewApp.ActiveSheet.Cells(12, 7) = Cells(rw, 7).Value
    xNewApp.ActiveSheet.Cells(9, 8) = Cells(rw, 8).Value
    xNewApp.ActiveSheet.Cells(21, 4) = Cells(rw, 13).Value
    xNewApp.ActiveSheet.Cells(8, 2) = Cells(rw, 14).Value
    xNewApp.ActiveSheet.Cells(9, 2) = Cells(rw, 15).Value
    xNewApp.ActiveSheet.Cells(10, 2) = Cells(rw, 16).Value & ", " & Cells(rw, 17).Value & " " & Cells(rw, 18).Value
    xNewApp.ActiveSheet.Cells(14, 6) = Cells(rw, 19).Value

    'Get name of active workbook to save as
    xNewApp.Sheets("Sheet2").Range("A2").Activate
    xNewApp.ActiveCell.CurrentRegion.Select
    OldNF$ = xNewApp.ActiveCell.NumberFormat
    xNewApp.ActiveCell.NumberFormat = "@"


    strFile2 = xNewApp.ActiveCell.Value
    strFile2 = "c:\test\" + strFile2 + suffix

    xNewWB.SaveAs (strFile2)
    xNewWB.Close

    Set xNewApp = Nothing
    Set xNewWB = Nothing

    Cells(rw, "c").Select

    End Sub
     
  9. jtraylor

    jtraylor Thread Starter

    Joined:
    Apr 11, 2012
    Messages:
    23
    cplmckenzie,
    I think we are almost there. The code works, except it is saving the workbook with a file name of Packing List which is the value of A1, not the packing list @ which is the value of A2. I copied the formula as you have it and just changed the location of the packing list template and the save location. I looked at the code and i see where you specify Range("A2") so not sure where it gets mixed up to save as A1.
     
  10. cplmckenzie

    cplmckenzie

    Joined:
    Apr 5, 2012
    Messages:
    50
    jtraylor,

    I attached a copy of the sheet I have been testing which saves the file with the value in"A2".

    Send me a copy of the file you are using.

    cplmckenzie
     

    Attached Files:

  11. jtraylor

    jtraylor Thread Starter

    Joined:
    Apr 11, 2012
    Messages:
    23
    cplmckenzie,
    Ok i have attached the file i am using that saves the packing list with the value of Packing List (A1) and not the value of 12-xxx (A2 the packing list number). I also saved your file and replaced the path to the packing list template along with save as file path (both just on the desktop for testing purposes) and got the same result.
     

    Attached Files:

  12. cplmckenzie

    cplmckenzie

    Joined:
    Apr 5, 2012
    Messages:
    50
    jtraylor,

    What I am seeing, the differences between what you are testing is in your packing-log-listinewtest2 is that you have the previously named file and packing list template in 2 separate work books.hey

    As where in my test file, they are in both the same work book.

    Look in the sheetname area of the test file I sent to you and recreate the two worksheets in 1 work book.

    Let me know what happen.

    When I tested the test sheet you sent me I got a subscript out of range area, indicating that it (the code) was looking for a non-existent sheet.

    cplmckenzie
     
  13. cplmckenzie

    cplmckenzie

    Joined:
    Apr 5, 2012
    Messages:
    50
    jtraylor,

    also in your workbook your workbook with packing list template place a second sheet and name it
    "Sheet2"

    cplmckenzie
     
  14. jtraylor

    jtraylor Thread Starter

    Joined:
    Apr 11, 2012
    Messages:
    23
    Ok. What i have just attached is an updated file of my Packing List Log (without the packing list template in it, and without the code Set shInv = ThisWorkbook.Sheets("PackingList")). It will populate my separate workbook titled Packing List Template and even save as a new workbook leaving the packinglist template untouched and ready for the next record, however it still continues to save as Packing List (the value of A1). Scrren shots attached.
     

    Attached Files:

  15. jtraylor

    jtraylor Thread Starter

    Joined:
    Apr 11, 2012
    Messages:
    23
    got it to work with the files attached above by deleting the 3 lines of code after "xNewApp.Sheets("Sheet2").Range("A2").Activate".

    Thanks
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1048926