Macro runs on one computer and get run time error 5 on another

[JessL]

Hello all expertise,

Hope you are doing well!

I have a macro that able to run on my local machine but it doesnt work when i run it on the another machine (server).
It shows me the Run Time error 5.
When it does when I click on the button in excel is that it will access to SAP system, saves and downloads a report.
To test out what would be the issue, I tried to run the vbs that will access SAP to download the report and it works.

I have also checked the excel version in both machines and its the same. Tried to google the solution but couldnt find any. I am new to VBA and wondering if anyone knows whats going here?

 

[JessL]

Yes you are right. The external vbscript is to generate out report from SAP. I tried to run the external script separately, no issue. I’m able to access sap and pull out report.
Yes all the needed script file has been copied to second pc. I have copied over the entire folder so won’t be missed and they are the same.
I even checked the OS and excel version in both pc. They are the same too. I’m confused

Now I see the

Try this version to see if it provides any clues.

Sub dlTM00()
    Dim shtvbs As Worksheet
    Dim histreptpath As String
    Dim ShellStr As String

    On Error Resume Next
    Set shtvbs = ThisWorkbook.Worksheets("Utilisation Report")
    On Error GoTo 0

    If Not shtvbs Is Nothing Then
        With CreateObject("Scripting.FileSystemObject")
            histreptpath = Trim(shtvbs.Range("D1").Value)
            If .FileExists(histreptpath) Then
                ShellStr = "wscript " & histreptpath
                MsgBox "Inspect Shell command:" & vbCr & vbCr & """" & ShellStr & """"

                Shell ShellStr, vbNormalNoFocus       'set to normal focus for debugging
                Application.Wait (Now + TimeValue("00:00:15"))
            Else
                MsgBox "VBScript file not found"
            End If
        End With
    Else
        MsgBox "Worksheet not found"
    End If
End Sub

Okay! It says VBScript not found. But I do have the script in my second machine

 

[rlv01]

Now I see the

Okay! It says VBScript not found. But I do have the script in my second machine

It could be on your 2nd machine, but in a different location. You should compare the path stored in cell D1 (inshtvbs.Range("D1").Value) with where the script file is actually found when you search for it with Windows explorer.

 

[JessL]

It could be on your 2nd machine, but in a different location. You should compare the path stored in cell D1 (inshtvbs.Range("D1").Value) with where the script file is actually found when you search for it with Windows explorer.

First of all, thanks a lot for all your advices!
I have checked, It’s in the same location I’m sure.

 

[rlv01]

First of all, thanks a lot for all your advices!
I have checked, It’s in the same location I’m sure.

From my point of view, there is a fundamental inconsistency that you must resolve. The two contradictory facts are these:

1. You say that the script file is in the proper location and that the filepath in cell D1 contains that location.

2. The test code when you ran it, reported that "VBScript file not found":

Okay! It says VBScript not found.

Both 1 & 2 cannot be true at the same time.

 

[JessL]

Yes I know it’s contradict but it’s really the case here. Do you think it’s because the script not able to recognise the path location?

 

[rlv01]

Yes I know it’s contradict but it’s really the case here. Do you think it’s because the script not able to recognise the path location?

Unless you post data about what you are seeing on PC#1 vs PC#2 so that other people who are not you can compare them, I'm not sure this is worth pursuing. That data would be for both PC#1 & PC#2 and would consist of

1. Name of the VBScript file
2. The exact file path for the VBScript file as displayed in Windows Explorer.
3. The exact contents of cell D1 (inshtvbs.Range("D1").Value)