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?

 

[davesexcel]

My first thought is the other computer does not have permission to do what your computer did.

 

[kvsrinivasamurthy]

Excel version may be different.

 

[JessL]

Excel version may be different.

I have checked, the excel version is the same

 

[JessL]

I

My first thought is the other computer does not have permission to do what your computer did.

have checked on this too, I’m pretty sure I have access to the folder or path mentioned in the script

 

[kvsrinivasamurthy]

Can you post the code showing the line where the problem is.

 

[JessL]

Yes sure

 

[rlv01]

I wonder if this is an excel problem. You are using the shell command to call the window scripting host to run an external vbscript, presumably to launch SAP. That seems like it has the potential for all kinds of trouble if you shift to a different PC. Does the needed script file even exist on the other PC? If it does, is it the same as on the first PC? Is the SAP access the same on the other PC? The place to start might be to see if you can run the script manually outside of excel.

 

[JessL]

I wonder if this is an excel problem. You are using the shell command to call the window scripting host to run an external vbscript, presumably to launch SAP. That seems like it has the potential for all kinds of trouble if you shift to a different PC. Does the needed script file even exist on the other PC? If it does, is it the same as on the first PC? Is the SAP access the same on the other PC? The place to start might be to see if you can run the script manually outside of excel.

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

 

[rlv01]

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