Permission denied - Run time error 70

[TG2812]

Hello,

I'm trying to duplicate and save my active workbook but for some reasons, the following error is popping up during the macro execution;
"Permission denied - Run time error 70". I tried to browse around but i still cannot find where I made the mistake in the coding.

Any help would be much appreciated.



Application.CutCopyMode = True
Application.ScreenUpdating = False


Dim newname As String
Dim i As Integer
Set Obj = CreateObject("WScript.Shell")
desktop = Obj.SpecialFolders("Desktop")
current = ActiveWorkbook.Path


newname = "myname1 " & " - " & Environ("UserName") & " - " & Replace(Now(), "/", "-")
FileCopy current & "" & ActiveWorkbook.name, desktop & "" & newname & ".xlsm"

 

[Yongle]

Try replacing

newname = "myname1 " & " - " & Environ("UserName") & " - " & Replace(Now(), "/", "-")

with

newname = "myname1 " & " - " & Environ("UserName") & " - " & Replace(Replace(Now(), "/", "-"), ":", "-")

Colons are not permitted in file names

 

[TG2812]

Hi Yongle,

Thanks for your help. However, it still does not work.
Perhaps I can take the problem from another angle..do you know how to instruct VBA to check in the activeworkbook.path contains another file with the same name and date. If so then it adds (1), (2) etc..? That would be even better.

 

[Yongle]

it still does not work

A path separator is required before the filename

amend this line

desktop = obj.SpecialFolders("Desktop")[COLOR=#ff0000] & Chr(92)[/COLOR]

 

[TG2812]

Hi,

I amended the line and still does not work =(

Application.CutCopyMode = True
Application.ScreenUpdating = False


Dim newname As String
Dim i As Integer
Set obj = CreateObject("WScript.Shell")
desktop = obj.SpecialFolders("Desktop") & Chr(92)
current = ActiveWorkbook.Path


newname = "myname1 " & " - " & Environ("UserName") & " - " & Replace(Replace(Now(), "/", "-"), ":", "-")
FileCopy current & "" & ActiveWorkbook.name, desktop & "" & newname & ".xlsm"

 

[Yongle]

instruct VBA to check in the activeworkbook.path contains another file with the same name and date. If so then it adds (1), (2) etc..? That would be even better.

Int(Timer / 60)
VBA Timer function counts actual seconds seconds since midnight to NOW()
Timer increments throughout the day
Divide by 60 to convert to minutes and convert to integer
So number that will be added to file name ranges from 0 to 1440 (ie 60 * 24)
Easier to use Timer than to test for repeated versions of "today's" file name

& Chr(92)
A path separator is required before the filename

& Replace(Date, "/", "-")
- replaced NNOW with DATE

Set obj = CreateObject("WScript.Shell")
desktop = obj.SpecialFolders("Desktop") [COLOR=#ff0000]& Chr(92)[/COLOR]
current = ActiveWorkbook.Path
newname = "myname1 " & " - " & Environ("UserName") & " - " [COLOR=#ff0000]& Replace(Date, "/", "-")[/COLOR]
'if file exists use a different name
If Not Dir(newname, vbDirectory) = vbNullString Then newname = newname [COLOR=#ff0000]& " " & Int(Timer / 60)[/COLOR]
FileCopy current & "" & ActiveWorkbook.Name, desktop & "" & newname & ".xlsm"

 

[Yongle]

Just wondering

FileCopy current & "" & ActiveWorkbook.name, desktop & [COLOR=#ff0000]""[/COLOR] & newname & ".xlsm"

Is the forum deleting your path separator ? (IT HAPPENS!)
- have a look at your first post
- did your code originally have a separator between the quotation marks?

If so do not need to add Chr(92)

 

[Yongle]

replace final line with this to check the final path is correct

MsgBox ActiveWorkbook.Name, desktop & "" & newname & ".xlsm"

 

[TG2812]

I'm probably missing something. Here below is the code I currently have. I confirm i have path separator.

Application.CutCopyMode = True
Application.ScreenUpdating = False


Dim newname As String
Dim i As Integer
Set obj = CreateObject("WScript.Shell")
desktop = obj.SpecialFolders("Desktop")
current = ActiveWorkbook.Path


newname = "myname1 " & " - " & Environ("UserName") & " - " & Replace(Replace(Now(), "/", "-"), ":", "-")
FileCopy current & "" & ActiveWorkbook.name, desktop & "" & newname & ".xlsm"

 

[Yongle]

What does MessageBox suggested in post#8 tell you?