Linked Excel workbooks problem

[stoolpigeon]

Each member of our team in the office uses their own individual excel file to store their 'work in progress' which all use the same template. I've recently fed these into Access via linking the excel files and then wrote a query that is a union of each of these links, so that we have a consolidated representation of all the work we're doing in a single query.

The problem I'm having is that if anyone has their individual workload open on their computer, the query fails to run and kicks up an error complaining that because that file is in use on another computer, Access is unable to access it and populate the query.

I've fairly extensively googled this problem and the only solution I have found so far is that it's possible but via a bit of vba coding. I'm fine coding some VBA for excel but haven't the first clue how to achieve this in Access.

Any help would be greatly appreciated.

 

[CJ_London]

Probably the easiest way to do this is to use the VBA FileCopy function to copy each users spreadsheet to a folder in your domain and then base your query off these.

FileCopy allows you to change the file name as part of the copy so it won't be an issue if all the users have the spreadsheet names the same.

Also, the spreadsheet does not need to be closed for the copy routine to work

 

[stoolpigeon]

Thanks for your reply. I can write the code to locate the files in their respective directories, to copy them, draw the data and then close and delete the copies, but how do I 'attach' this to a query?

I need this to work with an existing query, as opposed to creating a new one each time, as that query is linked to another excel workbook (reporting on the consolidated workloads in access)

 

[CJ_London]

By linking to them - see attached

 

[stoolpigeon]

I understand how to link the two but if I'm creating copies of the files in question then how can I automate copying and then linking to Access? Do I have to predetermine the name of the copy? And then cause the VBA to trigger on db open?

 

[CJ_London]

This is very similar to another post I am responding to - please take a look as it may help

The link is http://www.access-programmers.co.uk/forums/showthread.php?p=1253705#post1253705

However to answer this question:

1. Create a folder in your domain called Import
2. use the VBA Filecopy routine to copy the other users files into this folder
3. In Access, create a linked table to these files
4. In Access, modify your query to be based on the linked tables
5. When it is time to replace the files, run the VBA routine again - the files in your import folder will be overwritten and your linked tables will still be linked There is no need to relink or to rewrite the query

Do I have to predetermine the name of the copy

Yes - but assuming it doesn't change in the users domain and each users domain has a different name for the file you can use the filename in their domain

And then cause the VBA to trigger on db open?

If you want to, or put it on a button or when you open a form, or set it to a macro, or an on timer event. Depends what you want.

If you are looking for a real time view (i.e. user updates to excel are immediately reflected in the db) then I suggest this is not the way to go.

VBA code is VBA code, doesn't matter whether you are using it in Access or Excel, the only difference is that in Access you are working with Access objects (tables, queries, forms reports, etc) and in Excel you are working with Excel objects (Workbooks, worksheets, ranges, cells etc). Each can work in the other by creating a suitable reference.

So if you are more comfortable writing VBA in Excel, you could create a reference to Access and have a macro run automatically when the user makes a change to update a table in Access.

 

[stoolpigeon]

Thanks for your help on this. Rather than including the VBA code in Access I had each workbook create a copy on save and had that copy linked to Access. Simple when I thought about it. Thanks again.

 

[CJ_London]

no problem - there is usually more than one way to make an omlette!