I was hoping someone could offer guidance on the following VBA issue. I have code which creates and saves a text file with specific text while pulling from various cells in an excel sheet, it works fine. Where I'm having problems is, multiple people need to be able to use it, and I'm fed up having to tweak the file path code for every person, I've tried various variable methods and none are working, I'm a very amateurish amateur with code but am learning. Could someone check below and offer guidance with the Environ string and Shells?
The FilePath string saves the file perfectly well on my (other users) desktop, however, I can't get the Shell code to work correctly when referencing the Environ("USERPROFILE") string. The first Shell line works very well, however the users PC must be identified for it to work, defeating the purpose of using the Environ string in the first place.
Can someone please offer advice as to how I could get this to work?
Sub SaveFileandOpen() Dim TextFile As Integer Dim FilePath As String FilePath = Environ("USERPROFILE") & "\Desktop\" & Range("D6").Value & " File Name " & Format(Now(), "dd mmm yy") & ".txt" TextFile = FreeFile Open FilePath For Output As TextFile Print #TextFile, "First line of text file here" & _ " second part of first line." Print #TextFile, "" Print #TextFile, "Another line here." Close TextFile Call OpenWebsite 'Shell "C:\WINDOWS\notepad.exe C:\Users\MyPC\Desktop\" & Range("D6").Value & " File Name " & Range("D27").Text & ".txt", vbNormalFocus Shell "C:\WINDOWS\notepad.exe Environ("USERPROFILE") & "\Desktop\" & Range("D6").Value & " File Name " & Range("D27").Text & ".txt", vbNormalFocus End Sub
Thank you in advance!