Macro does work on my computer but not on the computers of my colleagues

Question

I am trying to make a macro for my colleagues during my internship. I think my code should be fine. The only problem is that the code works on my computer but shows an error code when my colleagues try to us it. I already adjusted the code to their disc locations.

I really have no idea what is going wrong.

I received the following error code:

Run-time error '1004': Method 'SaveAs' of object '_Worksheet' failed

We are working in the same Excel version and network.

Is there someone who can help me?

Sub nieuw4()

    Sheets("Output IE").Select
    Sheets("Output IE").Copy
    ActiveSheet.Shapes.Range(Array("Button 6")).Select
    ActiveSheet.Shapes.SelectAll
    ActiveSheet.Shapes.SelectAll
    Selection.Delete
    Range("B8:U33").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    ActiveSheet.EnableSelection = xlNoRestrictions
    
    ActiveWorkbook.Saveas FileName:="U:\MACRO test\BREAKDOWN RAS TEST 17052021\Output IE\" & _
    "Breakdown - " & Workbooks("Test Ras breakdown per markt 20211.xlsm").Sheets("Grid inladen").Range("A2") & (" - ") & Workbooks("Test Ras breakdown per markt 20211.xlsm").Sheets("Grid inladen").Range("C2") & ".xlsx"

End Sub

Do the other users have access to write to the folder you are saving to? — Jeremy Hodge, May 19 '21 at 14:14
I changed the location in the macro on every specific computer to their own drive so they can save it where they want it. — Thijmen Van Der Meulen, May 19 '21 at 14:35
Why not save the `xlsx` file to the same folder as the workbook it came from ? — CDP1802, May 19 '21 at 15:06

Dominique · Accepted Answer · 2021-05-20T07:33:06.403

0

I don't believe there's anything wrong with your macro, but with the location you want to write to:

U:\...

This means three things:

You should have a drive mapping to that U:-drive. Do you have this on that computer?
You should have write permissions to write to that (network) drive.
The filename you want to use should not contain forbidden characters (/, \, *, :, ..., see this other post)
When you write something to a file, that file should not be opened by somebody else. Don't forget to check that.

edited May 20 '21 at 07:33

answered May 19 '21 at 14:01

Dominique

16,450
15
56
112

Thank you for your quick reply. I indeed have a drive mapping to that U:-drive. It is my personal one. How can I see if I have permission to write to that network or drive? So if the Excel file is open on the same network by a somebody else? It does mean that it is not possible to use it? Is there also the possibility that I did not Link the Macro correctly to a button or something? – Thijmen Van Der Meulen May 19 '21 at 14:07
I have mentioned all possible problems, the fact that there was no drive mapping is the most urgent one. I believe the other issues won't matter anymore: just make sure you have the drive mapping and all other issues won't matter anymore. Good luck. – Dominique May 19 '21 at 14:43
Okay thanks a lot I will make sure that the drives are mapped and hopefully this will fix the issue. – Thijmen Van Der Meulen May 19 '21 at 14:46
It could not solve it on the hand of your help, however I still do appreciate it a lot! The problem is that my cell reference C2 in the end of my code contains a value which cannot be saved as a file name, in this case 5/12/2021. I changed this to 05122021 and it works now :) My colleague has a language setting which writes down dates with slashes instead of a dash. This was the problem... Stupid that I did not look into this but it is fine now. – Thijmen Van Der Meulen May 20 '21 at 07:24

Macro does work on my computer but not on the computers of my colleagues

1 Answers1