Runtime Error 75 Help

[Talat]

Hi,

I am working on a piece of code which checks to see if a particular directory name exists and if not create it, later saving an excel file into it. It works well first time, but when on subsequent runs the code tries to open and save another file in this same directory it returns error 75

I checked the creataed directory access rights and it shows that directory is Read Only. How do I set the ditrectroy access writes at the time of creation?

It seems that it does not even allow me to change it manually from the properties dialogue? Here is the code:

Sub ArchiveFile()


Dim strDN As String
Dim strFN As String

ChDir "G:\PUBS\PP-MS\INVOICES"

strDN = Range("E5")

MsgBox strDN

'If Len(Dir("c:\Instructions.doc")) = 0 Then
If Dir("G:\PUBS\PP-MS\INVOICES\" & strDN) = "" Then


MkDir ("G:\PUBS\PP-MS\INVOICES\" & strDN)
    Else
        'do nothing as the directory already exists
        'MsgBox "C:\Text Files\ directory already exists"
    End If

strFN = Range("K2") & ".xls"

MsgBox strFN

ChDir "G:\PUBS\PP-MS\INVOICES\" & strDN

ActiveWorkbook.SaveAs Filename:=strFN, _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False


End Sub

Will be grateful for any help on this.

Thanks,

Talât

_________________________
I have added cool emoticons to this message.
To see them go to http://x.exps.me?a8c82571f6e62857d81620e870f3f5cb

_________________________
I have added cool emoticons to this message.
To see them go to http://x.exps.me?a8c82571f6e62857d81620e870f3f5cb

 

[ogo]

Hi,
Please try below code and let me know if it works.

Sub ArchiveFile()
 
Dim strDN As String
Dim strFN As String
Dim x As String
 
On Error Resume Next
 
ChDir "G:\PUBS\PP-MS\INVOICES"
 
strDN = Range("E5")
MsgBox strDN
 
PathName = "G:\PUBS\PP-MS\INVOICES\" & strDN
 
x = GetAttr(PathName) And 0
If Err <> 0 Then
 
'*** code to create the directory***
    MkDir PathName
 
    strFN = Range("K2") & ".xls"
 
    MsgBox strFN
 
Else
        'do nothing as the directory already exists
        MsgBox "directory already exists"
 
End If
 
ChDir PathName
 
ActiveWorkbook.SaveAs Filename:=strFN, _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
 
End Sub

 

[Talat]

Thanks Ogo.

I will check and come back to you. I am off for couple of days, so probably on Wednesday.

Cheers.

Talât

 

[Talat]

Thanks Ogo. I have tried this now and its working perfectly well now.
I am very grateful for your help in this. Much appreciated.

Best wishes.

Talât