Shell code not working with Environ - Is it possible?

  • Hello all,

    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.

    Any assistance is greatly appreciated.

  • Hello,

    To confirm you are using a string ... you can test using Environ$

    and to ensure you are using a proper method ... you can take a look at following link…lose-file-with-VBA-Shell/

    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

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