Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export user inputs to a template and save it as a different file
Hi All,
I have a userfrom designed in workbook1 which accepts user inptus and has a "submit" button, On clicking submit button I want to export the user inputs to a seperate workbook lets say workbook2 (which will act as a template). Now I dont want this workbook to save, But save it as workbook3. so basically workbook3 is the exact same copy of workbook 2 but with user inputs, and workbook2 will always remain empty and act as a template. SO everytime a user inputs and clicks "submit" I want the inputs to be exported to workbook2 which will then be saved as workbook3.. Hope I am clear. Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export user inputs to a template and save it as a different file
Hi Sam,
Not fully sure really understand your workflow but as a suggestion: 1 - keep the template in workbook 1 2 - populate with data from your form 3 - copy template to new workbook 4 - save new workbook (your workbook 3) 5 €“ close (if required) following code is not a solution more an idea. It captures textbox values in to an array which you would then assign to the required ranges in your template. When done, template is copied and saved as new workbook. You can add code to delete values from template afterwards if needed. code goes behind your form €“ as said, not a solution but may give you some ideas. note: I use 2003 and SaveAs portion of code may need updating if you use 2007. Hope helpful Private Sub CommandButton1_Click() Dim ctl As Control Dim myarray() As String Dim NewWB As Workbook Dim ws As Worksheet i = 0 For Each ctl In Me.Controls If TypeName(ctl) = "TextBox" Then ReDim Preserve myarray(i) myarray(i) = ctl.Value i = i + 1 End If Next Set ws = Worksheets("Template") For i = LBound(myarray) To UBound(myarray) With ws 'add my array values 'to required template sheet ranges End With MsgBox myarray(i) Next 'copy template ws.Copy Set NewWB = ActiveWorkbook 'save & close With NewWB .SaveAs "C:\template.xls" .Close False End With End Sub -- jb "sam" wrote: Hi All, I have a userfrom designed in workbook1 which accepts user inptus and has a "submit" button, On clicking submit button I want to export the user inputs to a seperate workbook lets say workbook2 (which will act as a template). Now I dont want this workbook to save, But save it as workbook3. so basically workbook3 is the exact same copy of workbook 2 but with user inputs, and workbook2 will always remain empty and act as a template. SO everytime a user inputs and clicks "submit" I want the inputs to be exported to workbook2 which will then be saved as workbook3.. Hope I am clear. Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
save user inputs from userform in a seperate workbook | Excel Programming | |||
Do not let user save template if it has been been modified | Excel Programming | |||
prompt user to save file as {desired_name} and save it to a variab | Excel Programming | |||
Save user-defined chart types in a shared Excel template? | Charts and Charting in Excel | |||
Macro to export a worksheet and save as new file | Excel Programming |