Running Macro From Excel - results into a word document

[edmon]

I am stuck.
The macro puts all of the entered information into excel and then I have the macro open word to fill auto fill a form. I got as far as opening the docuemnt from the excel, but I keep getting an error that a "document is not open" to put the collected info into the form. Below is the code. I then will send a copy, by e-mail, to accounting. This all worked fine until we updated our documents into word.

Private Sub SendPI_Form()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim MyWord As Word.Application
Set MyWord = CreateObject("Word.Application")
MyDoc = "P:\Office Tools\Administrative\Forms\Office 2003\Kansas City\Project Initialization Form.doc"
MyWord.Documents.Open (MyDoc)
MyWord.Visible = True

'I am stuck here
Windows("Project Initialization Form.doc").Activate

ActiveDocument.Bookmarks("RequestBy").Select
Selection.TypeText Text:="208"

ActiveDocument.Bookmarks("Date").Select
Selection.TypeText Text:=MonthName(Month(Date)) & " " & Day(Date) & " , " & Year(Date)

If NewProj.Value = True Then
ActiveSheet.OLEObjects("NewProject").Object.Value = True
End If

ActiveDocument.Bookmarks("ProjectNo").Select
Selection.TypeText Text:=IntlProjBlng.ProjNo

ActiveDocument.Bookmarks("ProjectDesc").Select
Selection.TypeText Text:=IntlProjBlng.ProjDesc

ActiveDocument.Bookmarks("PhaseNo").Select
Selection.TypeText Text:=IntlProjBlng.PhsNo

ActiveDocument.Bookmarks("PhaseDesc").Select
Selection.TypeText Text:=IntlProjBlng.PhsDesc

ActiveDocument.Bookmarks("IntOwner").Select
Selection.TypeText Text:=IntlProjBlng.IntOwnr

ActiveDocument.Bookmarks("RFCNo").Select
Selection.TypeText Text:=IntlProjBlng.RFCNo

ActiveDocument.Bookmarks("BillingType").Select
Selection.TypeText Text:=IntlProjBlng.BlngType

ActiveDocument.Bookmarks("BudgetAmount").Select
Selection.TypeText Text:=IntlProjBlng.FeeBud

ActiveDocument.Bookmarks("ProjectDate").Select
Selection.TypeText Text:=IntlProjBlng.InitDate

ActiveDocument.Bookmarks("InvVerb").Select
Selection.TypeText Text:=IntlProjBlng.InvoiceTxt


'Send E-mail to Accounting
ActiveWorkbook.SendMail Recipients:="user", Subject:=("Project Initialization Form - " + ProjDesc)
'ActiveWorkbook.SendMail Recipients:="user", Subject:=("Project Initialization Form - " + ProjDesc)
'End sending E-Mail to Accounting

MyWord.Documents.Close (MyDoc)

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Windows("IPB Database.xls").Activate

End Sub

Any help would be great!

 

[NdNoviceHlp]

Some glich... I'm not sure if I just posted or not? Anyways, edmon give this a trial. Untested. HTH. Dave

Private Sub SendPI_Form()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim MyWord As Object

On Error GoTo Fixit
Set MyWord = CreateObject("Word.Application")
MyWord.ChangeFileOpenDirectory "P:\Office Tools\Administrative\Forms\Office 2003\Kansas City\"
MyWord.documents.Open Filename:="Project Initialization Form.doc"
MyWord.Visible = True

With MyWord.Selection


.Bookmarks("RequestBy").Select
Selection.TypeText Text:="208"

.Bookmarks("Date").Select
Selection.TypeText Text:=MonthName(Month(Date)) & " " & Day(Date) & " , " & Year(Date)

'??????? don't know what this stuff below is doing here?
If NewProj.Value = True Then
ActiveSheet.OLEObjects("NewProject").Object.Value = True
End If

.Bookmarks("ProjectNo").Select
Selection.TypeText Text:=IntlProjBlng.ProjNo

.Bookmarks("ProjectDesc").Select
Selection.TypeText Text:=IntlProjBlng.ProjDesc

.Bookmarks("PhaseNo").Select
Selection.TypeText Text:=IntlProjBlng.PhsNo

.Bookmarks("PhaseDesc").Select
Selection.TypeText Text:=IntlProjBlng.PhsDesc

.Bookmarks("IntOwner").Select
Selection.TypeText Text:=IntlProjBlng.IntOwnr

.Bookmarks("RFCNo").Select
Selection.TypeText Text:=IntlProjBlng.RFCNo

.Bookmarks("BillingType").Select
Selection.TypeText Text:=IntlProjBlng.BlngType

.Bookmarks("BudgetAmount").Select
Selection.TypeText Text:=IntlProjBlng.FeeBud

.Bookmarks("ProjectDate").Select
Selection.TypeText Text:=IntlProjBlng.InitDate

.Bookmarks("InvVerb").Select
Selection.TypeText Text:=IntlProjBlng.InvoiceTxt
End With

'Send E-mail to Accounting
ActiveWorkbook.SendMail Recipients:="user", Subject:=("Project Initialization Form - " + ProjDesc)
'ActiveWorkbook.SendMail Recipients:="user", Subject:=("Project Initialization Form - " + ProjDesc)
'End sending E-Mail to Accounting

MyWord.documents.Close (Mydoc)

MyWord.Quit
Set MyWord = Nothing

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Windows("IPB Database.xls").Activate
Exit Sub

Fixit:
On Error GoTo 0
MsgBox "You have an error(check file)"
MyWord.Quit
Set MyWord = Nothing
End Sub

 

[edmon]

Great Fix - New Problem

Thanks for the code. That worked great!. Another glich came with the bookmark tag, but I worked that out. Now I keep getting a type mismatch error. Apprently it does not like the form variable or will not insert it into the word document. I highlighted in bold the "stuck" location.

Private Sub SendPI_Form()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim MyWord As Object

'On Error GoTo Fixit
Set MyWord = CreateObject("Word.Application")
MyWord.ChangeFileOpenDirectory "P:\Office Tools\Administrative\Forms\Office 2003\Kansas City\"
MyWord.Documents.Open Filename:="Project Initialization Form.doc"
MyWord.Visible = True

With MyWord.Selection

'Insert text into word document by user at bookmark (placeholders) locations
.GoTo What:=wdGoToBookmark, Name:="RequestBy"
.TypeText Text:="208"

.GoTo What:=wdGoToBookmark, Name:="Date"
.TypeText Text:=MonthName(Month(Date)) & " " & Day(Date) & " , " & Year(Date)

.GoTo What:=wdGoToBookmark, Name:="ProjectNo"
.TypeText Text:=IntlProjBlng.ProjNo

.GoTo What:=wdGoToBookmark, Name:="ProjectDesc"
.TypeText Text:=IntlProjBlng.ProjDesc

.GoTo What:=wdGoToBookmark, Name:="PhaseNo"
.TypeText Text:=IntlProjBlng.PhsNo

.GoTo What:=wdGoToBookmark, Name:="PhaseDesc"
.TypeText Text:=IntlProjBlng.PhsDesc

.GoTo What:=wdGoToBookmark, Name:="IntOwner"
.TypeText Text:=IntlProjBlng.IntOwnr

.GoTo What:=wdGoToBookmark, Name:="RFCNo"
.TypeText Text:=IntlProjBlng.RFCNo

.GoTo What:=wdGoToBookmark, Name:="BillingType"
.TypeText Text:=IntlProjBlng.BlngType

.GoTo What:=wdGoToBookmark, Name:="BudgetAmount"
.TypeText Text:=IntlProjBlng.FeeBud

.GoTo What:=wdGoToBookmark, Name:="ProjectDate"
.TypeText Text:=IntlProjBlng.InitDate

.GoTo What:=wdGoToBookmark, Name:="InvVerb"
.TypeText Text:=IntlProjBlng.InvoiceTxt
End With

'Send E-mail to Accounting
MyWord.SendMail Recipients:="Eddie Kempf", Subject:=("Project Initialization Form - " + ProjDesc)
'ActiveWorkbook.SendMail Recipients:="user", Subject:=("Project Initialization Form - " + ProjDesc)
'End sending E-Mail to Accounting

MyWord.Documents.Close (Mydoc)

MyWord.Quit
Set MyWord = Nothing

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Windows("IPB Database.xls").Activate
Exit Sub

Fixit:
On Error GoTo 0
MsgBox "You have an error(check file)"
MyWord.Quit
Set MyWord = Nothing
End Sub

 

[NdNoviceHlp]

edmon make sure your variables are dimmed as strings before there insertion. HTH. Dave

 

[edmon]

How many problems in one day?

Thanks for the fix. I tried dimming and it cleared them, but I did find a fix that worked, see below

Example:
GoTo What:=wdGoToBookmark, Name:="RFCNo"
.TypeText Text:=IntlProjBlng.RFCNo.Text

Now that I have this completed and working, I sent it to the person who uses it, but is on a different version of excel (I am on 2003, other is on office 2k) and I get an error message. The funny thing it was working fine last week until I updated the code. It works for me but not him.

Here is where it is crashing:
Private Sub Country_Change()
If IntlProjBlng.Country.Value = "10: General Office" Then
Windows("IPB Database.xls").Activate
Sheets("General Office").Activate
SetBlngNo = Application.WorksheetFunction.Max(Columns("A:A"))
NewBlngNo = (SetBlngNo + 10000000 + 1)
IntlProjBlng.AvailProjNo.Caption = NewBlngNo
End If
End Sub

It highlights portons of the code (I have bolded them above) and give me a compile error;Can't find project or library.

This portion of the routine basically finds the last number in the column plus one and tells the user what the next available number is in the form

BTW thanks for the help.
- Ed-mon -

 

[NdNoviceHlp]

This portion of the code looks suspicious:

If IntlProjBlng.Country.Value = "10: General Office" Then

maybe should be:

If IntlProjBlng.Country = "10: General Office" Then

I'm glad your having some success. What I meant by dimming strings was

Dim Temp as String
Temp = IntlProjBlng.RFCNo
.TypeText Text:= Temp

or... maybe you haven't opened IPB Database.xls yet?

Workbooks.Open "C:\foldername\IPB Database.xls" 'your dir
Sheets("General Office").Activate

also... I'm really having difficulty with "IntlProjBlng.RFCO", "IntlProjBlng.Feebud", etc. What is "IntlProjBlng" dimmed as? I'm fairly certain that you can't extend IntlProjBlng.Country to IntlProjBlng.Country.Value or IntlProjBlng.Country.Text.. you can only use IntlProjBlng.Country
by itself. Give these a trial. Dave

 

[Norie]

edmon

On the computer where the problem is check in the VBA Editor under Tools>References...

Are there any marked as missing?

 

[edmon]

Thanks for all of the help

Norie and Dave,
Thank you for your help and ideas. I was able to get it to work.

There was a "missing" selection under the references. I corrected that and it worked beautifully.

Thanks for the Dim lesson. I typically do not use them. Most of my macros satrt off with macro recorder and then I go crazy and try things the recorder will not record.

I am learning as I go along so, all of your help is greatly appreciated.
Again,
Thanks

- Ed-mon -