Pass macro to another workbook

[BrendanDixon]

Hi All,

I hope someone can help me here. I am creating a macro to update the workbooks of users. What will happen is
1. The user will have a workbook opened in folder other their choice, but usually their desktop.
2. when the opened workbook detects there is a newer version.
3. The opened workbook will be renamed as a temporary file in the same folder.
4. It will then delete the original workbook from the folder and then copy the new workbook from the server to the original files directory.
5. It will then copy certain data that the user had in their original workbook which is now the temporary file to the new workbook.
6. I then want to close the temporary file and delete it. This is the tricky part

I did manage to have some code to do this. but it would close the workbook and delete it but would leave excel open and the user would have to close excel and then open the workbook again. which I thought was not ideal

I then came across this method of Application.OnTime Now(), "'TheNewWorkbook.xlsm'!Macro"
Where TheNewWorkbook is the updated workbook in the users folder that the temporary workbook will pass the macro to, and
Macro is the macro in the new workbook that will close and delete the temporary file.

This system worked perfectly in my test code. The problem I have is that the users may rename the workbook to what they think it should be. so the 'TheNewWorkbook.xlsm' needs to be dynamic. I had the following

Dim Name as string

Name = ThisWorkbook.Name ' This is set while original workbook is opened

Application.OnTime Now(), "'Name'!Macro"

The problem is that when it gets to Application.OnTime Now(), "'Name'!Macro"
It comes up with an error that it "cannot find "C:\Users\abc\Documents\Name.xlsx" even though it is on the desktop with xlsm extension
and then "cannot open Name.htm"


Can anyone tell me what is happening here?

 

[GWteB]

Not for sure but you could change ThisWorkbook.Name in

ThisWorkbook.FullName

and see what happens ....

 

[BrendanDixon]

Hi WWteB,

I have already tried that. it will then come up with the error "cannot find "C:\Users\abc\Documents\C:\Users\abc\Desktop\Name.xlsx"
and "cannot open C:\Users\abc\Desktop\Name.htm"

 

[GWteB]

I overlooked this part

I did manage to have some code to do this. but it would close the workbook and delete it but would leave excel open and the user would have to close excel and then open the workbook again. which I thought was not ideal

I suspect that you also close the workbook where your code runs. That could possibly cause that error. If so, it might be wise to have a separate workbook with your code from which you direct everything that needs to be done. Excel therefore does not have to be closed by the user.