在VBA中调用批处理文件无法正常工作 [英] calling Batch file in VBA not working properly

问题描述

我想创建一个可以被其他人使用的程序。目前，我的文件目录 C：\\ Documents和Settings \\ jpmccros \\桌面\\测试

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

该目录包含了我的 macro.xlsm ， names.bat ，并呼吁另一个子目录数据。

This directory contains my macro.xlsm, names.bat, and another sub-directory called Data.

该批处理文件 names.bat 执行以下操作：

The batch file names.bat does the following:

cd data

dir/b/o:n > names.txt

这不正是我想要它做的。当我打开批处理文件（这是在目录 C：\\ Documents和Settings \\ jpmccros \\桌面\\测试\\ 中，MS DOS命令提示符开始在 C：\\ Documents和Settings \\ jpmccros \\桌面\\测试\\ 然后运行我的命令，使我的文件 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.

当我打开 macro.xlsm 并运行Macro1的，它会调用批处理文件来打开。

When I open up macro.xlsm and run the macro1, it calls the batch file to open.

这是我的宏命令：

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

现在这里是我的问题：宏实际上是打开批处理文件，或至少它会打开MS DOS命令提示符。然而，当它打开的批处理文件时，初始目录是：

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和Settings \\ jpmccros \\我的文档

我需要这个批处理文件和宏是动态的，所以我需要的批处理文件来打开它的显示目录。这是怎么回事这个？是否有一个命令，我可以在我的批处理文件写的？有什么事情在VBA？

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?

推荐答案

您使用 activeworkbook.path 工作访问该批处理文件的方法。在VBA code发现它，相对于当前位置，并打开它。

Your method of accessing the batch file using activeworkbook.path works. The VBA code finds it, relative to its current location, and opens it.

不过，我是有这个问题曾一度VBA打开批处理文件，命令提示符目录启动 C：\\ Documents和Settings \\ jpmccros \\我的文档\\ 每次。

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.

您方法不绕过这个问题。我没有创建一个解决方案（并用你的 activeworkbook.path 的想法太）。而不是调用我的批处理文件，我只是创建一个在VBA并打印出一行 CD var_activeworkbook.path＆安培; \\ DATA。通过这种方式，我能够有VBA搜寻当前目录下，并将其保存为一个变量。

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.

检查出来：

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

这篇关于在VBA中调用批处理文件无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！