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
137 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 14

Assisted Solution

by:ThomasMcA2
ThomasMcA2 earned 167 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 166 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 167 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

Simple, centralized multimedia control

Watch and learn to see how ATEN provided an easy and effective way for three jointly-owned pubs to control the 60 televisions located across their three venues utilizing the ATEN Control System, Modular Matrix Switch and HDBaseT extenders.

Question has a verified solution.

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

Suggested Solutions

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

756 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