VBA calling a bat file not working on different workstation

[alexbuz]

Hi there,

I wrote a macro which is creating a bat file with values from excel cells and calling it as well. The excel and bat file are placed on a network server. Everything is working perfectly(writing the bat file, calling it, running the commands in cmd) when I run it from my workstation.
If I try to run it from a different network workstation it writes the bat file, the cmd window appears on screen for one second, but nothing happens after (I have to run separately the created bat file to get the final result). Why is happening this? Any ideas, please?
I am calling the bat file with shell function. I also tried to call it with Windows Script Host Run method, but the same result.

below are the last two lines (retVal keeps the bat file location):

Call Shell(retVal, 1)
End Sub

Any help would be much appreciated because is driving me crazy...

 

[Joe4]

Welcome to the Board!

Without seeing the code, it is hard to say. But it could be something like the code is using hard-coded drive mappings which are different on different workstations.

 

[alexbuz]

Hi Joe4,

The rest of the code is writing the bat file in a very specific way so I can run a vbscript with it and see below (the vbscript is in the same location as well):

Dim mybatFile As String, rng As Range, cellValue(11) As String, i As Integer, j As Integer, k As Integer, size As Integer
Dim myCall As String, excelAtt As String, fileIn As String, fileOut As String, retainValue As String
excelAtt = """"
'fileIn = " "
'fileOut = " "
myCall = "call publish.vbs"
mybatFile = deleted
Set rng = Selection
size = rng.Columns.Count
Open mybatFile For Output As #1
For i = 1 To rng.Rows.Count
For j = 1 To size
cellValue(j) = rng.Cells(i, j).Value
Next j
For k = size + 1 To 11
cellValue(k) = ""
Next k
If cellValue(5) = "CNEN" Or cellValue(5) = "" Then
retainValue = cellValue(1)
cellValue(1) = Worksheets(2).Cells(2, 2) + "" + retainValue + ".nwd"
retainValue = cellValue(2)
cellValue(2) = Worksheets(2).Cells(3, 2) + "" + retainValue + ".nwd"
Else
retainValue = cellValue(1)
cellValue(1) = Worksheets(2).Cells(4, 2) + "" + retainValue + ".nwd"
retainValue = cellValue(2)
cellValue(2) = Worksheets(2).Cells(5, 2) + "" + retainValue + ".nwd"
End If
Print #1 , myCall & " ";
For k = 1 To 11
If k < 11 Then
excelAtt = excelAtt + cellValue(k) + """" + " " + """"
Else
excelAtt = excelAtt + cellValue(k) + """"
End If
Next k
Print #1 , excelAtt
excelAtt = """"
Next i
Close #1
Call Shell(mybatFile, 1)
End Sub


sorry if the quality is poor, I've just started this weekend to write vba code..

 

[Joe4]

I think we need to see the "mybatFile" script too.

 

[alexbuz]

Ok. Selecting just a line in the excel file, inside the bat file will appear :

call publish.vbs "N:testarea\renamed\testmodel.nwd" "N:testarea\publishedtestmodel.nwd" "justatitle" "UF26" "CNEN" "notimportant" "company" "Unvalidated" "" "" ""

below is the code inside the vbscript called publish.vbs and basically is publishing some files with a set of new properties
option explicit
dim roamer
dim attrib
dim ndx
dim arg_in
dim arg_out
dim arg_title
dim arg_password
dim arg_author
dim arg_subject
dim arg_copyright
dim arg_keywords
dim arg_comments
dim arg_publisher
dim arg_publishedfor
dim flags
dim arg_expiry
dim expiry
dim count
count=WScript.Arguments.Count
arg_in=WScript.Arguments(0)
arg_out=WScript.Arguments(1)
arg_title=WScript.Arguments(2)
arg_password=WScript.Arguments(3)
arg_author=WScript.Arguments(4)
arg_subject=WScript.Arguments(5)
arg_copyright=WScript.Arguments(6)
arg_keywords=WScript.Arguments(7)
arg_comments=WScript.Arguments(8)
arg_publisher=WScript.Arguments(9)
arg_publishedfor=WScript.Arguments(10)
'arg_expiry=WScript.Arguments(7)
arg_expiry = dateadd("d", 21, Now())
expiry=CDate(arg_expiry)
'create roamer via automation
set roamer=createobject("navisWorks.document")
'open input file
roamer.openfile arg_in
'create publishing attribute
ndx=roamer.state.getenum("eObjectType_nwOaPublishAttribute")
set attrib=roamer.state.objectFactory(ndx)
'set publishing properties
attrib.title=arg_title
attrib.password=arg_password
attrib.author=arg_author
attrib.expirydate=expiry
attrib.subject=arg_subject
attrib.copyright=arg_copyright
attrib.keywords=arg_keywords
attrib.comments=arg_comments
attrib.publisher=arg_publisher
attrib.publishedfor=arg_publishedfor
flags=attrib.flags
ndx=roamer.state.getenum("ePublishFlag_DISPLAY_ON_OPEN")
flags=flags or ndx
attrib.flags=flags

'write output file
roamer.publishfile arg_out,attrib


and all this is running swimmingly from my workstation..

 

[Joe4]

I question this line:

[COLOR=#333333]call publish.vbs "[/COLOR][COLOR=#ff0000]N:testarea\renamed\testmodel.nwd[/COLOR][COLOR=#333333]" "[/COLOR][COLOR=#ff0000]N:testarea\publishedtestmodel.nwd[/COLOR][COLOR=#333333]" "justatitle" "UF26" "CNEN" "notimportant" "company" "Unvalidated" "" "" ""[/COLOR]

First off, it looks like a typo exists. It probably should be "N:\testarea\...", not "N:testarea\..."
Secondly, on the workstation it is not working on, do they have a N drive mapped, and is it going to the same place?
And do they have permissions to write to the folder and subfolders?

 

[alexbuz]

Yeah, sorry for that, I deleted by mistake the backslash.. It is writing "N:\testarea\..."

Yes, they have exactly the same configuration,rights as I do. When they run the macro, the bat file is written without problems in the N drive location.
Something strange is happening when the macro is calling the bat file...

 

[Joe4]

I can think of one other thing that you may want to check.

From the workstation that is working, go into the VB Editor, go to Tools -> References, and note which Libraries have been selected.
Now, do the same thing on the workstation where it is not working. Are the same Libraries selected? If not, select the missing ones and try again.

 

[alexbuz]

Yes..We have the same libraries selected..

It is so strange...and I haven't slept since Monday because of this )

 

[Joe4]

Unfortunately, these things can be hard to diagnose without having access to the actual environments (especially the one that it is not working in).
What I would probably recommend doing is to step through the code line-by-line on the system it is not working, and see if you can see what is going on.
That's all I really have left...