View Excel In Access

[sgfromny]

I'd like the ability to view an Excel Spreadsheet in an Access Form - Using a field on the form to indicate the filename of the xl Document.. Is this possible? If so, How?

 

[Remou]

You will need an unbound OLE control and some code:

Private Sub Form_Current()
If Not IsNull(Me.FileFullPath) Then
Me.OLEUnboundName.Visible = True
Me.OLEUnboundName.SourceDoc = Me.FileFullPath
Me.OLEUnboundName.OLETypeAllowed = acOLELinked
Me.OLEUnboundName.Action = acOLECreateLink
Else
Me.OLEUnboundName.Visible = False
End If
End Sub

 

[sgfromny]

Thanks for the help -

I do get this error though:
- Can't perform the operation specified in the Action Property of the visual basic procedure you're trying to run.

Any Ideas why?

 

[Remou]

Have you an Unbound Object Frame with the following properties:

[tt]OLEClass : Microsoft Excel 2000
DisplayType : Content
UpdateOptions : Automatic
OLEType : Linked
OLETypeAllowed : Linked
Class : Excel.Sheet.8[/tt]

 

[sgfromny]

OLEClass : Microsoft Excel 2003
DisplayType : Content
UpdateOptions : Automatic
OLEType : embedded
OLETypeAllowed : Linked
Class : Excel.Sheet.8

 

[Remou]

Have you set the Enabled or Locked properties?

http://msdn2.microsoft.com/en-us/library/Aa223185(office.11).aspx

 

[sgfromny]

Yes - Actually by Default Enabled was NO and Locked was Yes.
Changed them to Yes and No respectively and Still get an error:

Cant Link the OLE Object or the bound or Unbound Object Frame

 

[Remou]

And you have changed this line:
Me.OLEUnboundName.SourceDoc = Me.FileFullPath

To reference a textbox containing the full path to an excel file?

It may be worth testing with the name of a file, rather than a textbox.

 

[sgfromny]

Yes - Actually I have it set to a variable first
xlFile = "S:\Proposal\"& me.document & ".xls"
me.OLEUnboundName.SourceDoc = xlFile

Ive checked the filename and its correct..

 

[Remou]

Try inserting this before the action:

OLEUnboundName.SourceItem = "R1C1:R5C5"

OLEUnboundName - you have changed this to the name of your unbound control, have you not?

 

[sgfromny]

Yes but Still trouble - For simpilicty I named my control to OLEUnboundName

 

[Remou]

Are your references ok? Can you see the Excel file that you used in creating the unbound control?

 

[sgfromny]

If I create the Control from an XL file I can see the Spreadsheet, when I assign a new file to the control, it fails.

 

[Remou]

I give up, for now.

 

[sgfromny]

I surprised you hung on for as long as you did - Many Thanks!