Run Time Error 75

[Tony Bender]

I built a VB application allowing personnel in our company access to
numerous Excel files on our share drive. To get a sense as to which
users were using which reports I added the following macro to this
application which logs the user name and the Excel workbook they
retrieved. This worked fine for years until we moved to Excel 2000.
Now the user gets the "Run Time Error 75 Path/File Access Error", upon
clicking 'end' they still get the report, but it is an annoyance I'd
like to correct.

I went to Microsoft's Knowledge base regarding 'Run Time Error 75',
and it said there is problem with VBA when the Name statement is
involved. However I did not understand the language of the
'work-around'.

I greatly appreciate any ideas on how I can resolve this.

Thank you....


Option Explicit
Private Sub Workbook_Open()

Dim LogDir As String
Dim LogFile As String
Dim myFileNum As Long
Dim testDir As String

'**This is the name and location of the log on the shared

LogDir = "\\phx1ns01\sales\Brand\Log Texts\"
LogFile = LogDir & "\log.txt"

testDir = ""
On Error Resume Next

testDir = Dir(LogDir, vbDirectory)
On Error GoTo 0

If testDir = "" Then
'not connected or spelling error!
Exit Sub
End If

myFileNum = FreeFile()

Open LogFile For Append As #myFileNum
Print #myFileNum, ThisWorkbook.FullName & vbTab _
& Application.UserName & vbTab & Now
Close #myFileNum

End Sub

 

[Township of East Hanover]

Sub LogInformation(LogMessage As String)
Const LogFileName As String = "\\phx1ns01\sales\Brand\Log Texts\log.txt"
Dim FileNum As Integer

FileNum = FreeFile ' Next file number
Open LogFileName For Append As #FileNum ' creates the file if it doesn't
exist
Print #FileNum, LogMessage ' Write information at the end of the text
file
Close #FileNum ' Close the file
End Sub

Then use the above like this:

Private Sub Workbook_Open()
LogInformation ThisWorkbook.Name & " opened by " & _
Application.UserName & " " & Format(Date, "yyyy-mm-dd hh:mm")
End Sub

The above code courtesy of www.exceltip.com

 

[Steve Garman]

You have a backslash on the end of LogDir and on the beginning of LogFile.

This results in path "\\phx1ns01\sales\Brand\Log Texts\\log.txt"

The second "\\" is probably giving your Open statement indigestion

 

[Tony Bender]

Thanks,

I tried your suggestion, verbatim, but got the following error message:

Compile error in hidden module: this workbook

Do I need to change the wording anywhere?

 

[Dave Peterson]

I think Steve solved your problem--but that final backslash wasn't there in the
original post <vbg>.

http://google.com/[email protected]
(one line in your browser)

 

[Tony Bender]

Steve,

Good eyes for spotting that, however after I changed it some users are
still getting the 'Run Time Error 75'

Thanks anyway though...

Tony

 

[Tony Bender]

Dave,

After deleting that one slash per Steve's suggestion, some (not all)
users are still getting the Run Time Error 75 message. All our users
have identical PC configuration and all have Excel 2000. What would
cause the inconsistancy?

Thanks,

Tony

 

[Dave Peterson]

My first guess is that you have some users that don't have permissions to make
changes to that file/folder.

I'm not on a network, but if I made my .log file readonly (via windows
explorer), then I got that same error.

If the user doesn't have permission to update that file, it'll react the same
way.

You might want to just try opening that file (on a troublesome pc) in Notepad,
change something and see if they can save it.

 

[Dave Peterson]

And no one is editting that file (and locking out others)?????

 

[Tony Bender]

Dave,

Is there any way to turn off the Run Time Error Message? Like
DiplayAlerts = False, or something like that?

 

[Chip Pearson]

Tony,

You can use On Error logic to prevent errors from interrupting
code execution. The simplest way is to use "On Error Resume
Next". Note that this does *not* fix the error, but rather
ignores it (and any side effects it may have) and causes code to
continue to the next statement. Look up "On Error" in help for
more details.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Tony Bender]

Thank you,

I got it to work...

Thanks

Tony