Showing an Excel sheet on an Access report

[JoeyCrash2]

Hi,

I'm starting in this forum because I don't know where else to turn at this point. I've been searching for a couple of weeks on this and can't find anything anywhere, so if this has been addressed before, I apologize.

I'm altering an existing database for a client and am attempting to create a report that, on a per-record basis, pulls in a consistant range of cells from a spreadsheet (each record will have its own spreadsheet) as part of the Access report. The spreadsheets do not contain numerical or textual data, but are only used to create a diagram for each record (using outlines of cells), so all I need to do is show the sheet, as-is, in the report along side other data.

The report already contains a field from the query that generates the file path necessary for each record's spreadsheet (based on a record number). Try as I might, I can't get the report to show the spreadsheet unless I create a link using an unbound-object frame. However, doing so limits me to a single file link. I need the link to dynamically update, dependent upon the field containing the file path. Hopefully there's some way to do this with VBA, but I'm a newbie, so I only know a little bit about the VBA language.

Any help is greatly appreciated.

Thanks,
JV

 

[sparks80]

Hi,

It is certainly possible to display an excel spreadsheet directly on an Access report using an unbound object frame.

Have a look at this web page from the Microsoft Knowledge Base. It describes how to programatically link an object on a form, and uses a spreadsheet as the example. Although this describes the method for a form, the same should also work on a report.

I am not sure which event would be best to place the code in, possibly the On Current event - this will fire each time a new record is selected. This will probably work if only the current record is being displayed on the screen. If you intend to print multiple records at once, then you may need to use the On Print event instead.
As a word of warning this may be relatively slow as it will need to load each spreadsheet in turn.

You will need to set the SourceDoc equal to the field containing the spreadsheet path.

Let me know if this helps (or not!), or if you need any help implementing the code.

 

[JoeyCrash2]

Sparks80,

Thanks for getting back to me so quickly. I found the page on programmatically linking an object, but I ran into the same issue as with other attempts. I cannot set the source doc to the field that contains the file path. When I set the source doc as such:

.SourceDoc = [FPath]

and try to run the report, I get "Run-time Error '2101': The setting you entered isn't valid for this property." I get similar errors when attempting to put the field name in the Source Doc filed in the object Properties box.

I think this has been the biggest stumbling block all along with my attempts at this. Any ideas?

Thanks,
JV

 

[JoeyCrash2]

Does anyone have any ideas on this? I'm completely stuck....

 

[JoeyCrash2]

Okay, so, if anyone is still looking at this thread, I have been able to make it work sometimes, but not always and I have no idea why. It worked last week intermittently and now it's not working at all. I set the .SourceDoc equal to my field name without any quotes or brackets and that's when it worked, but only if I ran the report and then clicked on the field that holds the file path, not the linked object. I don't know why it even updates when I click on the field because I have nothing set for OnClick, yet that's the only way it would update. Now it's just telling me to Debug and that my .SourceDoc setting is invalid. I really thought I had it last week, but now I'm a bit more stumped. Help?