Can this VBA code stop prompting to confirm saving as xlsx each time?

HopefulGJL

Board Regular
Joined
Nov 28, 2008
Messages
100
Hello. The below code is creating a new workbook for me (.xlsx with no code) based on the name in cell A2 of Sheet1 (which changes based on the name in cell A1 of Sheet1). It also closes the new workbook, and keeps this .xslm workbook open exactly as I need it to do.

It next deletes all the external links in the new workbook to keep just the values, which is also what I need it to do.

However, it's prompting to confirm saving as .xlsx each time. I'm looking for it to just do that without asking. I'm purely guessing it might need a SaveChanges:=False somewhere, but I don't know where to put it. I've tried different places, and haven't been successful.

Thanks in advance for any information you can provide.


VBA Code:
Sub BreakLinksandSave()
  Dim link As Variant, wb As Workbook, origFile As String, newFile As String
 
  Set wb = Application.ActiveWorkbook
 
  If Not IsEmpty(wb.LinkSources(xlExcelLinks)) Then
    For Each link In wb.LinkSources(xlExcelLinks)
      wb.BreakLink link, xlLinkTypeExcelLinks
    Next link
  End If
 
  origFile = wb.FullName
 
  newFile = ActiveWorkbook.Sheets(1).Range("A2").Value
  ActiveWorkbook.SaveAs Filename:="C:\File Pathway\" & newFile, FileFormat:=xlOpenXMLWorkbook
  newFile = ActiveWorkbook.Name
 
  Application.DisplayAlerts = False
  Workbooks.Open origFile, UpdateLinks:=True
  Application.DisplayAlerts = True
 
  Workbooks(newFile).Close False
End Sub
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,157
Office Version
  1. 365
Platform
  1. Windows
Move this line
VBA Code:
Application.DisplayAlerts = False
to before you save the file.
 
Solution

HopefulGJL

Board Regular
Joined
Nov 28, 2008
Messages
100
Thank you! Here's the final code that, based on testing, seems to be working exactly as desired.

VBA Code:
Sub BreakLinksandSave()
  Dim link As Variant, wb As Workbook, origFile As String, newFile As String
 
  Set wb = Application.ActiveWorkbook
 
  If Not IsEmpty(wb.LinkSources(xlExcelLinks)) Then
    For Each link In wb.LinkSources(xlExcelLinks)
      wb.BreakLink link, xlLinkTypeExcelLinks
    Next link
  End If
 
  origFile = wb.FullName
  Application.DisplayAlerts = False
  newFile = ActiveWorkbook.Sheets(1).Range("A2").Value
  ActiveWorkbook.SaveAs Filename:=""C:\File Pathway\"" & newFile, FileFormat:=xlOpenXMLWorkbook
  newFile = ActiveWorkbook.Name
 
  Workbooks.Open origFile, UpdateLinks:=True
  Application.DisplayAlerts = True
 
  Workbooks(newFile).Close False
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,157
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,704
Messages
5,654,829
Members
418,155
Latest member
demasisi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top