Using OLE frame with an Excel sheet for a report template

[Guest]

I am trying to use an Excel file as a template for an Access report. Linked
the excel file through OLE frame with the properties set as follows:

Source Doc : C:\sajit\test.xls
Source Item : test1

where test1 is the range name in which the template is.
I also have other sheets which are used as templates for other reports. The
templates were made by making a 'Copy' 'Sheet' from the previous sheet, each
sheet contains one template range.

When I preview the report, Access picks the wrong template, even though the
filename and the range name specified in properties are correct. Excel seems
to have a problem with defining range names, please see the post titled
'range naming problems' under Excel Application errors.

Sajit
Abu Dhabi

 

[Arvin Meyer [MVP]]

Instead of using an OLE frame, try linking directly to the spreadsheet. This
will place the spreadsheet into the Access Tables container, where you can
work with it just like any other table.

 

[Guest]

Thanks for your response, Arvin,

The data table controls that is overlayed over the template are linked like
a table.
My question is related to the link of the excel sheet that is linked to the
OLE frame.

The back drop can also be made by inserting as a picture to the report.
However this has the disadvantage to making the tabular template appear
disproportionate in the report.

Is it possible to link the template as a table link?

I could possibly send you the .mdb and the .xls files to show you what I am
trying to do.

 

[Arvin Meyer [MVP]]

If you can use a column in the spreadsheet, and a field in the database to
store the path to the file, you do not need to use an OLE control. Instead
try using an Image control, setting its Picture property to the path:

Sub Form_Current()
Me.NameOfControl.Picture = Me.txtPath
End Sub

Where txtPath is the name of the control which sources the Path field in
your table.

 

[Guest]

I am using the Excel file with a tabular layout made of Excel cell borders.
The cells are filled with the controls. The layout occasionaly have to be
changed to accomodate larger text lengths in some of the fields.

The image control does not accept an Excel file for a source. Even if I were
to print the Excel as a graphic it prints itself to a larger than actual
proportion. The control allows to scale it. But that will be too much hassle
for the task, considering the modifications.

It would have been convenient if I were able to use the unbound control and
the Excel file.

Is this an Access bug?

Would you know any other work around?