VBA Write to Logfile on Network - Only working for me?

[VBAtman]

Hey everyone, first post here.

I have a file that multiple users access on a shared network drive (sharepoint).

I have this macro setup to update a .log file with the username and date/time when users open the file. Here's the logmessage code:

Sub LogInfo(LogMessage As String)

'set path and name of the log file where you want to save
'the log file
Dim path As String
path = ThisWorkbook.path
Const LogFileName As String = "\Log\logfile.LOG"
Dim FileNum As Integer
Dim fullFilePath As String

fullFilePath = path & LogFileName
FileNum = FreeFile ' next file number
Open fullFilePath For Append As #FileNum ' creates the file if it doesn't exist
Write #FileNum, LogMessage ' write information at the end of the text file
Close #FileNum ' close the file

Exit Sub

Here is the call in the Sub Workbook_Open():Application.AskToUpdateLinks = False
LogInfo ThisWorkbook.Name & " opened by " & _
Application.userName & " " & Format(DateTime.Now, "yyyy-mm-dd hh:nn AM/PM")



This works correctly for me, and only me. Otherwise it errors out. Any suggestions? The location of the workbook and the log file is on a shared network drive that all users have access to.

Appreciate any feedback you can give!

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

The exact details of the error message might be useful. This may well provide some clues.

Unfortunately, I no longer have access to a Sharepoint system so I can't test anything. However, I would start by checking that everyone had suitable access permissions. Also check if ThisWorkbook.path returns the answer you were expecting when others use the workbook.

You will probably need to find a "tame user" who will log on for you so that you can debug the macro using their credentials while they watch.

 

[VBAtman]

Hi and welcome to the MrExcel Message Board.

The exact details of the error message might be useful. This may well provide some clues.

Unfortunately, I no longer have access to a Sharepoint system so I can't test anything. However, I would start by checking that everyone had suitable access permissions. Also check if ThisWorkbook.path returns the answer you were expecting when others use the workbook.

You will probably need to find a "tame user" who will log on for you so that you can debug the macro using their credentials while they watch.

Thanks for the reply!

I had someone run it and they are getting Runtime Error 52, "Bad file name or number". The user has the same access as I do.

 

[RickXL]

Was it the "open file" command where that happened?

What was the actual file name as created by Excel for that user? Something like the addition of a:

Debug.Print [I]fullFilePath[/I]

in the code just before the Open file command should display the name in the Debug Window.
You could use MsgBox if you prefer.

MsgBox [I]fullFilePath[/I]

 

[VBAtman]

Yes, appears to be happening on the Open File command.

I had the msgbox print out the path:

https://[network path]/Log/Logfile.LOG

It looks like it should be working

It appears to be working correctly up to that point. I'm stumped!

 

[RickXL]

I don't know. Is that supposed to work? I would not expect to be able to update a web page like that.

Does it work if you map Sharepoint as one of your drives? I know, permissions permitting, you can make Sharepoint look like another network drive. Does that work?

 

[VBAtman]

Oh it's a file located on a sharepoint drive. I do have it mapped to a drive and access it thru explorer view. When I run the macro on my machine, it works. It doesn't work for anyone else.

Maybe I'll check to see if they have it mapped similarly.

 

[RickXL]

That might be the difference.

Also, you can search for Sharepoint and find ways to map a drive in VBA. For example: https://social.msdn.microsoft.com/F...ent-library?forum=sharepointdevelopmentlegacy