Avatar of John Sheehy
John Sheehy
Flag for United States of America asked on

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..."

objWord.Documents.Open "\\rsmal3-fps1\Share\Sample_CO.docx"

With objWord.ActiveDocument
.Bookmarks("CO").Range.Text = ws.Range("D" & i).Value
.Bookmarks("Author").Range.Text = ws.Range("I" & i).Value
.Bookmarks("Requirement_Number").Range.Text = ws.Range("E" & i).Value
.Bookmarks("Doors_ID").Range.Text = ws.Range("C" & i).Value
.Bookmarks("Title").Range.Text = ws.Range("F" & i).Value
.Bookmarks("Description").Range.Text = ws.Range("G" & i).Value
.Bookmarks("Verification_Method").Range.Text = ws.Range("H" & i).Value
.Bookmarks("Success_Criteria").Range.Text = ws.Range("J" & i).Value
.Bookmarks("Data_Requirements").Range.Text = ws.Range("K" & i).Value

strPath = "\\rsmal3-fps1\Share\SheetOne" & ws.Range("D" & i).Value & ".docx"

objWord.ActiveDocument.SaveAs FileName:=strPath, _
AddToRecentFiles:=False
objWord.Quit False

End With

Next i
GoTo gracefulExit

gracefulExit:
    Application.StatusBar = False
Set objWord = Nothing

End Sub

Any help is always appreciated.

Thanks
John
Microsoft ExcelOffice ProductivityVB Script

Avatar of undefined
Last Comment
John Sheehy

8/22/2022 - Mon
SOLUTION
ThomasMcA2

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
John Sheehy

ASKER
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.

John
Option-Tab.doc
SOLUTION
Simon

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
ltlbearand3

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
John Sheehy

ASKER
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.

John
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes