Open This Workbook.Path

[maclachlan19]

I'm having problems understanding how to designate a specific file path on this line of code.
This works as long as the file is one folder before \old reports..... As soon as the file is moved the code no longer works.
I would like to be able to hardcode a folder such as D:\DealerShare\Accounting\ELR Analysis.log, but this doesn't seem to work.

Open ThisWorkbook.Path & "\old reports\Log Files\ELR Analysis.log" For Append As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]

the full code is as follows in case this helps

 Private Sub Workbook_Open()On Error GoTo errhandler:
Open ThisWorkbook.Path & "\old reports\Log Files\ELR Analysis.log" For Append As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , Application.UserName, Now
Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
Exit Sub
errhandler:
MsgBox "Log file not saved"


End Sub

 

[mrshl9898]

This is working, is it what you meant?

Open "C:\Users\andrew.marshall\Desktop\logfile.txt" For Append As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]

 

[Michael M]

Try

Private Sub Workbook_Open()
On Error GoTo errhandler:
Open "D:\DealerShare\Accounting\ELR Analysis.log" For Append As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]  
Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]  , Application.UserName, Now
Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]  
Exit Sub
errhandler:
MsgBox "Log file not saved"
End Sub

 

[Kenneth Hobson]

If the drive or path or filename does not exist, it won't "work". A hard coded drive:\path\filename.ext has the same limitation as the dynamic path that you set. You can use Dir() to determine if the folder exists at least.

Of course you could always create the folders. Again, if the drive does not exist, that won't work either.

Some people do hard paths but the drive changes. e.g. D drive is USB drive 1. It has the path. On USB drive 2, when I put it in after drive 1, it becomes drive "E". So, if I use Dir() I can at least check for the path in drive D or drive E. When using the Dir() for folders, use the 2nd parameter. e.g.

Sub Test()
    MsgBox Dir(ThisWorkbook.Path & "\old reports\Log Files\", vbDirectory) <> "", , _
        ThisWorkbook.Path & "\old reports\Log Files\ELR Analysis.log"
    MsgBox Dir("D:\DealerShare\Accounting\", vbDirectory) <> "", , _
        "D:\DealerShare\Accounting\"
    MsgBox Dir(ThisWorkbook.Path & "\", vbDirectory) <> "", , ThisWorkbook.Path & "\"
End Sub

 

[maclachlan19]

Thanks Everyone, Michael your code worked well.

 

[maclachlan19]

One last question on this project (which is working amazing)
How can I add the filename to the text document. Currently it shows the username and date/time.

Private Sub Workbook_Open()On Error GoTo errhandler:
Open "D:\Accounting ST\Log Files\ELR Analysis.log" For Append As #1 
Print #1 , Application.UserName, Now
Close #1 
Exit Sub
errhandler:
MsgBox "Log file not saved"


End Sub

 

[maclachlan19]

Got this to work.

Worksheets("DETAIL").Protect Password:="ELR", AllowUsingPivotTables:=True