Run time error 70 - Permission Denied

[jbuist]

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!

'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

 

[jimrward]

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

 

[jbuist]

Thanks. I have full privileges so I don't think that's the problem.

 

[jimrward]

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

 

[xenou]

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.

 

[jbuist]

I am able to right click in the folder, create a new text file, enter characters, and save.

 

[jbuist]

@xenou
Do you have a recommendation to close the file? thx!

 

[jbuist]

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?

 

[xenou]

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.:

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.

 

[jbuist]

Thanks everyone! I added a line to close the txt file in the first sub. So far so good!

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]