Excel Save Error

[psctornado]

Hi All,

I have a bit of an issue that I just cannot seem to resolve. Currently I have the following as a code to save a copy of my existing workbook to my documents folder, so that it assists me in my mail merge.

Everytime I then save a second copy of the workbook by going up to 'File --> Save', I get an error 'Location is not available, C:\Users\My Name\MyDocuments is not accessible'.

error.jpg

I want to say the reason for this is my copy code. Would anyone have any suggestions on the below code?

Sub Save()
ActiveWorkbook.SaveCopyAs Environ("userprofile") & "\My Documents\My Workbook\My_Workbook.xls"
End Sub

Any help would be greatly appreciated!

[SamT]

I'm using office 2002 and Environ("userprofile")

on my system returns

"C:\Documents and Settings\" & User Name

[Paul_Hossler]

ActiveWorkbook.SaveCopyAs Environ("userprofile") & "\My Documents\My Workbook\My_Workbook.xls"

1. What OS and what version of Excel?

2. Any you're SURE that 'My Documents' and 'My Workbook' folders both exist?

3. You could try

ActiveWorkbook.SaveCopyAs Environ("userprofile") & "\Documents\My Workbook\My_Workbook.xls"

and see it that works. I believe, that in Win7, the 'My Documents' folder is a junction to the 'Documents' folder

Paul.

[D_Marcel]

I did some research about this error and found nothing. I'm using Windows 7 and Office 2010, Environ("username") returns to me "C:\Users\" & User Name.

Is it possible that have some configuration in Windows to enable programmatic access to Windows Explorer?

[Paul_Hossler]

Did you try and save to ...\Documents\... and not ...\My Documents\... ?

Paul

[psctornado]

Hey Paul & D_Marcel,

I have Win 7 Enterprise & Office 2010. I'm sure I have both My Documents & My Workbook folders. I tried to update the My Documents to 'Documents'. This macro is connected to a button to save a copy into my 'My Documents' folder, which it successfully does. The issue is when I try to save the workbook after the fact that I get the above error. Do either of you have suggestions of other 'copy workbook' code?

This never gave me an issue before we upgraded from XP to Win 7 & office 2003 to office 2010.

D_Marcel -- I'm not sure about the configuration question you posed. Is there a way to verify that?

[Dave]

Not sure if this will help but maybe trial just using copyfile. HTH. Dave

Dim FSO As Object
ActiveWorkbook.Save
Set FSO = CreateObject("Scripting.FileSystemObject")
'source,destination,save (Adjust destination path to suit)
FSO.CopyFile ActiveWorkbook.FullName, "c:\test\test.xls", True
Set FSO = Nothing

[Paul_Hossler]

Sorry, other than trying

ActiveWorkbook.SaveCopyAs Environ("userprofile") & "\Documents\My Workbook\My_Workbook.xls"

to sidestep the Win7 folder re-direction, I'm out of ideas.

The above works for me with Win7 and 2010

Paul

[psctornado]

So after a bit of digging on the internet...it looks to be not so much an issue with coding at all, but rather with permissions. To aleviate this issue the PC permsissions must be set to admin. Unfortunately, for me I can't set this as this is occuring on a work computer. I wish there was a way around this, but I dont' see that being possible...

I appreciate all the help guys!

[Paul_Hossler]

The 'My Documents' is a junction to 'Documents' and is there for backwards compatibility. It's Owner is SYSTEM which is why you can't access it.

However, 'Documents' is a folder and you should have permissions to it

Again, I didn't have any problems with SaveCopyAs when I put it into 'Documents', both on the locked-down work PC and the home PC

Paul

[psctornado]

Hey Paul,

This has been driving me nuts! I completely understand where your coming from. I went through the entire VBA code and replaced all 'My Documents' to 'Documents', and that doesn't to seem to make any difference. I'm at a loss as to anything else I could do to remedy this.

[SamT]

Place a new folder on the C:\ drive. Name it "TestFolder." The path of that folder is "C:\TestFolder\"

Change the code to directly use that path, then test the code.

Let us know how that goes

[psctornado]

Hi All,

I think I may have figured out the issue. The xls file is launched via a bat file so as to not corrupt the 'master' file. When looking at the coding it utilizes 'My Documents' rather than 'Documents' Paul H. had mentioned to change above.

On a hunch, I updated the bat file to reference 'Documents' instead, and now when saving the xls file I no longer have the error that I noted above.

This case appears to be resolved!!