# Excel VBA problem with file manipulation



## babar80 (Oct 16, 2006)

Ok, here is my problem : 

I have 5 files each the same except the data inside them.
Each has the same macros.

My aim is to have at each file launch a shared excel file launched in read only that displays all data of all these 5 files. (this is now donw).

However, there shoul be a way to update this shared file automatically.
I thus designed a macro to update the shared file (everything works fine if only one users updates it at the same time)

But I still have 2 problems :
- I cannot assign a shortcut to my macro (the macro having to be used a certain amount of time in a day, I do not want to use the menus to launch it) : if I assign a shortcut to the macro, the macro will stop in the middle of opening all files before copying their content in the shared file : 
here is the "faulty" code : 
Sub UpdateGlobalPreconds()

Dim searchEndLine As Integer
Dim copiedLines As Integer
Dim filesToCopyPath As String
Dim ReferenceFile As String
Dim FileToUpdate As String
Dim PrecondListSheetName As String
Dim temp As Integer
Dim tempString As Variant
Dim usersNumber As Integer
Dim Users(4)
Dim Files(4)
Dim FileNames(4)

Application.ScreenUpdating = False
' Application.EnableEvents = False

'définition du nombre d'utilisateur
usersNumber = 5

'Définition de la feuille à copier
PrecondListSheetName = "BusinessLogicGlossary_Precond"

'définition du fichier à mettre à jour
FileToUpdate = "Rules_Preconditions_Glossary-Global.xls"

'stockage du nom du fichier initial
ReferenceFile = ActiveWorkbook.Name

'définition de la liste des utilisateurs.
Users(0) = 1
Users(1) = 2
Users(2) = 3
Users(3) = 4
Users(4) = 21

filesToCopyPath = ActiveWorkbook.Path & "\"
searchEndLine = 2

'définition des fichiers à ouvrir
For temp = 0 To usersNumber - 1
FileNames(temp) = "Rules_Preconditions_Glossary" & Trim(Str(Users(temp))) & ".xls"
Files(temp) = filesToCopyPath & FileNames(temp)
Next

'ouverture de tous les fichiers à copier

IN THIS LOOP, IF A SHORTCUT HAS BEEN USED, THE LOOP WILL END AFTER THE FIRST FILE OPENING AND THE MACRO STOP 
For temp = 0 To usersNumber - 1
If FileNames(temp) <> ReferenceFile Then
 Workbooks.Open filename:=Files(temp), ReadOnly:=True
End If
Next

[...]
End Sub

- I still need a way to update almost automatically the read only opened shared file for all users. I have used the ChangeFileAccess method on sheet activate to switch from read only to read/write and back to read only, but this doesn't realy seem to work well (it happens that on refresh, file stays in read/write mode and doesn't switch back to read only mode, which is pretty harmfull because the macro update won't be able to update the shared file anymore) : here is the code : 

*
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

If ThisWorkbook.ReadOnly Then
ThisWorkbook.ChangeFileAccess Mode:=xlReadWrite, Notify:=True
ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly, Notify:=False
Else
ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly, Notify:=False
End If*

End Sub


Any Idea is welcomed, because as I really do not master excel nor vba, I'm kinda stuck in the glue for now

Thks


----------



## Glaswegian (Sep 16, 2005)

Hi and welcome to TSF.

I would use John Walkenbach's method for extracting data from a *closed* workbook, rather than opening each workbook. The function is shown here - there is an example that takes values from more than one cell. You'll need to do some tweaking but it should work just fine. Alternatively, try this example which loops through closed workbooks in a folder - note that the workbook structure must be identical (which I think yours is).

You could also use the *OnTime* method to set up a macro for a specifc time to run, say 2.00pm every day. Chip Pearson has some good examples here..

Hope this helps.


----------



## babar80 (Oct 16, 2006)

thxs for the tip !

I'll try it tomorrow and see if it solves my shortcut problem (I bet it will...)

Concerning the onTime method, I'll check if there's a way I can accept a regular update but perhaps the first modification will do the trick or I'll change the concept from updating a file to an autoupdated file. I'll keep you posted

And thxs again for the help


----------



## babar80 (Oct 16, 2006)

Hi again.

I tried the read from closed workbooks thing and it does work pretty well ... when the files it reads from aren't opened by anyone.

The only remaining problem is here a performance problem : is there a way to have the copy be as fast when files are opened by other people as when they aren't ? I cannot use the ontime method because I need to be able to refresh the file at will, but as the shortcut key problem doesn't happen anymore, it's ok this way.

I tried to use QueryTable to see if refresh could be faster, but I came to the same problem of performance when read files are opened by another user, in addition to the systematic opening of the linked file in read only mode when it was opened by someone else.

So if anyone has some ideas about those issues, I'll be glad to hear it

merry evening to everyone

Elephantman


----------

