Export strPath - Runtime Error 70 - Permission Denie

**EDR** · 10-11-2009, 06:45 PM

Hi Folks:

The larger snippet of code below works perfectly on a computer with Administrator rights only? However, when I try to run on any other machine - Vista or XP, I get "Runtime error 70, permission denied." I believe this is because it's trying to write a file to the C:/. I debugged, and the code gets caught up at:

Please Login or Register  to view this content.

Export to Strpath is related. As soon as it tries to write the first file to the C:/ drive it generates the error.

I have researched runtime error 70 at nauseam and can't find a work-a-round. I will not be able to change all the permissions settings on every computer this might try to be run on. Not all users will be able to log on as an Administrator. If there's a way to bypass this problem, or to save directly to the users desktop, I'm ok with that? I just need to avoid this error on non-Administrator log ins. Any ideas?

Thank you!

Please Login or Register  to view this content.

Please remember the above code works perfectly as is, only when someone is logged in as an Administrator. Weird.

Anyway to bypass write to drive permissions in Macro code?

THANK YOU!!!

**Leith Ross** · 10-11-2009, 07:17 PM

Hello EDR,

My guess is this is an issue with Office 2007 security. I don't have Office 2007 and it is soon to be replaced by Office 2010 in about month, so I never bothered with it.

Microsoft keeps changing the security levels and rules like socks. If you can put this on a network drive, you might be able to talk IT into given your program the necessary permissions for users to access and run the program. One method I used in Office 2003 was to write the macro in Outlook and sign the project. However, the signature was only a self-cert and not a third party digital signature like VeriSign. Self-Cert is free but only is trusted on the machine that created it. In this case, still not a viable option.

**EDR** · 10-11-2009, 07:39 PM

Thank you Leith! Much appreciated!

I'm still open to other ideas or possible work-arounds. Here's more data for consideration:

1. I just tried the .xlsm file on a laptop with Administrator log on and it *did not* work. So it works on *some* computers with Administrator log on only, not others? All are vista PC's with Macro security settings "enabled" in Excel 2007. It does not work on any machine that is not logged on as Administrator, but works on some that are, on site and offsite?

2. A network drive is one idea, but someone may use on their laptop offsite, so I need it to work on any machine with Excel 2007 or higher. The .xlsm file as a whole (prior to this addition of export strpath code) worked perfectly. As long as the user has Excel 2007 Developer Tab Macro Settings to "Enable all Macros" - and they all do - it used to work fine prior to this addition of code regardless of log in status.

3. Ideally writing or "creating" a PDF file would be superior to JPG, although JPG would work just fine. There's no way to attach three PDF or JPG files to Outlook directly from an Excel range without writing to the drive?

4. I'd really like to get this working. Waiting for Office 2010 is a crap shoot, and even if it did work in 2010, it would require everyone to upgrade, which is not ideal either.

Hmmm?

Thank you again, and if you or anyone else thinks of something, I'm all ears!

**JWM6** · 12-02-2009, 08:56 PM

I recently had an crash issue on Vista where I was getting a similar though different error because I was trying to create a file and Vista stated I did not have access. I did some research and found that if I right-clicked the icon for my program and select "Run As Administrator" this all worked correctly...

As a workaround I wrote a piece of code that detects Vista then tries to create a file at the very start of my program. With the ON ERROR clause setup, I won't crash but instead I put up a dialog box asking the user to run the app by right clicking on the icon and select "Run As Administrator".

Code as follows:

If (osVer = "Windows Vista") Then

' test write access
On Error GoTo errorWrite
Dim FileNum As Integer

OrigPath = ActiveWorkbook.path
fileNm = "writeAccess.tst"
FullFileName = OrigPath & "\" & fileNm

LogMessage = "test write marker..."

FileNum = FreeFile ' next file number
Open FullFileName For Append As #FileNum ' creates the file if it doesn't exist
Print #FileNum, LogMessage ' write information at the end of the text file
Close #FileNum ' close the file

GoTo noError

errorWrite:
result = MsgBox("**** Windows Vista has been detected ****." _
& vbCrLf & "Please relaunch this program." _
& vbCrLf & "Right-click on the program icon and select 'Run As Administrator'.", vbOKOnly)
' just QUIT - can't really do anything with Vista at this point
GoTo AppQuit
noError:
' remove the test write access file
Kill (FullFileName)
End If

Though it's a nicer workaround than crashing - is there anyway I can avoid this hack?