I have been using the below code and it seems to work very well. However, It currently prompts me to over write the file that is currently there. (same file name)
Is there a way I can do this automatically in the save as step at the bottom?
Is there a way I can do this automatically in the save as step at the bottom?
Code:
ActiveWorkbook.SaveAs Filename:=_
"C:\Users\Owner\Documents\_DENSO QRE\SKPI PARTS RETURN\all_namc_skpi_download.xls"_
Code:
Public Sub SKPIUPDATE()
Dim QPR
Dim lnk
Dim frm
Dim start
Dim fin
Dim drp1
Dim drp2
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, 60)
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 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
ActiveWorkbook.SaveAs Filename:=_
"C:\Users\Owner\Documents\_DENSO QRE\SKPI PARTS RETURN\all_namc_skpi_download.xls"_
ActiveWorkbook.Close
QPR.navigate ("https://www.portal.toyotasupplier.com/public/pr_logout.htm")
Windows("SKPI 2008.xls").WindowState = xlMaximized
Last edited: