Hello everyone!



I am stumped. I want to automate my team. I send a report every few hours which has data that updates throughout the day in our company software. Currently I need to export to excel data from our software and copy the export into excel, run a macro, and resend a link to the new file template on our shared drive

What I would like to do is send one report out that will update throughout the day whenever I update my main report using tables or a data connection. To do this I am thinking I can paste the export into the macro, import the results into access, and then import directly into a template that can be updated all day long whenever I update the source. I tried going from excel to excel but you cannot share a workbook with a table.

Is this possible? Is there an easier way? Thank you in advance.

I don't understand all that, but let's start with part you identified as the problem:I'm not sure what this means. You're trying to save an Excel workbook from one machine, then open it in Excel on another PC? Naw, that's too simple. Maybe the clue is the word "share": You want to have two sessions of Excel have the same workbook open at the same time? Probably not, but in any case I don't see how a table would affect that. What do you mean by "share" (or by "going from excel to excel")?

And while I'm at it, what do you mean by "table"? Because after all, everything you put in Excel is, in a way, a table. Do you mean an area in Excel that's been named, maybe? (Although I don't know why that would make any difference either.)

Let me try again. I would like to take data from workbook A and have it update mirrored workbook B using a data connection. When I make a change to workbook A I want it to update in workbook B when refreshed. I have about 12 people using workbook B so I need the connection to work even if being used. I brought access into this because I have not been able to accomplish this going from workbook A to B. Any help would be appreciated.

Oh, you really did mean "share" in the complete sense. I'm inclined to think you're right, that if you want updates to be placed in B even while people are using B, you'd have to make it a database. It doesn't have to be Access—I mean, it could be Oracle or whatever—but you probably can't do that in Excel. Excel complains about just two people trying to have write access to the same workbook, much less 12.

Now, if you're really in love with the idea of doing it in Excel, then there are probably ways to get fairly quick updates. Any changes could be written to a file, and then as soon as the workbook is free—not being updated by anyone—a program could run that would load the changes into the workbook, so they'd be available to the next person who opened it. I'm not sure how that would be done, but I can think of a couple possibilities. But it wouldn't be true concurrent sharing. It's the best I know how to do, though. Sound interesting?

The issue that I have is that between all 12 users they use the reports throughout the day so I would need to find a way to push updates if possible. This is why I figured access since it would be excel to access then access to another excel workbook it would work.

Well, Access (like most database apps) is constructed to allow multiple users concurrent update. But I don't know of any other Office app that does that. It doesn't matter (AFAIK) that you're writing a VBA program to do it, because VBA is going to invoke Excel to do the actual updates—your VBA program is "calling" Excel—and Excel is going to complain, when you try to update a workbook, that someone else is currently using it. And likewise it doesn't matter that the request to update the workbook is coming from Access; the request still goes to Excel, and Excel still knows better than to let two people update a document at the same time. If you want true concurrent update, it has to happen in Access, to an Access database. And yeah, you can get Access to update the Excel workbook later, for reporting purposes; but only at a time when no other user happens to have it open.

Wait a minute, I just thought of something: You said other people, up to 12, may be using that workbook. But are they updating the workbook, or only reading it?

If they're only reading it, not changing anything, then they can open the workbook as Read-Only. Then Excel can update it. Would that work for you?

Correct. They are doing updates but none that need to be saved. Using the data wizard I connected workbook A to workbook B already and it works but i can only open workbooks A in read only unless Workbook B is closed and vice versa.

My team will be making their own edits to the workbook throughout the day but if this works would press a refresh button to receive the new data from workbook A.


Can I import workbook A into access and link it to another local table that could be imported into workbook B? I am new to access and don't know databases very well.

Thank you again for your input.

What I'm thinking is that if you can get these users to open Workbook B read-only (since they're not going to be updating it), then I should think Excel will accept your program making changes to it while they have it open. And in that case you don't need Access at all; the process you already have working will do the job for you.

To test this hypothesis, have one of the users open Workbook B with the Read-Only option, and while you know he has it open with read-only, and no one else has it open at all, try updating it with changes. I think it'll work.

If a user just double-clicks on a workbook icon, Excel won't open it read-only. There are two ways I know of to do it:

1) In Excel, use the dialogue in File, Open. Point to the workbook you want to open. Instead of hitting the Open button, click on the down arrow next to it and select "Open Read-Only". Now the file is open in read-only mode, and it'll say "[Read-Only]" in the title bar; if the user absentmindedly tries to save it, Excel won't allow it, but will insist that he use Save As to a different name if he wants the changes to be saved anywhere.

2) You can remind your users to open as read-only (and incidentally allow them to open by double-clicking on the icon in Windows) by setting the "Read-only recommended" switch for the workbook. To do this, use Save As. Before clicking on the Save button, select the Tools drop-down menu beside it, then General options. Set the "Read-only recommended" checkbox. After clicking OK, then you can save. After that, if anyone double-clicks on the icon, Excel will pop up a message saying that read-only is recommended for this workbook and offering them a choice.

Now, both methods are voluntary; your users could, if they chose, open the workbook without the read-only setting and make changes to it. Maybe that's the way you want it. But if you decide you want to make it harder, there are fancier things you could do. You could, for example, make it read-only in Windows; after that, no one can update it without changing that setting. Your program would have to temporarily turn off the read-only switch, update the data and then turn read-only back on. Someone might happen to open the file for update during the few seconds your program was doing that, but it's unlikely.

Or you could get your Windows security people involved, setting up the access rules so that only a certain account (the one you use to save changes) can update it and everyone else is read-only.

But if you want others to be able to update Workbook B on occasion—just not to do it normally, or forgetfully—then the "Read-only recommended" switch sounds like the right bet to me.

Perhaps a simpler and less error-prone way of doing this is to have one workbook that gets updated from the database and, linked to that, another workbook (or a series of workbooks, one for each user) that can be used for viewing. That way, there's no risk of the workbook that has to be updated by the system being locked by a user who forgot to open it as read-only. With the linking workbook(s), the data links can be refreshed on demand.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

I like it! Except for the Active-Directory suggestion, all the methods I thought of were, shall we say, risky—they might have failed through accidental non-cooperation of users. This is much better, I think. Sure, a user could still munge up the works by refusing to go along with the program, but it'd be a lot harder to do inadvertently.

Thank you! I had so many issues still with all methods. I ended up putting a macro in the workbook that connects the data source, updates it, then unlinks it. So far no issues.