Scripting.Filesystemobject > Textstream object > Disappears on close (1 Viewer)

[Isaac]

note - cross posted on stack overflow
I have some code that is giving me an unexpected result. And there IS something 'different' about the end-user this is happening to - she recently was a recipient of my company's modernization initiative, which involved being sent a new Microsoft Surface laptop (with win 10 of course) that supposedly is 'better compatible with Office 365' the way we are going to be using it.

Ok so here is what happens. I create a Scripting.Filesystemobject. I set an object variable (meant to be a Textstream), by fso.createtextfile. When stepping through the code one line at a time, I can see the file (filename.vbs) being created. (When the CreateTextFile line executes, I watch the folder - Bam, there it is in the folder). When the line executes to populate it with some text, that works fine. But as soon as the Textstream.CLOSE line operates--which, normally, is the point at which you have a saved, stable file in the folder--as that line executes, the file just disappears. Gone.

I wondered if it was a weird permissions, group policy thing. Because I was using %appdata% location. So I tested can she manually create a text file in that location? Yep. Can she manually create a VBS file, and run it, in that location? Yep.

I've tried both %appdata% and %documents%. It happens to both locations.

This code is in an Excel workbook beforeclose event, but I put this in the Access VBA thread because it's pure VBA really, and the problem has nothing to do with Excel, and the Excel forum gets less traffic.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'initiate full backup vbs script:
Dim ts As Object, fso As Object, strScriptText As String, strScriptPath As String
'populate our variable with the full text of the script: found on QLoader in this range:
strScriptText = ThisWorkbook.Worksheets("QLoader").Range("z_BackupScriptText").Value
'replace the text "placeholder" with this workbook's actual full path/name so the vbs can do a file copy backup:
strScriptText = Replace(strScriptText, "placeholder", ThisWorkbook.FullName)

'fire up FSO:
Set fso = CreateObject("scripting.filesystemobject")
'determine the new VBS file's path
strScriptPath = Environ("AppData") & "\Filename_" & Format(Now, "yymmddhhmmss") & ".vbs"
'create our textstream object:
Set ts = fso.createtextfile(strScriptPath) 'this works fine--once executes, I can see the file in the folder
'write our script into it
ts.write strScriptText 'this works fine
'save and close it
ts.Close 'when this line executes, the file DISAPPEARS*****

'GO:
Shell "wscript " & strScriptPath, vbNormalFocus 'and by this point, of course, the path/file is not found, because the file disappeared during the last line execute

End Sub

Again, note:
1) I've tried both %appdata% and %documents%
2) She can manually create text files in both locations - AND manually create AND execute vbs files, I've tested it live
3) It works for 2 other people who have been testing it, using various excel versions, but not on the newly deployed laptop from I.T.

I've seen group policies before that limited VBS files, but not like this. In those cases it was clear: You couldn't run them, wscript and cscript were disabled. Here, we can create them and run them manually, but Textstream.Close makes them disappear.
My money is on either anti-virus crap or GPO, but I could be wrong, does anyone have any insight or what might be going on?

 

[Isaac]

Well, well. On the few questions I post here, I seem to answer them all. LOL. However, in this case, I can claim personal credit for absolutely none of it. I found two solutions, one of which made me feel quite the idiot.

First solution: Since some corporate I.T. antivirus magic was 'removing' my .vbs file the second the Textstream object was out of scope, (but the same thing was NOT happening to .txt files), it was pointed out to me that I could simply leave it as Filename.Txt, and shell out wscript with the E: switch

Shell "wscript.exe /E:vbscript """ & strScriptPath & """ ", vbNormalFocus

...which tells wscript.exe to run a script, the script being the text file. I should have known this, as I knew you could tell wscript to run a vbs file; it never dawned on me that that could be used to run a file with ANY extension.

Second solution: Rendering my entire approach completely unnecessary (how to make a copy of a currently-open Workbook, etc), is the SaveAsCopy method. I'm not sure how, but in 15 years of VBA including Excel, I'd never heard of it. Crazy when you run across something totally fundamental that you just never had a need for before, and therefore it never came up. Major face palm!