Create Text FIle and Open

  • Hello all,

    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?

    Thank you in advance!

  • Assuming that Range("D27") contains the today's date formatted as "dd mmm yy" then the code should probably be:

    Shell "C:\WINDOWS\notepad.exe " & Environ("USERPROFILE") & "\Desktop\" & Range("D6").Value & " File Name " & Range("D27").Text & ".txt", vbNormalFocus

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!