Writing text from Excel 2007 to a .txt file on a Sharepoint Site

Question

I have this VBA sub in an Excel 2007 project. It records user name, report name, date, and version on a .txt file in a Sharepoint site. Some of my users are getting a "Run-Time error'76' Path not found issue.

Here's my code:

Sub logReport(ReportName As String)

Call AppendTxt("//myaviall/teamsites/AviallReportingSolutions/Airplane_Usage_Log/Airplane_ACT.txt", UNameWindows & ";" & ReportName & ";" & Now & ";" & VersionNum)

Dim oFS, TS, FileObj

'Get text stream 'Set oFS = CreateObject("Scripting.FileSystemObject") 'Set FileObj = oFS.GetFile("//myaviall/teamsites/AviallReportingSolutions/Airplane_Usage_Log/Airplane_ACT.txt") 'Set TS = FileObj.OpenAsTextStream(8, -2) 'ForWriting, TristateUseDefault)

' Write to file

  'TS.WriteLine UNameWindows & ";" & ReportName & ";" & Now & ";" & VersionNum
  'TS.Close

  'Set TS = Nothing
  'Set FileObj = Nothing
  'Set oFS = Nothing

End Sub

Function AppendTxt(sFile As String, sText As String) On Error GoTo Err_Handler Dim FileNumber As Integer

FileNumber = FreeFile                   ' Get unused file number
Open sFile For Append As #FileNumber    ' Connect to the file
Print #FileNumber, sText                ' Append our string
Close #FileNumber                       ' Close the file

Exit_Err_Handler: Exit Function

Err_Handler: MsgBox "The following error has occured" & vbCrLf & vbCrLf & _ "Error Number: " & Err.Number & vbCrLf & _ "Error Source: AppendTxt" & vbCrLf & _ "Error Description: " & Err.Description, vbCritical, "An Error has Occured!" GoTo Exit_Err_Handler End Function

Rick Rick · Accepted Answer · 2012-12-14T10:27:47

If there is only one user that is getting the issue the first thing that I would look at it access, as Scott points out.

I run a very similar procedure which writes a line to a csv file from outlook based on the code here http://www.devhut.net/2011/06/06/vba-append-text-to-a-text-file/

This is probably not your answer but if the access is ok then it couldnt hurt to try another method.

Update

I also include this into my code to test if the file exists

exists = Dir$(sFile) <> fileName

Where fileName = "Airplane_ACT.txt" But I would maybe try this with a msgBox to see what it returns.

Also try try changing your string to Airplane_ACT_test.txt and run the code, this should create a new txt file, if this is the case then the issue may be related to your initial txt file.

Last thing: try with a different path eg: to the user's desktop.

Writing text from Excel 2007 to a .txt file on a Sharepoint Site

1 Answers