Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Populate Excel Form and Word Form from Excel Table


(!)

jtraylor's Avatar
jtraylor jtraylor is offline
Computer Specs
Member with 23 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Beginner
19-Apr-2012, 12:33 AM #16
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.
jtraylor's Avatar
jtraylor jtraylor is offline
Computer Specs
Member with 23 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Beginner
19-Apr-2012, 01:25 AM #17
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.
jtraylor's Avatar
jtraylor jtraylor is offline
Computer Specs
Member with 23 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Beginner
19-Apr-2012, 03:03 PM #18
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
cplmckenzie's Avatar
cplmckenzie cplmckenzie is offline
Computer Specs
Member with 50 posts.
 
Join Date: Apr 2012
26-Apr-2012, 08:55 AM #19
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

====================================================
cplmckenzie's Avatar
cplmckenzie cplmckenzie is offline
Computer Specs
Member with 50 posts.
 
Join Date: Apr 2012
26-Apr-2012, 08:59 AM #20
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
cplmckenzie's Avatar
cplmckenzie cplmckenzie is offline
Computer Specs
Member with 50 posts.
 
Join Date: Apr 2012
26-Apr-2012, 09:33 AM #21
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?
jtraylor's Avatar
jtraylor jtraylor is offline
Computer Specs
Member with 23 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Beginner
27-Apr-2012, 10:01 PM #22
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
cplmckenzie's Avatar
cplmckenzie cplmckenzie is offline
Computer Specs
Member with 50 posts.
 
Join Date: Apr 2012
28-Apr-2012, 08:20 PM #23
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
jtraylor's Avatar
jtraylor jtraylor is offline
Computer Specs
Member with 23 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Beginner
29-Apr-2012, 02:13 AM #24
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.
cplmckenzie's Avatar
cplmckenzie cplmckenzie is offline
Computer Specs
Member with 50 posts.
 
Join Date: Apr 2012
29-Apr-2012, 06:07 AM #25
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
File Type: xls packinglistlogTEST.xls (90.0 KB, 53 views)
jtraylor's Avatar
jtraylor jtraylor is offline
Computer Specs
Member with 23 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Beginner
29-Apr-2012, 01:22 PM #26
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
File Type: xls packing-list-log-newtest2.xls (80.5 KB, 51 views)
File Type: xls _Packing List Template.xls (36.0 KB, 37 views)
cplmckenzie's Avatar
cplmckenzie cplmckenzie is offline
Computer Specs
Member with 50 posts.
 
Join Date: Apr 2012
29-Apr-2012, 04:01 PM #27
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
cplmckenzie's Avatar
cplmckenzie cplmckenzie is offline
Computer Specs
Member with 50 posts.
 
Join Date: Apr 2012
29-Apr-2012, 04:22 PM #28
jtraylor,

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

cplmckenzie
jtraylor's Avatar
jtraylor jtraylor is offline
Computer Specs
Member with 23 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Beginner
29-Apr-2012, 07:14 PM #29
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
File Type: xls packing-list-log-newtest2.xls (80.5 KB, 42 views)
File Type: xls _Packing List Template.xls (36.0 KB, 32 views)
File Type: xls PACKING LIST.xls (30.5 KB, 35 views)
File Type: docx Screen Shots.docx (131.1 KB, 58 views)
File Type: docx Screen Shots 2.docx (94.9 KB, 50 views)
jtraylor's Avatar
jtraylor jtraylor is offline
Computer Specs
Member with 23 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Beginner
30-Apr-2012, 12:35 AM #30
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

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


Tags
excel, forms, macros, templates

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑