vba excel create a file using environmental variable for file path

Question

I am stuck with vba excel macro. What I need is to have a macro that does the following two things:

creates an empty text file that will be stored either in user's %USERPROFILE%\filename.txt or %TMP%\filename.txt.
output the contents of the Shell() command to a file created in point 1 above. For example a DOS equivalent of "ipconfig > %TMP%\filename.txt", or similar DOS commands.

The %USERPROFILE% and %TMP% will contain spaces as the usernames are "First_NameLast_Name".

I've been searching some examples of this simple and trivial task, but I am unable to achieve what I want. I get runtime error 53 file not found. An example code that i've tried:

Sub CreateAfile()
    Dim MyPath As String
    MyPath = Environ$("TMP") & "\filename.txt"
    'MsgBox MyPath
    Call Shell("ipconfig > " & MyPath, vbHide)
End Sub

The MyPath variable above seem to generate the correct filename path, at least when used by the MsgBox. However it doesn't seem to work when called from the Shell().

Could someone help me to achieve my goals?

Thanks

Andy Wynn · Accepted Answer · 2015-12-15 02:34:43Z

1

First off, you should probably format that code if you want a response from this community. But...

You are on the right track. Environ("var") can be used with any of the windows environment variables. I use environ("userprofile") and environ("username") quite a lot in programs. Environ("userprofile") & "\textfile.txt" should work.
You don't need to call shell, you can just do Shell on its own. If you are gonna want a piped output, or to run a console command, then you need to do something like:

Shell "cmd.exe /k ipconfig >G:\text.txt", vbHide

Hope this helps!

answered Dec 15, 2015 at 2:34

Andy Wynn

1,3038 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrei Over a year ago

Sorry, first time use and didn't RTFM on posting at 2am. From my example above, the MyPath is correctly created to reflect the file path. However, I can't figure out how to use it from the Shell. In the second example that you've listed, it seem to work if I manually enter the path. However, for every user the path would be different and I need to use a variable to reflect this. Instead of useing the fixed path. How would I pass on the MyPath variable to the Shell command. Using the MyPath$ as i've read in several examples on google doesn't seem to work. Perhaps there is a more elegant way?

Andrei · Accepted Answer · 2015-12-15 19:25:38Z

1

Right, it seem my problem was not correctly diagnosed. There was a bit of a racing condition in my script. The file creation from the Shell didn't complete in time before the next line of code was excecuted. That line was reading the file that was supposed to have been created by the Shell command. Thus there was the error 53. After setting an app to wait a few seconds the file was successfully created before the file access by the next line. Thanks for all your help

answered Dec 15, 2015 at 19:25

Andrei

113 bronze badges

Collectives™ on Stack Overflow

vba excel create a file using environmental variable for file path

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related