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

JessL

New Member
Joined
Jun 3, 2022
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Jun 3, 2022
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,804
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Jun 3, 2022
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
May 16, 2017
Messages
1,804
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Jun 3, 2022
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
May 16, 2017
Messages
1,804
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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)
 

Forum statistics

Threads
1,176,084
Messages
5,901,293
Members
434,886
Latest member
qazibelal

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top