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

[Charlie4]

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:

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

With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = ",+(?=" & vbNewLine & ")"
    txt = .Replace(txt, "")
End With

Can anyone help?

 

[daverunt]

Hi. Never used regex but don't you need to

Set xx=CreateObject("VBScript.RegExp")

With xx?

 

[Charlie4]

Hi. Never used regex but don't you need to

Set xx=CreateObject("VBScript.RegExp")

With xx?

Hmm. Just tried it - the

Set xx = CreateObject("VBScript.RegExp")

throws same error...

 

[NdNoviceHlp]

Hi Charlie4. I think daverunt meant something like this....

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

 

[Charlie4]

Hi Charlie4. I think daverunt meant something like this....

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.

 

[NdNoviceHlp]

You are welcome. Thanks from us Daves for posting your outcome. Dave

 

[jburbea]

I did everthing needed but still I get an err = 70
on
Set regex = CreateObject("VBScript.RegExp")
when
Dim regex As Object

I enabled the "Microsoft VBScript Regular Expression 5.5" in Tools -> References. prior to execution