VBA SaveAs >> over write current file

[gg]

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?

ActiveWorkbook.SaveAs Filename:=_
"C:\Users\Owner\Documents\_DENSO QRE\SKPI PARTS RETURN\all_namc_skpi_download.xls"_

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

 

[Joe4]

It looks like you are working with Excel worksheets. Where does the Access part come in (note that you posted this in the Access forum)?
Are you running this code from Excel or Access?

 

[gg]

It is not Access. I must have posted in the wrong forum.

 

[Joe4]

I'll move it for you.

I think if you put the line

Application.DisplayAlerts=False

just before your save line, it should overwrite it without asking for confirmation.

Just set it to

Application.DisplayAlerts=True

at the end

 

[gg]

Joe,
Thanks for that little command.

What you have give me is a way to toggle (back and forth) between the display functions?

Tell me more about it. Does that command stop all the display alerts?

Like I get enother one in another application where I download an excel file from a website
and it ask me to open or save it?

 

[Joe4]

You know, I am not exactly sure what the whole array of messages it suppresses and which ones it doesn't.

I believe that it suppresses the Warning and Confirmation pop-ups (what I like to call the "Are you sure?" pop-ups). But it will not suppress and custom Message Boxes or Input Boxes you program.

You might need to test it out, but I would think it wouldn't suppress pop-ups from the Web.

Here is a little write-up on it, though it does not specify exactly which types of alerts it suppresses:
http://msdn.microsoft.com/en-us/library/aa175241(office.11).aspx

 

[Norie]

gg

It will only suppress alerts from the application the code is in. ie Excel