Fixing missing references automatically

  • Thread starter Thread starter John Welch
  • Start date Start date
J

John Welch

When I distribute an Excel workbook with VBA code, half the time I have to
call up the users and walk them through going to the references list and
unchecking and rechecking the missing references, because someone always has
different libraries than I do. It's frustrating and embarrasing - it makes
me look unprofessional as a developer, which I guess I am. Can anyone tell
me a good way to avoid this problem, either by having this task automated or
by sending out some kind of update that makes everyone current with the
latest vba libraries?
I'm using Excel 2002, and the libraries I'm having the most problems with
are MS Office 10/11 and MS Word 10/11.
Thanks
 
I think lots of people don't use the references when they release thier
workbooks to the users.


Instead of:
Dim WDApp As Word.Application
Dim WDDoc As Word.Document

You could use:
Dim WDApp As Object
Dim WDDoc As Object

Then:
Set WDApp = new Word.application
becomes
Set WDApp = CreateObject("Word.Application")

Here's an old post that shows you one way to do it.

Option Explicit
Sub ChartToDocument2A()

Const wdPasteMetafilePicture As Long = 3
Const wdInLine As Long = 0
Const wdNewBlankDocument As Long = 0
Const wdFormatDocument As Long = 0

Dim WDApp As Object
Dim WDDoc As Object

Dim wWsht As Worksheet
Dim sShape As Shape
Dim WordWasRunning As Boolean

WordWasRunning = True
On Error Resume Next
Set WDApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set WDApp = CreateObject("Word.Application")
WordWasRunning = False
End If

WDApp.Visible = True 'at least for testing!

With WDApp
.AppRestore
.AppMaximize 1
Set WDDoc = .Documents.Add(documenttype:=wdNewBlankDocument)
.InsertPara
End With

For Each wWsht In ThisWorkbook.Worksheets
For Each sShape In wWsht.Shapes
sShape.CopyPicture
WDApp.Selection.PasteSpecial Link:=False, _
DataType:=wdPasteMetafilePicture, _
Placement:=wdInLine, DisplayAsIcon:=False
Next sShape
Next wWsht

WDDoc.SaveAs Filename:="C:\my documents\word\test11.doc", _
FileFormat:=wdFormatDocument
WDDoc.Close savechanges:=False

If WordWasRunning Then
'leave it running
Else
WDApp.Quit
End If

Set WDDoc = Nothing
Set WDApp = Nothing

End Sub


While you're developing the code, you can use the references (called early
binding). Then you'll get the nice intellisense from the VBE. But when you're
ready to release, you can change to late binding.

Notice that the Word Constants are now just constants in my excel code. (Since
there's no reference, you'll either get a variable undeclared error (with Option
Explicit) or they'll just be undeclared variants.)
 
Back
Top