Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 284
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Opening Excel barry Setting up and Configuration of Excel 11 December 6th 06 11:30 PM
How do I merge data from excel into a Word template? CJ Excel Discussion (Misc queries) 0 August 22nd 06 08:48 PM
Pasting charts to Word from Excel as picture Hari Charts and Charting in Excel 5 July 5th 06 08:04 PM
in an excel macro can you import data from word into a cell? Trefor Excel Discussion (Misc queries) 11 October 6th 05 01:49 PM
Opening two separate instances of Excel Ron Bishop Excel Discussion (Misc queries) 2 August 4th 05 05:30 PM


All times are GMT +1. The time now is 09:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"