Running Macro From Excel - results into a word document

edmon

New Member
Joined
Oct 10, 2003
Messages
7
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!
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,791
Some glich... I'm not sure if I just posted or not? Anyways, edmon give this a trial. Untested. HTH. Dave
Code:
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

New Member
Joined
Oct 10, 2003
Messages
7
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

Well-known Member
Joined
Nov 9, 2002
Messages
2,791
edmon make sure your variables are dimmed as strings before there insertion. HTH. Dave
 

edmon

New Member
Joined
Oct 10, 2003
Messages
7

ADVERTISEMENT

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

Well-known Member
Joined
Nov 9, 2002
Messages
2,791
This portion of the code looks suspicious:
Code:
If IntlProjBlng.Country.Value = "10: General Office" Then
maybe should be:
Code:
If IntlProjBlng.Country = "10: General Office" Then
I'm glad your having some success. What I meant by dimming strings was
Code:
Dim Temp as String
Temp = IntlProjBlng.RFCNo
.TypeText Text:= Temp

or... maybe you haven't opened IPB Database.xls yet?
Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
edmon

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

Are there any marked as missing?
 

edmon

New Member
Joined
Oct 10, 2003
Messages
7
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 -
 

Forum statistics

Threads
1,136,989
Messages
5,678,975
Members
419,796
Latest member
doctorgresham

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top