Excel VBA > SaveAs >> over write current file

[ggodwin]

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

[Siddharth Rout]

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs (etc.)
Application.DisplayAlerts = True

Hope this helps...

[RobDog888]

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs (etc.)
Application.DisplayAlerts = True

Hope this helps...

That shouldnt work as turning off display alerts forces the default selection of the warning dialog for overwritting which is "No".

Why cant you use the .Save instead?

[Siddharth Rout]

Hi Rob I have tried it and it works...

when display alerts is off, it won't ask you. it will simply owerwrite the file...

[RobDog888]

Are you sure

http://msdn.microsoft.com/en-us/library/aa205032.aspx

When using the SaveAs method for workbooks to overwrite an existing file, the 'Overwrite' alert has a default of 'No', while the 'Yes' response is selected by Excel when the DisplayAlerts property is set equal to True.

[Siddharth Rout]

Are you sure

http://msdn.microsoft.com/en-us/library/aa205032.aspx

Hi Rob Can I sue the website

I just tried it again and it works First I tried it without the display alerts and it prompted me. Then I tried with the following code and it didn't prompt me... Try it out yourself...

Private Sub CommandButton1_Click()

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"P:\1-1\Book11.xls"

Application.DisplayAlerts = True


End Sub

[anhn]

When using the SaveAs method for workbooks to overwrite an existing file, the 'Overwrite' alert has a default of 'No', while the 'Yes' response is selected by Excel when the DisplayAlerts property is set equal to True.

Hi Bill, The word "True" should be read "False".

***
It may be OK to use Application.DisplayAlerts = False.
But what happens if the the file to be overwritten is being in used?
In that case, a run-time error message will be display because the file cannot be overwritten. This error need to be handled.

[ggodwin]

It worked great for me.

But as I understand it. This will only work for display's from Excel and not one from Windows.

Example:
I get a Windows dialog box when I download a file into excel from a Website that ask me if I want to open or save the file. I don't think this will work for that one.

Thanks for the help.

[Siddharth Rout]

I get a Windows dialog box when I download a file into excel from a Website that ask me if I want to open or save the file. I don't think this will work for that one.

no it won't work for that one

[thydzik]

thanks, works for me.

only had to trawl about 8 google pages to find it.

[RobDog888]

It worked great for me.

But as I understand it. This will only work for display's from Excel and not one from Windows.

Example:
I get a Windows dialog box when I download a file into excel from a Website that ask me if I want to open or save the file. I don't think this will work for that one.

Thanks for the help.

Probably not as macros are usually disabled by default with a browser/IE

[ggodwin]

I had forgotten about this thread...

But yes that command fixed the issue. On the Windows dialogue box. Once I checked the box it has never come back.