Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Opening a Word template from excel
What I am trying to create is a Request for information log.
My ultimate solution is as follows: When cell A2 is clicked, it will change it's value to the next consecutive number from the cell above. B2 would then have the date inserted into it. A Word template file would open and automatically save as a .DOC with "RFI-" and the number from A2. The Word file can be edited and then just saved. A hyperlink would then be created in C2 allowing you to open that word file later to view it. At the very least, I'd like to be able to click cell...say D2 and open the word template file to where when Save is selected it will allow you to save as a DOC file without having to change the file type. It seems when I use a hyperlink to the template and hit save, it will just overwrite the template. I changed the template to read only, but it still tries to save as a templete type file. The hyper link seems to open the template file differently than dbl-clicking it in windows explorer. Sorry it's soo long of a question. Rich |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Opening a Word template from excel
Rich,
The subroutine below should do close to what you want. For testing, I created a Word template named TestX.dot and placed it in my default location for Word templates (if you do otherwise, you will need to full path to the dot file rather than just the template name in the sub). The template contains a bookmark named "myBookmarkA" where I chose to have the date of document creation inserted but you don't need that if you don't want to automatically insert items into the document you're creating. If you want, you can make the Word document visible to allow the user to make modifications. The routine assumes the existence of a folder named C:\Test which is where the new Word documents will be saved to. When you open the VB editor window, go to 'Tools References' and make sure to set a reference to the Microsoft Word Object Library. This code need to be placed into the particular sheet where you will be working. Whenever you click the cell in column A that is immediately below the previously filled cell, the cell you clicked in A will be filled with a number one greater than the cell above it, the cell to its right will be populated with the current date, the new Word document will be created and saved to the folder "C:\Test" and the cell to the right in column C will be populated with a hyperlink to the newly created Word document. _______________________________ Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim NewTopRow As Long Dim strDocName As String Dim strTxtInsert As String Dim bStarted As Boolean Dim oApp As Word.Application NewTopRow = ActiveSheet.UsedRange.Rows.Count + 1 If Target.Address = "$A$" & CStr(NewTopRow) Then Application.EnableEvents = False Cells(NewTopRow, 1).Value = Cells(NewTopRow - 1, 1).Value + 1 Cells(NewTopRow, 2).Value = Date strDocName = "RFI-" & CStr(Cells(NewTopRow, 1).Value) strTxtInsert = Format$(Date, "mmm, d, yyyy") On Error Resume Next Set oApp = GetObject(, "Word.Application") If Err < 0 Then bStarted = True Set oApp = CreateObject("Word.Application") End If oApp.Activate oApp.Documents.Add Template:="TestX.dot" oApp.ActiveDocument.Bookmarks("MyBookmarkA").Range .InsertAfter strTxtInsert oApp.ActiveDocument.SaveAs "C:\Test\" & strDocName & ".doc" oApp.ActiveDocument.Close wdSaveChanges ' Quit Word, only if it wasn't running when this code was started If bStarted Then oApp.Quit End If ActiveSheet.Hyperlinks.Add Anchor:=Cells(NewTopRow, 3), _ Address:="C:\Test\" & strDocName & ".doc", TextToDisplay:=strDocName Application.EnableEvents = True End If End Sub ______________________________ Steve "RealmSteel" wrote in message ... What I am trying to create is a Request for information log. My ultimate solution is as follows: When cell A2 is clicked, it will change it's value to the next consecutive number from the cell above. B2 would then have the date inserted into it. A Word template file would open and automatically save as a .DOC with "RFI-" and the number from A2. The Word file can be edited and then just saved. A hyperlink would then be created in C2 allowing you to open that word file later to view it. At the very least, I'd like to be able to click cell...say D2 and open the word template file to where when Save is selected it will allow you to save as a DOC file without having to change the file type. It seems when I use a hyperlink to the template and hit save, it will just overwrite the template. I changed the template to read only, but it still tries to save as a templete type file. The hyper link seems to open the template file differently than dbl-clicking it in windows explorer. Sorry it's soo long of a question. Rich |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Opening a Word template from excel
Steve, This works Excellent. I really appreciate your reply. I'll do the required modifications for my file structure and post up if I hit an impass. Thanks again, Rich "Steve Yandl" wrote: Rich, The subroutine below should do close to what you want. For testing, I created a Word template named TestX.dot and placed it in my default location for Word templates (if you do otherwise, you will need to full path to the dot file rather than just the template name in the sub). The template contains a bookmark named "myBookmarkA" where I chose to have the date of document creation inserted but you don't need that if you don't want to automatically insert items into the document you're creating. If you want, you can make the Word document visible to allow the user to make modifications. The routine assumes the existence of a folder named C:\Test which is where the new Word documents will be saved to. When you open the VB editor window, go to 'Tools References' and make sure to set a reference to the Microsoft Word Object Library. This code need to be placed into the particular sheet where you will be working. Whenever you click the cell in column A that is immediately below the previously filled cell, the cell you clicked in A will be filled with a number one greater than the cell above it, the cell to its right will be populated with the current date, the new Word document will be created and saved to the folder "C:\Test" and the cell to the right in column C will be populated with a hyperlink to the newly created Word document. _______________________________ Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim NewTopRow As Long Dim strDocName As String Dim strTxtInsert As String Dim bStarted As Boolean Dim oApp As Word.Application NewTopRow = ActiveSheet.UsedRange.Rows.Count + 1 If Target.Address = "$A$" & CStr(NewTopRow) Then Application.EnableEvents = False Cells(NewTopRow, 1).Value = Cells(NewTopRow - 1, 1).Value + 1 Cells(NewTopRow, 2).Value = Date strDocName = "RFI-" & CStr(Cells(NewTopRow, 1).Value) strTxtInsert = Format$(Date, "mmm, d, yyyy") On Error Resume Next Set oApp = GetObject(, "Word.Application") If Err < 0 Then bStarted = True Set oApp = CreateObject("Word.Application") End If oApp.Activate oApp.Documents.Add Template:="TestX.dot" oApp.ActiveDocument.Bookmarks("MyBookmarkA").Range .InsertAfter strTxtInsert oApp.ActiveDocument.SaveAs "C:\Test\" & strDocName & ".doc" oApp.ActiveDocument.Close wdSaveChanges ' Quit Word, only if it wasn't running when this code was started If bStarted Then oApp.Quit End If ActiveSheet.Hyperlinks.Add Anchor:=Cells(NewTopRow, 3), _ Address:="C:\Test\" & strDocName & ".doc", TextToDisplay:=strDocName Application.EnableEvents = True End If End Sub ______________________________ Steve "RealmSteel" wrote in message ... What I am trying to create is a Request for information log. My ultimate solution is as follows: When cell A2 is clicked, it will change it's value to the next consecutive number from the cell above. B2 would then have the date inserted into it. A Word template file would open and automatically save as a .DOC with "RFI-" and the number from A2. The Word file can be edited and then just saved. A hyperlink would then be created in C2 allowing you to open that word file later to view it. At the very least, I'd like to be able to click cell...say D2 and open the word template file to where when Save is selected it will allow you to save as a DOC file without having to change the file type. It seems when I use a hyperlink to the template and hit save, it will just overwrite the template. I changed the template to read only, but it still tries to save as a templete type file. The hyper link seems to open the template file differently than dbl-clicking it in windows explorer. Sorry it's soo long of a question. Rich |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Opening a Word template from excel
OK.
This probably should be in the programming group, but since I started it here, I'll keep this thread going. I've modified the code for my particular application. I added a little to also put the RFI number in the word document. Everything works great. I am having a couple issues. I have to make sure I have any cells above the start of this filled with some value or letter and then have a cell with Zero in it right where I want the auto numbering to begin. Not a huge deal. I just put asterisks in there. My big problem now is formatting the cells when a RFI is created. I need to have the cells in the new row formatted with a border and a few cells after the auto generated cells filled with a color. If I format the cells before the macro is run, it moves it's first cell down below my formatting. What I want to do is add in the formatting to this code so when I click the next empty cell, it will create the RFI and then format that row. My confusion comes from "NewTopRow". I am very new to VBA programming, but have been around since Basic and Fortran were the rage, so I understand the logic. I've done some searching, but do not understand how "NewTopRow" works. In my worksheet, I have 6 columns. The first 3 are auto generated, but the next 3 are for user inputted data. I did a macro record to generate the code for formatting, but I need to modify it so it works with the "NewTopRow" idea. Thanks, Rich "Steve Yandl" wrote: Rich, The subroutine below should do close to what you want. For testing, I created a Word template named TestX.dot and placed it in my default location for Word templates (if you do otherwise, you will need to full path to the dot file rather than just the template name in the sub). The template contains a bookmark named "myBookmarkA" where I chose to have the date of document creation inserted but you don't need that if you don't want to automatically insert items into the document you're creating. If you want, you can make the Word document visible to allow the user to make modifications. The routine assumes the existence of a folder named C:\Test which is where the new Word documents will be saved to. When you open the VB editor window, go to 'Tools References' and make sure to set a reference to the Microsoft Word Object Library. This code need to be placed into the particular sheet where you will be working. Whenever you click the cell in column A that is immediately below the previously filled cell, the cell you clicked in A will be filled with a number one greater than the cell above it, the cell to its right will be populated with the current date, the new Word document will be created and saved to the folder "C:\Test" and the cell to the right in column C will be populated with a hyperlink to the newly created Word document. Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening Excel | Setting up and Configuration of Excel | |||
How do I merge data from excel into a Word template? | Excel Discussion (Misc queries) | |||
Pasting charts to Word from Excel as picture | Charts and Charting in Excel | |||
in an excel macro can you import data from word into a cell? | Excel Discussion (Misc queries) | |||
Opening two separate instances of Excel | Excel Discussion (Misc queries) |