"Run-time error 70: Permission denied" error when I run this VBScript code...

Charlie4

New Member
Joined
Nov 22, 2017
Messages
9
Hi All

I've moved a big project onto new PC. I have the below sub to remove commas from a CSV file.
One line causes Run-time error 70: Permission denied. I haven't run this for a while so not sure if it's the move or an update that's broken it.

Sub:

VBA Code:
Public Sub FOffCommas(FileName As String)

' WHAT: Opens CSV file, removes spare commas from it, closes it.

Dim txt As String
Dim fso As Object

'1)  Opens text file/CSV in 'instance' of FileSystemObject
'       (a Microsoft thing).
Set fso = CreateObject("Scripting.FileSystemObject").opentextfile(FileName)

'2)  'Reads' all text from that file into string var 'txt'.
txt = fso.ReadAll

'3)  Looks through txt & removes commas
With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = ",+(?=" & vbNewLine & ")"
    txt = .Replace(txt, "")
End With
'4)  Closes text file/CSV instance
fso.Close

'5)  Opens CSV file (again, differently somehow) as file #1
Open FileName For Output As #1

'6)  Prints (commas removed) txt to it
    Print #1, txt
    
'7)  Closes it
Close #1

End Sub

Offending line (that causes error 70):
VBA Code:
With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = ",+(?=" & vbNewLine & ")"
    txt = .Replace(txt, "")
End With

Can anyone help?
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,871
Office Version
  1. 2013
Platform
  1. Windows
Hi. Never used regex but don't you need to

Set xx=CreateObject("VBScript.RegExp")

With xx?
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,676
Hi Charlie4. I think daverunt meant something like this....
Code:
Dim RegEx As Object
Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
    .Pattern = ",+(?=" & vbNewLine & ")"
    .Global = True
    txt = .Replace(txt, "")
Set RegEx = Nothing
End With
HTH. Dave
 
Solution

Charlie4

New Member
Joined
Nov 22, 2017
Messages
9
Hi Charlie4. I think daverunt meant something like this....
Code:
Dim RegEx As Object
Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
    .Pattern = ",+(?=" & vbNewLine & ")"
    .Global = True
    txt = .Replace(txt, "")
Set RegEx = Nothing
End With
HTH. Dave
That worked. Brilliant. Cheers, Chaps.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,183
Messages
5,576,574
Members
412,734
Latest member
rubinrods
Top