Using VBA to run BAT file but problem with folder name

[pfkelly]

Hi I am new to this forum

I have a batch file I am executing from excel vba. The batch file will not run if it is located in the following folder tree

I:\Hvel\Data\Testing(ADM001)\GasCylinderInventory\BatchFiles

If I remove the folder name (ADM001) then it works fine. I am unable to work out how to get this fixed.

Any help is really appreciated.

Thanks

Peter

[judgeh59]

without testing on my side....this might work....

[code]
FileName = "I:\Hvel\Data\Testing" & chr$(40) & "ADM001" & CHR$(40) & "\GasCylinderInventory\BatchFiles"
[/code

[pfkelly]

tried your suggestion and now "file not found" is displayed.

I copy and pasted exactly as you suggested.

[pfkelly]

here is the exact vba

Sub Run_StockOutBat()

Call Shell("I:\Hvel\Data\Testing" & Chr$(40) & "ADM001" & Chr$(40) & "\GasCylinderInventory\BatchFiles\StockOut.bat", vbReadOnly)

End Sub

[Leith Ross]

Hello pfkelly,

Batch files require using the old 8.3 DOS file names. You can get those from the newer long file names using the macro below.

Add a new module to your VBA project and paste the macro code into it.

Long Path Names to old 8.3 Format Names

Please Login or Register  to view this content.

Using the Macro

Please Login or Register  to view this content.

FolderName will hold the converted path that you can use.

[pfkelly]

Thanks for the reply,

I entered the code and found the GetShortPathName is resulting in a compile error which is telling me the sub or function is not defined.

[Leith Ross]

Hello pkelly,

Are you running 64 bit Office?

[pfkelly]

Hi Leith

I am running 64 bit windows, 32 bit office

[Leith Ross]

Hello pkelly,

I am running 64 bit Windows with 32 bit Office as well. Can you post a copy of your workbook? I would like to review the macro code.

[pfkelly]

Hi Leith

Here is my workbook.

Thanks

Peter

[Leith Ross]

Hello Peter,

I have installed the code below in the attached workbook. Try it and let me know the results.

Module1 Macro Code

Please Login or Register  to view this content.

[romperstomper]

Hi Leith,

You omitted the API declaration:

Please Login or Register  to view this content.