ACCESS 2003 Excel 2003 : VBA for opening Excel file from Access and copying a pictre from excel then returning it to Access form

So I have an excel workbook that has a nice global map of shaperange objects. With some very simple code I can change the colors, group and ungroup collections of countries into arrays, etc...and it works pretty well. However, I would like to bring this into Access.

So I could copy and paste all the shapes into an access form manually, but then they become pictures and I cannot change the colors of the countries (shaperange objects) to have the map act interactively as I can in excel.

So I am thinking that I know how to use excel functions from access, and how to open excel from access. Is there a way to copy an object from excel (I know the file name and the shape name that i mean to copy everytime), and bringing it back to access to paste on a form?

Atypical, I know, all my Access questions are. Thanks!

EDIT:

I would be happy to elaborate my half crazy idea. So I know how to open a new excel instance, or open a template behind the scene (w/o actually making it visible). And I know how to use vba to preform excel tasks, from access modules.....or even run formulas and excel functions from access. i have this interactive map program that works with excel modules, but I would like to make them a part of an access form.

So to answer the below question, if I can just find the missing link with copying and image/shaperange object from excel, and copying it into access form, then I can write code that will always grab the shapes from my excel template format the shapes in excel before copying and pasting into access form, where layout formatting can take place. when a user selects something on a form were I want to include an area of a global map with relative colors/shading, etc...access will always get the pre-defined map pieces it needs from a collection of object in a file/excel template. My idea was after the selection, I would format the shapes, just like I already do with vba, then ole.copy and ole.paste back into the access form. If it is a picture at that point, it doesn't matter. Atleast for the shape itself

lots of code but i think it is worth a try to try to incorporate excel and this shaperange object based map into a form enviroment like the access forms! Pretty strange but it might work well?? Some weird atypical things have become staples in my access database.

Well you're asking this:

"So I could copy and paste all the shapes into an access form manually, but then they become pictures and I cannot change the colors of the countries (shaperange objects) to have the map act interactively as I can in excel.

"So I am thinking that I know how to use excel functions from access, and how to open excel from access. Is there a way to copy an object from excel (I know the file name and the shape name that i mean to copy everytime), and bringing it back to access to paste on a form?"

Maybe some potential responders are asking themselves why if the present solution is not good enough to paste into an Access form because "then they become pictures," but it's okay to Automate the same procedure that you believe is substandard.

Maybe rephrasing the question would help.

sure....short answer. if i automate, then I can format before I paste it back into access with same code I use now to do it. user clicks in access, access calls excel, shapes are select and formatted there, brought back to access, and appear on form.

Justin 2010-03-27 22:36:27

Describe in detail what constitutes the object you want to lift (copy) from the Excel sheet. Is it a combination of Drawing Objects positioned on top of a photograph, along with the photograph underneath--all intended to be gathered up together and reproduced on the Access Form? Is it just a Group(ed) combination of Drawing Objects that, together, form a nice image that you'd like to copy over to the Access Form?What is the collective composition of this thing on the Sheet that you want to mimic on the Form?

2010-03-28 02:44:53

Also, I guess OLE linking and embedding is out. Remember, you could set one of those into your Form, and you'd actually be using Excel in the client area of that pane within your Form. Just another idea. (But be no means the only way to go here. Just give us some more information about what exactly is on the Excel Sheet. Exactly.)

2010-03-28 03:08:32

that brings up an interesting idea.....if I embedded an excel spreadsheet into a form as an OLE object, could I simply use the same vba in the form's module I use in Excel? I know have to set the reference but is that all....do I need to reference the "frame/object name"

Justin 2010-03-28 03:59:50

Look at: support.microsoft.com/kb/158929

2010-03-28 06:19:44

But you still haven't told us what you're organizing on the Sheet, which is important to our attempt at understanding what the solution to your problem might be.

2010-03-28 06:21:27

One solution if you want to go the OLE route, is to send VBA Automation instructions from Access to Excel to change the material in Excel, and then update the Access Form with the results of that Excel depiction.

2010-03-28 06:23:47

they are just shapes, drawn objects. Some have been put into arrays where applicable (because the phil islands are a bunch of shapes for one entity). if you want we can, even for the sake of example say that it is just one shape (like a square) from the drawing menu that has been placed on the sheet. Thanks for your help!

Justin 2010-03-28 13:30:54

yes exactly, the OLE route you describe is exactly what I am looking to accomplish. thanks!

Justin 2010-03-28 13:31:33

Server-side, from VBA on Excel, you can also fire a .NET app to take a cropped snapshot of anywhere on the screen (after bringing your Excel to the forefront in the Z-Order). Then use VBA from the same side to send, from Excel, an instruction to Access to save/update itself before anytime a Form is launched user-side. I don't know if this is a client/server setup w/ mulitple users.

2010-03-28 15:34:31

ansaurus

tags:

views:

answers:

ACCESS 2003 Excel 2003 : VBA for opening Excel file from Access and copying a pictre from excel then returning it to Access form

related questions