Run time error 70 - Permission Denied

jbuist

New Member
Joined
Mar 16, 2018
Messages
29
Hello.

The first part of my code works fine. I'm getting a "permission denied" error for the second part of the code starting at "Set stream". This code works fine if my filepath is the desktop. I have permission for the Q drive/folder. Any help is appreicated.
Thank you!

Code:
'THIS CODE CREATES A NEW TXT FILE
Sub createANewtextFile()
Dim sFilePath As String
Dim fileNumber As Integer

sFilePath = "Q:\xxxx\yyyyy\ExcelTextTEST.txt"
' Assign a unique file numner
fileNumber = FreeFile
Open sFilePath For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNumber]#fileNumber[/URL] 


End Sub
'THIS CODE WORKS TO COPY FROM EXCEL AND PASTE TEXT INTO A TXT FILE
Sub COPY_FROM_EXCEL_AND_PASTE_TEXT_INTO_A_TXT_FILE()
Range("A1").Select
   Dim FilePath As String
   Dim CellData As String
   Dim LastCol As Long
   Dim LastRow As Long
  
   Dim fso As FileSystemObject
   Set fso = New FileSystemObject
   Dim stream As TextStream
  
   LastCol = ActiveSheet.UsedRange.Columns.Count
   LastRow = ActiveSheet.UsedRange.Rows.Count
    
   ' Create a TextStream.
   Set stream = fso.OpenTextFile("Q:\xxxx\yyyyy\ExcelTextTEST.txt", ForWriting, True)
  
   CellData = ""
  
   For i = 1 To LastRow
      For j = 1 To LastCol
         CellData = (ActiveCell(i, j).Text)
         stream.WriteLine CellData
      Next j
   Next i
  
   stream.Close

End Sub
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Try manually going to that folder right click new txt file and see if you can enter few random characters and save file you might have only read only privs
 
Upvote 0
Can you verify what I suggested if you had full privs you wouldn’t be getting the message just in case the root shares have changed
 
Upvote 0
Your first sub opens the same file without closing it so that should be cleaned up (probably removed - looks like a piece of code that wasn't finished and is abandoned). Possibly the file exists but is locked for editing. I would also suggest you verify that you have full privileges since the error message says you don't.
 
Upvote 0
I am able to right click in the folder, create a new text file, enter characters, and save.
 
Upvote 0
If I were to change the path to the desktop, is there a way to write the code so if I'm not running the code it would use the other person's desktop location?
 
Upvote 0
To use a desktop folder based on the current user you can use the WScript shell host (among other techniques, I am sure). This is how I have done this over the years, e.g.:

Code:
Dim strDesktopFolderPath As String
strDesktopFolderPath = CreateObject("Wscript.Shell").SpecialFolders("Desktop")
Debug.Print strDesktopFolderPath

I don't have a recommendation for closing file handles that have not been properly close. In general this stuff will clear up on its own as the operating system realizes the file handles are no longer in use. If there were some clear problem that you can't otherwise resolve a reboot will do the trick.
 
Upvote 0
Thanks everyone! I added a line to close the txt file in the first sub. So far so good!

Code:
Open sFilePath For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNumber]#fileNumber[/URL] 

Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNumber]#fileNumber[/URL]
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top