Creating a form to ask a user where the template is and where to save the generate documents.
OK, So I have this Excel spreadsheet with 5 tabs on it. Right now on each tab there is a button that allows the user to extract certain data and create a word document for each row of information. This works great for me. But, the input path and the output paths are hard coded into the module.
I would like to create a tab called Main_Form and have it ask where the template for the word document is located and where to save the output documents for each tab. Here's the catch. There will only be one template, but the documents will be saved in different locations depending on the tab. So one question asking for the template and five questions asking for the path of each output folder. Then a button next to each path that says run and a button at the bottom that says run all.
Here is the code that I am using now:
Sub Export_SheetOne()
Dim objWord As Object
Dim ws As Worksheet
Dim LastRow As Long, i As Long
Dim FileName As String, strPath As String
Dim xMsg As Long
Set ws = ThisWorkbook.Sheets("SheetOne")
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
Set objWord = CreateObject("Word.Application")
For i = 2 To LastRow
objWord.Visible = True
xMsg = MsgBox("Are you sure you want to Continue, this could take longer than two (2) minutes??", vbYesNo, "Proceed with Export?")
If xMsg = vbYes Then
GoTo MoveON
Else
GoTo gracefulExit
End If
MoveON:
Application.StatusBar = "Update Word From Excel: Initialization..."
Thomas,
I did create an option tab as you suggested. Please see the attachment.
I still want the users to be able to browse to the location of where the file will be saved. That's the think I don't know how to do and how to save that path so the run button will look at that path. I think I have an idea on how to make it save the path.
All the answers provided are exactly what I needed for various parts of my project. So I am going to split the points amongst all of you. I did use all of your examples.
Thank you all very much for the help.
John
John Sheehy
ASKER
The answers provided were exactly what I was looking for. They all addressed various stages of my project and I am able to use all the examples throughout the project.
I did create an option tab as you suggested. Please see the attachment.
I still want the users to be able to browse to the location of where the file will be saved. That's the think I don't know how to do and how to save that path so the run button will look at that path. I think I have an idea on how to make it save the path.
John