Search code examples
vbafilesystemobject

(VBA) Runtime Error 76 in connection with CreateTextFile on others' stations

Good evening,

I've been trying to fix this bug for a few hours now with no luck. It is especially frustrating since the bug doesn't express itself on my own work station, but rather on every body else's. The purpose of the code is create (FileSystemObject -> CreateTextFile) two .txt-files and write (TextStream) certain information into each of them. The following are the relevant lines of code:

Dim username, filename, filename_2, filepath, complete_filepath, complete_filepath_2 As String
Dim fso As New FileSystemObject
Dim fso_2 As New FileSystemObject
Dim txtStream, txtStream_2 As TextStream

'Gets username
username = CreateObject("WScript.NetWork").username

filename = "db_Redel"
filename_2 = "db_ship"

complete_filepath = "C:\Users\" & username & "\Documents\" & filename & ".txt"
complete_filepath_2 = "C:\Users\" & username & "\Documents\" & filename_2 & ".txt"

Set txtStream = fso.CreateTextFile(complete_filepath, True) 'RUNTIME ERROR 76
Set txtStream_2 = fso_2.CreateTextFile(complete_filepath_2, True)

I am certain the declared filepath exists on the machines this has been tested on. I have a suspicion that something is keeping FileSystemObject object from functioning properly, such as permissions, but I checked the Office security centres on the other work stations and they all had the same settings as me. The following are the activated reference:

  • Visual Basic For Application
  • Microsoft Excel 14.0 Object Library
  • OLE Automation
  • Microsoft Office 14.0 Object Library
  • Microsoft Scripting Runtime

These were also activated on all of the other machines.

What can I do? Just to be clear: The code works as intended on my own work station.

UPDATE: I made one of my friends try it, and it works just fine on his PC too.

Solution

  • Your code ran fine on my PC. However, you are making a common mistake while dimming your variables. The line:

    Dim username, filename, filename_2, filepath, complete_filepath, complete_filepath_2 As String

    Will actually only dim complete_filepath_2 as a string. The others become variant types.

    The same problem with line:

    Dim txtStream, txtStream_2 As TextStream

    Only txtStream_2 will Dim as Textstream

    You need to dim one variable per line:

    Dim username as String

    Dim filename as String

    Dim filename_2 as String

    Dim filepath as String

    Dim complete_filepath as String

    Dim complete_filepath_2 As String

    Only then will you get the variable types you're expecting