PDA

View Full Version : Turn on Resources on Install



Djblois
03-08-2007, 08:21 AM
I use some Outlook code and Word code in my add-in. However sometimes I forget to turn those resources on in the VBA for some of my users. Is there a way to turn them on with code?

Bob Phillips
03-08-2007, 08:23 AM
Turn on? What do you mean?

Djblois
03-08-2007, 08:26 AM
Sorry, I meant the References in VBA under tools on the VBA toolbar

I have to turn on the Outlook reference library and the Word Reference Library.

Bob Phillips
03-08-2007, 08:40 AM
Once you have done it in a workbook, it remains. You don't need to do it again.

Djblois
03-08-2007, 08:44 AM
I know that but sometimes when I install it on someone else's machine for the first time I forget to turn it on then when they try to use some of those features they get errors.

Bob Phillips
03-08-2007, 10:21 AM
So use late binding, then you can forgte about it.

So instaed of



Dim oOutlook As Outlook.Application
Dim oNameSpace As Outlook.NameSpace
Dim oMailItem As Outlook.MailItem
Dim oRecipient As Outlook.Recipient

Set oOutlook = New Outlook.Application
Set oNameSpace = oOutlook.GetNamespace("MAPI")
oNameSpace.Logon , , True

Set objMailItem = oOutlook.CreateItem(olMailItem)

you would use



Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object


Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True


Set oMailItem = oOutlook.CreateItem(0)

Djblois
03-13-2007, 07:43 AM
xld,

what is the difference between early binding and late binding? Here is some code I am using for word:

Documents.Open ("H:\@Business_Reporting_Today\References\Business Reporting Today.doc")

Here is some code I am using for Outlook:
Sub eMailActiveWorkbook()

Dim OL As Object
Dim EmailItem As Object
Dim WB As Workbook

TurnOffFeatures
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set WB = ActiveWorkbook
WB.Save
With EmailItem
.Attachments.Add WB.FullName
.Display
End With

TurnOnFeatures

Set WB = Nothing
Set OL = Nothing
Set EmailItem = Nothing

End Sub

Bob Phillips
03-13-2007, 08:12 AM
Early binding is just binding to the type library at compile time, late binding is at run time.

Early binding is more efficient as VBA retrieves information from the library up-front, and uses that throughout, whereas late binding has to go through the registry, find the library, look up the details, every time a reference is made to an object within that library. But early binding ties you to a praticular version, late binding is more flexible.

With late binding, you cannot use that libraries constants, such as olMailItem, as you will get a compile error if you do. You could declare them as constants in your code, but otherwise you have to use the constant value.

Djblois
03-23-2007, 10:47 AM
How do I change this to late binding?

Dim OL As Object
Dim EmailItem As Object
Dim WB As Workbook

TurnOffFeatures
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set WB = ActiveWorkbook
WB.Save
With EmailItem
.Attachments.Add WB.FullName
.Display
End With

TurnOnFeatures

Set WB = Nothing
Set OL = Nothing
Set EmailItem = Nothing

Djblois
03-23-2007, 11:01 AM
xld,

can you fix it so your post is horizontal not vertical?

Bob Phillips
03-23-2007, 11:36 AM
It's VBAX, not me, but I will try again
Dim OL As Object
Dim EmailItem As Object
Dim WB As Workbook
TurnOffFeatures
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(0)
Set WB = ActiveWorkbook
WB.Save
With EmailItem
.Attachments.Add WB.FullName
.Display
End With
TurnOnFeatures
Set WB = Nothing
Set OL = Nothing
Set EmailItem = Nothing


Fixed, MD

Bob Phillips
03-23-2007, 11:38 AM
It's no good.

Djblois
03-23-2007, 11:48 AM
Now it is telling me object variable or with block not set when I try to run the code after I turn off the Outlook references.

Bob Phillips
03-23-2007, 12:09 PM
Where?

Djblois
03-23-2007, 12:32 PM
it is weird because it is giving me that error message where I show the form that I created for email options.

emailform.show

Bob Phillips
03-23-2007, 12:42 PM
I don't see that line in the code.

Djblois
03-23-2007, 12:47 PM
that is because that code is called from the form

Ivan F Moala
03-23-2007, 09:07 PM
Now it is telling me object variable or with block not set when I try to run the code after I turn off the Outlook references.

go to your UF code
Ensure you have Option explicit set.
Recompile your code.... see where it highlights your error.
It would appear ypou may still have a early bound reference set.

Djblois
03-26-2007, 08:39 AM
Thank you all, I got that to work. Now how do I change this to late binding:

Documents.Open ("H:\@Business_Reporting_Today\References\Business Reporting Today.doc")

Bob Phillips
03-26-2007, 09:37 AM
That code is neither late nor early.

Djblois
03-26-2007, 09:43 AM
then how do I get it to work with the reference turned off. I get an object required error

Bob Phillips
03-26-2007, 09:43 AM
Probably because you have qualified it with the application object.

Djblois
03-26-2007, 09:46 AM
ok so how do I unqualify it?

Bob Phillips
03-26-2007, 09:48 AM
I meant haven't not have.

lucas
03-26-2007, 09:52 AM
I think Bob's saying you have to dim it as an object too...just as you did in this code for the email object:
Dim OL As Object
Dim EmailItem As Object
Dim WB As Workbook
TurnOffFeatures
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(0)

Bob Phillips
03-26-2007, 10:04 AM
I was assuming he had already done that Steve, although I was thinking Word, and that he just needed to pre-pend Documents with that object variable.

Djblois
03-26-2007, 10:08 AM
so would this be what you mean

dim Documents as object

lucas
03-26-2007, 10:09 AM
Just trying to cover the bases Bob....I didn't see in his previous code where he had dealt with the Word object so I thought I would remind him of the concept...not meaning to step on toes.:whistle:

Bob Phillips
03-26-2007, 10:12 AM
Nor did I Steve, he hasn't mentioned it specifically, hence my obtuseness borne of lack of information.

lucas
03-26-2007, 10:17 AM
Reading between the lines in not my best suite. It seems they think we can look over thier shouders somtimes and see what they are doing.....

Several regular supporters here have signatures that refer to complete descriptions of what your trying to do but if you don't get the info you have to work in the dark.....time consuming and frustrating.

Bob Phillips
03-26-2007, 10:20 AM
so would this be what you mean

dim Documents as object

No, no!

Letr's assume, because you haven't told me, that you have started word from your code and have a Word object. Let us further assume that that object is called oWord (spooky stuff!).

IF these assumptions are correct, you probably want

oWord.Documents.Open (blah blah blah)

Djblois
03-26-2007, 10:27 AM
no I haven't started word yet but I guess I need to?

Djblois
03-26-2007, 12:18 PM
How do I start word programatically?

lucas
03-26-2007, 12:25 PM
create object....just as you did with outlook..

Bob Phillips
03-26-2007, 12:49 PM
no I haven't started word yet but I guess I need to? You do if you are working with Word documnts. Are you sure that is what you need?

Djblois
03-26-2007, 12:52 PM
THis is what I have now and it is I think opening the file hidden:

Sub Instructions()

Set WD = CreateObject("Word.Application")
TurnOffFeatures

On Error Resume Next
WD.Documents.Open ("H:\@Business_Reporting_Today\References\Business Reporting Today.doc")
TurnOnFeatures

End Sub

I think that because it doesn't show the first time I run it and if I run it again it says it is locked for editing by me.

Bob Phillips
03-26-2007, 01:56 PM
Add
WD.Visible = True

after creating the Word instance.

Djblois
03-26-2007, 02:28 PM
that works perfectly but now I want to create it so it checks if it is open already and if it is already open then it will switch to it. I know how to do this with excel but not with word.

Bob Phillips
03-26-2007, 02:40 PM
Same, just Word.Application not Excel.APplication.

Bob Phillips
03-26-2007, 02:41 PM
PS when you go consulting I claim 10% of your income.

Simon Lloyd
03-26-2007, 03:07 PM
Here's a couple of macro's i picked up that may help you understand what you are doing! (thats 2.5% for me too....need the cash Bob!)
The two example macros below demonstrates how you can send information to Word
(e.g. creating a new document) and how you can retrieve information from Word
(e.g. reading information from a document).

Note! Read and edit the example code before you try to execute it in your own project!

The two example macros below demonstrates how you can send information to Word
(e.g. creating a new document) and how you can retrieve information from Word
(e.g. reading information from a document).
Note! Read and edit the example code before you try to execute it in your own project!
Sub CreateNewWordDoc()
' to test this code, paste it into an Excel module
' add a reference to the Word-library
' create a new folder named C:\Foldername or edit the filnames in the code
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim i As Integer
Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Add ' create a new document
' or
'Set wrdDoc = wrdApp.Documents.Open("C:\Foldername\Filename.doc")
' open an existing document
' example word operations
With wrdDoc
For i = 1 To 100
.Content.InsertAfter "Here is a example test line #" & i
.Content.InsertParagraphAfter
Next i
If Dir("C:\Foldername\MyNewWordDoc.doc") <> "" Then
Kill "C:\Foldername\MyNewWordDoc.doc"
End If
.SaveAs ("C:\Foldername\MyNewWordDoc.doc")
.Close ' close the document
End With
wrdApp.Quit ' close the Word application
Set wrdDoc = Nothing
Set wrdApp = Nothing
End Sub

Sub OpenAndReadWordDoc()
' assumes that the previous procedure has been executed
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim tString As String, tRange As Word.Range
Dim p As Long, r As Long
Workbooks.Add ' create a new workbook
With Range("A1")
.Formula = "Word Document Contents:"
.Font.Bold = True
.Font.Size = 14
.Offset(1, 0).Select
End With
r = 3 ' startrow for the copied text from the Word document
Set wrdApp = CreateObject("Word.Application")
'wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Open("C:\Foldername\MyNewWordDoc.doc")
' example word operations
With wrdDoc
For p = 1 To .Paragraphs.Count
Set tRange = .Range(Start:=.Paragraphs(p).Range.Start, _
End:=.Paragraphs(p).Range.End)
tString = tRange.Text
tString = Left(tString, Len(tString) - 1)
' exclude the paragraph-mark
' check if the text has the content you want
If InStr(1, tString, "1") > 0 Then
' fill into active worksheet
ActiveSheet.Range("A" & r).Formula = tString
r = r + 1
End If
Next p
.Close ' close the document
End With
wrdApp.Quit ' close the Word application
Set wrdDoc = Nothing
Set wrdApp = Nothing
ActiveWorkbook.Saved = True
End Sub


Regards,Simon

Bob Phillips
03-26-2007, 03:14 PM
You've hit the same problem as me.

Simon Lloyd
03-26-2007, 03:17 PM
Well will soon be payday!.......isn't 10% a little low?

Bob Phillips
03-26-2007, 03:36 PM
I was referring to how your post got messed up. Did you sort that, or was it an admin?

Simon Lloyd
03-26-2007, 03:47 PM
I posted the code but it posted it as a single line!, so i went to edit back to my workbook copy n paste again...then it was ok?

Wierd as i did no action differently!

Bob Phillips
03-26-2007, 04:07 PM
That's what I am getting all the time, but often I can't even fix it.

Simon Lloyd
03-26-2007, 04:16 PM
Do you use smart indent?, i'm just looking for a common factor!

anyway i'm off to bed for now!

Bob Phillips
03-26-2007, 04:22 PM
No, never do.

Djblois
03-27-2007, 05:43 AM
This works partially:


Set wd = CreateObject("Word.Application")
wd.Visible = True

On Error Resume Next
Set wdDoc = wrdApp.Documents.Open("H:\@Business_Reporting_Today\References\Business Reporting Today.doc")

If Err Then
wd.Documents.Open ("H:\@Business_Reporting_Today\References\Business Reporting Today.doc")
Else
wdDoc.Activate
End If

but what error code shoud I set it to on the line

If Err Then


In Excel I have it set to 9

Simon Lloyd
03-27-2007, 06:48 AM
If Err.Number = 429 Then
this is the error code if it's not already loaded!

Djblois
03-27-2007, 06:57 AM
How do I find out the error code numbers? So I don't have to ask again?

Djblois
03-27-2007, 06:59 AM
THank you for your help but that isn't working:

Sub Instructions()

Set wd = CreateObject("Word.Application")
wd.Visible = True

On Error Resume Next
Set wdDoc = wrdApp.Documents.Open("H:\@Business_Reporting_Today\References\Business Reporting Today.doc")

If Not Err.Number = 429 Then
wd.Documents.Open ("H:\@Business_Reporting_Today\References\Business Reporting Today.doc")
Else
wdDoc.Activate
End If

End Sub

I also tried:

Sub Instructions()

Set wd = CreateObject("Word.Application")
wd.Visible = True

On Error Resume Next
Set wdDoc = wrdApp.Documents.Open("H:\@Business_Reporting_Today\References\Business Reporting Today.doc")

If Err.Number = 429 Then
wd.Documents.Open ("H:\@Business_Reporting_Today\References\Business Reporting Today.doc")
Else
wdDoc.Activate
End If

End Sub

Bob Phillips
03-27-2007, 08:51 AM
I have propagated your error but you will get problems if you create a Word instance and set a variable called wd but refer to wrdApp later on.

ALWAYS USE OPTION EXPLICIT!



Set wd = CreateObject("Word.Application")
wd.Visible = True

On Error Resume Next
Set wdDoc = wrdApp.Documents.Open("H:\@Business_Reporting_Today\References\Business Reporting Today.doc")
On Error GoTo 0

If Not wdDoc Is Nothing Then
wdDoc.Activate
Else
MsgBox "File not found"
End If

Djblois
03-27-2007, 09:35 AM
This is what I have now:

Sub Instructions()

Dim wdDoc As Object
Dim wd As Object

Set wd = CreateObject("Word.Application")
wd.Visible = True

On Error Resume Next
Set wdDoc = wd.Documents("H:\@Business_Reporting_Today\References\Business Reporting Today.doc")
On Error GoTo 0

If Not wdDoc Is Nothing Then
wdDoc.Activate
Else
wd.Documents.Open ("H:\@Business_Reporting_Today\References\Business Reporting Today.doc")
End If

End Sub

unfortunately, if it is open already it is telling me it is locked for editing

Bob Phillips
03-27-2007, 10:33 AM
Try this



Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long

On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err
On Error GoTo 0

Select Case iErr
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select

End Function

Sub Instructions()
Dim wdDoc As Object
Dim wd As Object

Set wd = CreateObject("Word.Application")
wd.Visible = True

If Not IsFileOpen("H:\@Business_Reporting_Today\References\Business Reporting Today.doc") Then
wd.Documents.Open ("H:\@Business_Reporting_Today\References\Business Reporting Today.doc")
wdDoc.Activate
Else
MsgBox "File is already open"
End If

End Sub