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
132 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 167 total points
Comment Utility
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
Comment Utility
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:SimonAdept
SimonAdept earned 166 total points
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 20

Accepted Solution

by:
ltlbearand3 earned 167 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

772 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now