Run Time Error 9 Subscript out of range

[gg]

Here is another one. I Have resolved a similar one with the help of this thread.

http://www.mrexcel.com/forum/showthread.php?t=326781

However, I believe this to be a different issue. Again the problem does not occur on all machines. However on everyone except mine this error occurs.

The error highlights the BOLD area of code. Which is the last line of the VB.

Any suggestions? I think it has to be a simple Windows or Excel setting..

Public Sub SKPIUPDATE_ALL_NAMC()
Dim QPR
Dim lnk
Dim frm
Dim start
Dim fin
Dim drp1
Dim drp2
Dim drp3
Dim src1
Dim NAMC As Integer
' This macro will automatically open and download the TMMK-VEH daily scrap
'and store the file in the same directory

Set QPR = CreateObject("InternetExplorer.application")

    QPR.Visible = True
    
    QPR.navigate "https://www.portal.toyotasupplier.com/wps/myportal/"
    
    Do While QPR.Busy: DoEvents: Loop
    Do While QPR.readyState <> 4: DoEvents: Loop
    
    With QPR.document.forms("Login")
        .User.Value = "******"
        .Password.Value = "******"
        .submit
    End With

    Application.Wait Now + TimeSerial(0, 0, 11)

    QPR.navigate ("https://www.portal.toyotasupplier.com/skpi/")
    
    Application.Wait Now + TimeSerial(0, 1, 90)
    
    If myNAMC = "TMMK-VEH" Then
        NAMC = 4
    ElseIf myNAMC = "TMMK-PWT" Then
        NAMC = 3
    ElseIf myNAMC = "TMMC" Then
        NAMC = 5
    ElseIf myNAMC = "TMMTX" Then
        NAMC = 6
    ElseIf myNAMC = "TABC" Then
        NAMC = 7
    ElseIf myNAMC = "NUMMI" Then
        NAMC = 8
    ElseIf myNAMC = "TMMI" Then
        NAMC = 9
    ElseIf myNAMC = "TMMTX" Then
        NAMC = 6
    ElseIf myNAMC = "TMMBC" Then
        NAMC = 10
    ElseIf myNAMC = "TMMAL" Then
        NAMC = 11
    ElseIf myNAMC = "TMMNK" Then
        NAMC = 12
    End If
        
    Set lnk = QPR.document.Links(3) ' 3=TMMK-VEH,4=TMMK-PWT,5=TMMC,6=TMMTX,7=TABC,8=NUMMI,9=TMMI,10=TMMBC,11=TMMAL,12=TMMNK
    
    Do While QPR.Busy: DoEvents: Loop
    Do While QPR.readyState <> 4: DoEvents: Loop
    
    lnk.Click
    
    Do While QPR.Busy: DoEvents: Loop
    Do While QPR.readyState <> 4: DoEvents: Loop

    QPR.navigate ("https://www.portal.toyotasupplier.com/skpi/SkpiGatewayServlet?jadeAction=NCPARTS_SEARCH")
    
    Do While QPR.Busy: DoEvents: Loop
    Do While QPR.readyState <> 4: DoEvents: Loop
  
    Set frm = QPR.document.forms("form1")
    Set dwn = QPR.document.forms("page")
        
    Set start = frm.all("SKPI_SEARCH_START_DATE_KEY")
    start.Value = "01/01/" & Year(Now)
    
    Set finish = frm.all("SKPI_SEARCH_END_DATE_KEY")
    finish.Value = Format(Now - 1, "mm/dd/yyyy")
    
    Set drp2 = frm.all("SKPI_SEARCH_NC_TYPE_KEY")
    drp2.Item(1).Selected = True
    
    Set drp3 = frm.all("SKPI_SEARCH_NAMC_KEY")
    drp3.Item(p1).Selected = True
    
    Set src1 = frm.all("Submit")
    
    src1.Click
    
    Do While QPR.Busy: DoEvents: Loop
    Do While QPR.readyState <> 4: DoEvents: Loop
    
        
    QPR.navigate ("https://www.portal.toyotasupplier.com/skpi/DownloadNCPartListServlet")
    
    Application.Wait Now + TimeSerial(0, 1, 0)
    
    'Windows("DownloadNCPartListServlet").Activate
        
    Application.DisplayAlerts = False
          
    ActiveWorkbook.SaveAs Filename:= _
        "P:\OE Group\SPR\2008 DENSO SPR\SKPI 2008\all_namc_SKPI_download\all_namc_skpi_download.xls" _
    
    QPR.navigate ("https://www.portal.toyotasupplier.com/public/pr_logout.htm")

    Windows("SKPI 2008.xls").WindowState = xlMaximized
End Sub

 

[Richard Schollar]

Open Windows Exploerer on the machine where this is not working and go Tools>Folder Options>View tab and in the Advanced Settings list uncheck "Hide extensions for known file types".

 

[gg]

Open Windows Exploerer on the machine where this is not working and go Tools>Folder Options>View tab and in the Advanced Settings list uncheck "Hide extensions for known file types".

That was part of the fix to the previous problem. These machines are already unchecked.

 

[Richard Schollar]

Can you execute the command in the Immediate Window of the VBE on any of these machines?

 

[gg]

Can you execute the command in the Immediate Window of the VBE on any of these machines?

I have it working no problem on one machine.

 

[mvptomlinson]

The 'Error 9 Subscript out of range' error typically means the sheet or workbook doesn't exist. make sure on the other PC's that aren't working you have rights to the file/folder, and that the path/name are spelled perfectly.

 

[Richard Schollar]

I have it working no problem on one machine.

I actually meant on one of the ones where it doesn't currently work within the sub.

Following on from mvptomlinson's advice, you do actually have a Windo named "SKPI 2008.xls" during macro execution? If you have two wondows open on the same workbook, this may not be the case (as they get appended with the window number like "SKPI 2008.xls:1").

 

[gg]

Guy's thanks for the support on this one.

The VB is actually embeded in the file name SKPI 2008.xls and the path is the same for all the machines. As far as rights to the file? I assume we are have the same rights. This file is on a company shared directory and all the users access it daily and have not problems.

Because this file has the embeded VB do I even need this code? Do you think I can activate the worksheet that is required and be fine?