0

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.

KHH
  • 124
  • 1
  • 4
  • 13
  • If you suspect the FSO may be the reason, have you tried creating the file using the "built-in" VBA file methods? – Tim Williams Jan 10 '13 at 18:37
  • Which built-in file methods do you have in mind? My knowledge of this isn't very deep. Appreciate it! – KHH Jan 10 '13 at 19:21
  • KHH - I mean something like http://stackoverflow.com/questions/3085615/how-can-i-write-to-a-text-file-reliably-from-excel-vba – Tim Williams Jan 10 '13 at 21:53
  • That looks very interesting. I just tested it, and it looks like I can use it for my purpose. Thanks. – KHH Jan 10 '13 at 23:38

3 Answers3

2

Turns out the issue was with the dateformat. The format on my own PCs was DD-MM-YYYY, while the format on the other work stations is DD/MM/YYYY. Forward-slash obviously cannot be in a filename, so the issue was fixed with:

DateNow = Date()
filename = Replace(DateNow, "/", "-")

:(

KHH
  • 124
  • 1
  • 4
  • 13
  • The `Replace` function works in this particular instance (but it causes the order od DDMM to be jumbled according to the user's settings), but a more robust approach would handle *any* potentially invalid character in *any* regional date formatting *and* keep the order of DD and MM consistent. A safer approach is to use `filename = Format$(DateNow,"YYYYMMDD") where YYYYMMDD is an ISO-8601 compliant date format. – ThunderFrame Dec 21 '16 at 03:32
0

From your input is unclear about actual values of username and Date. In case these have spaces - there may be a problem as you observe. Try to use fso.FolderExists("C:\Users\" & username & "\Documents\") method first and make sure it returns True.

Peter L.
  • 7,276
  • 5
  • 34
  • 53
  • Hello Peter. `Username` is always 6 characters long, 3 letters and 3 numbers: XXX### I removed the `Date` variable, but it had the format YYYY/MM/DD. The macro works on both my work station and my PC, so I cannot see how the problem can lie in the name of the file. – KHH Jan 10 '13 at 19:29
  • @KHH The only guess I have for the moment - FSO has NO rights to write into the target Folder. This may be true as soon as you use one of Win "special" folders - "C:\Users", which usually have special access rights. One more thing that deserves to be checked - `Microsoft Scripting Runtime` is indeed added to references on the workstations. – Peter L. Jan 10 '13 at 21:27
0

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

tbur
  • 16
  • Wow, thanks a lot. That could have caused me a lot of headaches in the future. Is that really the only way to declare variables? It just takes up so much space with in that format. – KHH Jan 10 '13 at 23:40
  • You **don't** need to dim one variable per line: `Dim username as String, filename as String, filename_2 as String, filepath as String, complete_filepath as String, complete_filepath_2 As String` is a perfectly valid, strongly-type declaration line. – ThunderFrame Dec 21 '16 at 03:25