I've been modifying some code which was put in place to create a text file which can then be copy pasted as required (a requirement for my job is to create reports on a regular basis, this speeds it up as all I need to do is change a few excel ranges). I want to roll it out to collegues however I want to keep the code locked down to prevent any mishaps. To do this, I need to use the Environ string to create a file on their desktop, however, once done, I would like the file to then be opened to aid copy pasting. Previously, when just myself was using it, I linked it to my desktop only, the file was created and opened perfectly, howeve,r now using the Environ("USERPROFILE") string, it won't open. Could anyone provide guidance on how to get it working?
Please see the code below.
The first Shell line (see end of code) worked fine when FilePath was set to my desktop. Now FilePath is set to Environ("USERPROFILE") it won't work. The second Shell line is shown for info, I have tried varying combinations using quotations and parenthesis around the whole string and small section but to no avail. While automatically opening the file is not necessary for what I need, it will help speed things up.
Sub Createfileandopen() 'This code will create a text file from which information can be copied and pasted as required Dim TextFile As Integer Dim FilePath As String 'Code below creates a text file on a users desktop. FilePath = Environ("USERPROFILE") & "\Desktop\" & "file name and type here.txt" 'Determine the next file number available for use by the FileOpen function TextFile = FreeFile 'Open the text file and generate the text Open FilePath For Output As TextFile 'The code below is used for creation of the text file, to add a line seperator, use the following 'code between each paragraph, Print #TextFile, "" Print #TextFile, "generic text here" & Range("A2").Value & " more text here." Print #TextFile, "" Print #TextFile, "some more text here blah blah" 'Save & Close the file Close TextFile 'The below code will then open the file and display it after creating, this can then be copy and pasted. Shell "C:\WINDOWS\notepad.exe C:\Users\USERPROFILE\Desktop\file name and type here.txt", vbNormalFocus 'Shell "C:\WINDOWS\notepad.exe Environ("USERPROFILE") & "\Desktop\" & "file name and type here.txt", vbNormalFocus End Sub
Any assistance is greatly appreciated.