How do you embed Access Report in Excel?

[Webtechie]

I need to create a master spreadsheet with dashboard qualities.

I will embed about 5 charts on a sheet using VBA. Now next to the embedded
chart, I'd like to show an Access report.

I don't want to export an Access report to an spreadsheet. I'd like to show
the access report in a part of a spreadsheet.

Does anyone know how to embed an Access report into a userform or embed it
in a spreadsheet? Using VBA?

Thanks,

Tony

 

[Joel]

Simply record a macro while going to the menu

Insert - Object - from file

the choose either line to file or show as ICON (if you want to actually put
the file into the worksheet).

Here is my recorded macro
ActiveSheet.OLEObjects.Add(Filename:="C:\TEMP\submissionx.mdb",
Link:=False _
, DisplayAsIcon:=True, IconFileName:= _
"""C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE""
/NOSTARTUP ""%1""" _
, IconIndex:=0, IconLabel:="C:\TEMP\submissionx.mdb").Select


Usualy I edit it like this

set obj = ActiveSheet.OLEObjects.Add( _
Filename:="C:\TEMP\submissionx.mdb", _
Link:=False, _
DisplayAsIcon:=True, _
IconFileName:="""C:\Program Files\Microsoft Office\Office10" & _
"\MSACCESS.EXE"" /NOSTARTUP ""%1""", _
IconIndex:=0, _
IconLabel:="C:\TEMP\submissionx.mdb")

Notice I removed the select from the end and put "set obj = " at the
beginning.

 

[Webtechie]

Joel,

Thanks for responding. I don't think I was clear in my question. This
merely puts an icon in the spreadsheet. When you click the icon, the
database opens.

In Excel, when you embed a chart into the spreadsheet, you can have several
charts next to each other and see the charts. I would like to have a a
viewer or something were I can put an Access report in the spreadsheet.

When looking at the spreadsheet, you would see a chart and next to the chart
an Access report.

 

[Joel]

I don't know if you want to automate th e process. I would have to do a few
experiments to write a macro but you can do it manually

Open the Access database and get the view you are looking for. then Press
Cntl- Print Screen (on top row of keyboard). Then go to excel and Paste the
picture. I know you can do this with code but haven't done it myself.


Excel has a copypicture method which in excel is equivalent to highlighting
cells then press Shift, and go to Edit menu on worksheet and select
CopyPicture. Then paste the picture. There is equivalent VBA method call
CopyPicture().