Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Creating a form to ask a user where the template is and where to save the generate documents.

Posted on 2014-11-25
6
Medium Priority
?
144 Views
Last Modified: 2014-12-01
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
0
Comment
Question by:John Sheehy
6 Comments
 
LVL 14

Assisted Solution

by:ThomasMcA2
ThomasMcA2 earned 668 total points
ID: 40464696
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
 

Author Comment

by:John Sheehy
ID: 40464750
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
 
LVL 18

Assisted Solution

by:Simon
Simon earned 664 total points
ID: 40465695
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 20

Accepted Solution

by:
ltlbearand3 earned 668 total points
ID: 40468072
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
 

Author Comment

by:John Sheehy
ID: 40473723
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
 

Author Closing Comment

by:John Sheehy
ID: 40473727
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question