[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Unbound OLE object in report update

Posted on 2011-10-27
Medium Priority
Last Modified: 2013-11-05
Hi Experts,

I have i report where i link to a word file through a OLE object. But i would like to change the link to another file  through VBA code. Can someone give an example how to do that?
LVL 12

Accepted Solution

danishani earned 2000 total points
ID: 37038014
You can use a Function like this below, found in this thread:

Public Function DisplayDoc(ctlDocControl As Control, strDocPath As Variant) As String

On Error GoTo Err_DisplayDoc

Dim strResult As String
Dim strDatabasePath As String
Dim intSlashLocation As Integer
Dim strImagePath

With ctlDocControl
 .Visible = True
 .Enabled = True
 .Locked = False
 ' Specify what kind of object can appear in the field.
 .OLETypeAllowed = acOLELinked
 ' Class statement--optional for Excel worksheet.
 .Class = "Microsoft Word Document"
 .SourceDoc = strDocPath
 .Action = acOLECreateLink
 ' Optional size adjustment.
 .SizeMode = acOLESizeZoom

strResult = "Document found and displayed."

End With

DisplayDoc = strResult
Exit Function

 Select Case Err.Number
   Case 2101       ' Can't find the picture.
 ctlDocControl.Visible = False
   strResult = "Can't find document."
 Resume Exit_DisplayDoc:
      Case 2455
        Resume Next
      Case Else       ' Some other error.
        MsgBox Err.Number & " " & Err.Description
        strResult = "An error occurred displaying document."
      Resume Exit_DisplayDoc:
  End Select
 End Function

Open in new window

Then in you can do something like :
Dim strWordDoc As String  
Dim ctl As Access.Control  
' or use the Common dialog to selct the new doc
strWordDoc = "C:\YourPathName\YourFileName.docx"
Set ctl = Me.ctlOleDoc  'chang the OLE object nam accordingly
DisplayDoc ctl, strWordDoc

Open in new window

Hope this helps,

Author Closing Comment

ID: 37042862
Just what i was looking for.

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question