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
John SheehySecurity AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ThomasMcA2Commented:
Instead of a separate form, you could just create an "options" tab that looks like a form. It can have buttons that call your subs, and your subs can get the user's choices via something like Sheets("Options").Range("C6").Value.
0
John SheehySecurity AnalystAuthor Commented:
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
0
SimonCommented:
Hi John,

I usually use the FileSystemObject to pick files, check for the existence of files and folders etc. See
http://msdn.microsoft.com/en-us/library/office/gg278516%28v=office.15%29.aspx
http://msdn.microsoft.com/en-us/library/aa711216%28v=vs.71%29.aspx 

I'm not in front of the right machine at the moment to provide code samples, but it is v.easy to use.

Some good basic examples here
http://support.microsoft.com/kb/186118

notably these ones
 Private Sub Command2_Click()
         Dim fso As New FileSystemObject
         Dim f As File
         'Get a reference to the File object.
         Set f = fso.GetFile(Environ("windir") & "\system.ini")
         MsgBox f.Size 'displays size of file
      End Sub

      Private Sub Command3_Click()
         Dim fso As New FileSystemObject
         Dim f As Folder, sf As Folder, path As String
         'Initialize path.
         path = Environ("windir")
         'Get a reference to the Folder object.
         Set f = fso.GetFolder(path)
         'Iterate through subfolders.
         For Each sf In f.SubFolders
           Debug.Print sf.Name
         Next
      End Sub

Open in new window


I can help you adapt these to your needs or provide more extensive examples from my own code tomorrow.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

ltlbearand3Commented:
John,

If I understand your question correctly, you just want some code for use in Excel to be able to help a user select a file and then put that file name and path in a cell.  I would suggest using the filedialog in this instance for the code when someone clicks the browse button.  Here is a snippet on how to put a select file in path in cell A1:

Sub SelectMyFile()
    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = False
        .Show
        
        Range("A1").Value = .SelectedItems(1)
 
    End With
    
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John SheehySecurity AnalystAuthor Commented:
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
0
John SheehySecurity AnalystAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.