Embedded Excel Files

[Stang70Fastback]

So I have this lovely, pretty Excel sheet full of conditional formatting that is used as a vehicle assignment board where I work. I know I can embed the file into an Access form using an unbound object, and therefore "integrate" it into Access, however this creates a copy of the file, and therefore when people change the sheet, the original Excel file is not updated. Is there any way to embed the excel file in such a way that the Excel document itself is also updated? Basically some sort of an active, two-way link rather than what amounts to just importing a copy of it? I need to be able to pull data from various cells in that Excel file so it needs to be kept up-to-date. Thanks for your help!

 

[Beetle]

You can link to an Excel file as if it were a table then display it in your form. When the form is refreshed any updates to the Excel file will be reflected in the form.

 

[Stang70Fastback]

Thanks for the reply. I suppose that would be one way of doing it, however I am trying to create an "integrated" program, and so I hate the idea of them having to have a separate window open with Excel running. I'm starting to think I might just have to re-create the Sheet in a Form in Access, and link each individual vehicle assignment to an underlying table. I'm just not sure I'll be able to pull off the conditional formatting in the same fashion.

 

[Beetle]

It doesn't have to be in a separate window. It could be a sub form that takes the place of the object frame you're using now.

 

[Stang70Fastback]

I'm a bit confused. Let me try to explain more clearly WHY I am confused and maybe you can set me straight:

I have a "Bus Board" in Access, a screenshot of which is included here. You can see the overall formatting and the conditional formatting in effect.

I have never worked with embedding Excel documents into Access, and the only way I got it working was using an unbound object frame in the form. This gave me the option to display a "picture" of the sheet, which would be fine, but I need people to be able to edit it, so I chose the other option. However, I then discovered that this doesn't actually link to the source file I point it to. It simply imports the Excel file and embeds it. Everything works fine, but the source file does not change, which is a problem, because I need to link to the second worksheet on that source file, which has the assigned vehicles in one long column, which I can then create a link to in Access to use in other circumstances.

So basically, either I need a different way of embedding my Excel file that WILL update the original file, or I am not seeing something obvious, such as that since this excel file was completely imported, I should be able to link to the second worksheet of that file which is now IN Access, as opposed to linking to the one in the source file.

I think what I did not make clear is that I am not just VIEWING an Excel file, but EDITING it. I basically want to be able to EDIT the Excel file FROM Access, but then also be able to pull data FROM that file into Access to use in other means. I know that sounds redundant (sending data out and then bringing it right back), because it is, but I don't know of a way to achieve the same sort of formatting in Access to simply keep the data IN Access to begin with, unless you can help me with that.

Does that make sense?

 

[Beetle]

OK, I see that my previous advice isn't really going to help in this scenario. I personally don't know of a way to do what you are describing with the Excel file itself. Certainly, if you had all of the raw data in Access, you could probably put together a form that would display this data in a way very much like your Excel screen shot there. However, as I'm sure you're aware, Access handles data very differently than Excel, so a display like this would likely take some totals or cross tab queries, which would be read only, so you would need a separate method to edit the underlying data. I have done a few applications like this. Sometimes there's a need to have the data displayed in a way that isn't conducive to direct editing in Access, so I usually use the click event of a button or a text box to open another popup form for editing the underlying data, then upon close of this form the display data is requeried. Not sure if that's the direction you want to go with this or not, just a thought.

 

[Zurvy]

Any luck regarding the solution for this scenario?

I may have the answer...