Export user inputs to a template and save it as a different file

[sam]

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

 

[john]

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