I'm trying to create a program that can be used by other people. Currently, my files are in the directory C:\Documents and Settings\jpmccros\Desktop\test
This directory contains my macro.xlsm, names.bat, and another sub-directory called Data.
The batch file names.bat does the following:
cd data
dir/b/o:n > names.txt
This does exactly what I want it to do. When I open the batch file (which is in the directory C:\Documents and Settings\jpmccros\Desktop\test\, the MS DOS Command Prompts starts in C:\Documents and Settings\jpmccros\Desktop\test\ then runs my commands and makes my file names.txt, and puts it exactly where I want it.
When I open up macro.xlsm and run the macro1, it calls the batch file to open.
This is my macro command:
Dim names_txt, names_bat, full_name, filename, folder As String
Dim position As Integer
Dim pathcrnt As String
full_name = ThisWorkbook.FullName
filename = ThisWorkbook.Name
position = InStr(1, full_name, filename, 1)
position = position - 1
folder = Left(full_name, position)
names_bat = folder & "names.bat"
Shell names_bat, vbMaximizedFocus
Now here is my problem: The macro actually opens the batch file, or at least it opens the MS DOS Command Prompt. However, when it opens the batch file, the initial directory is:
C:\Documents and settings\jpmccros\My Documents
I need this batch file and macro to be dynamic, therefore I need the batch file to open up its displaying directory. What's going on with this? Is there a command I can write on my batch file? Is it something in VBA?
Your method of accessing the batch file using activeworkbook.path works. The VBA code finds it, relative to its current location, and opens it.
However, the issue I was having was once VBA opens the batch file, the command prompt starts in the directory C:\Documents and Settings\jpmccros\My Documents\ every time.
Your method does not bypass this issue. I did create a solution (and used your activeworkbook.path idea too). Instead of calling my batch file, I simply create one in VBA and print out a line cd var_activeworkbook.path & "\data". This way, I was able to have VBA search for the current directory and save it as a variable.
Check it out:
Dim pathcrnt As String, batch_file As Integer
pathcrnt = ActiveWorkbook.Path
batch_file = FreeFile()
Open pathcrnt & "names.bat" For Output As #batch_file
Print #batch_file, "cd " & pathcrnt & "\data"
Print #batch_file, "dir/b/o:n > names.txt"
Print #batch_file, "pause"
Close #batch_file
Shell pathcrnt & "names.bat", vbMaximizedFocus