Excel VBA > SaveAs >> over write current file

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?

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

---

Code:

---

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

---

Hope this helps...

Quote:

---

Originally Posted by koolsid

Code:

---

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?

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...

Are you sure :D

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

Quote:

---

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.

---

Quote:

---

Originally Posted by RobDog888

Are you sure :D

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

---

Hi Rob Can I sue the website :lol:

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...

Code:

---

Private Sub CommandButton1_Click()

Application.DisplayAlerts = False

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

Application.DisplayAlerts = True


End Sub

---

Quote:

---

Originally Posted by Bill Gates

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".:sick:

***
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.

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.

Quote:

---

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 :)

thanks, works for me.

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

Quote:

---

Originally Posted by 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.

---

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

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.:D