asked on

Excel VBA file not saving as expected

I would expect the statement below…
“wb.SaveAs PthAndName, FileFormat:=51, CreateBackup:=False”
to create a file that would be in the “.xlsx” format that is NOT macro-enabled. But is still saves as Macro-enabled even though the file extension is “.xlsx”. This could be because there is a data Link reference that still exists when the file is saved.
Is there a way to save this file so that the links are broken?

Sub filesave()
PthAndName = ActiveWorkbook.Path & "\" & ActiveSheet.Name

MsgBox "The file willbe saved as: " & PthAndName & ".xlsx"

' vLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

'sBreakLinks

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

ActiveSheet.Copy
Set wb = ActiveWorkbook
wb.SaveAs PthAndName, FileFormat:=51, CreateBackup:=False
wb.Close False

Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
Set wb = Nothing

End Sub

Norie

What happens if you include the file extension in the filename?

PthAndName = ActiveWorkbook.Path & "\" & ActiveSheet.Name & ".xlsx"

MsgBox "The file willbe saved as: " & PthAndName

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

ActiveSheet.Copy
Set wb = ActiveWorkbook
wb.SaveAs PthAndName, FileFormat:=51, CreateBackup:=False
wb.Close False

Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
Set wb = Nothing

Open in new window

tesla764

ASKER

The file saves as .xlsx but macros are still enabled because there is a Data Link that references the workbook DRA Tool.xlsm, which creates the worksheet DRA Summary.xlsx. Is there a way to save the worksheet DRA Summary,xlsx so there is no data link to DRA Tool.xlsm.
When I open the workbook DRA Summary.xlsx I receive the message...
This workbook contains links to other data sources. I click "Don't Update" but the data link still remains.
Refering to the code I showed earlier, Is there a way to save the workbook so that the data links are broken?

Ejgil Hedegaard

You could try breaking the link before saving
ActiveWorkbook.BreakLink Name:= "Path and filename to DRA Tool.xlsm", Type:=xlLinkTypeExcelLinks

tesla764

ASKER

I get another error message. I have another idea which may work. After the .xlsx file is saved...
'Open the file back up
'break the link
'Close the file

Could you refresh me on the Opening and closing statements that wouldbe needed to accomplish this?

tesla764

ASKER

Here's the code block...

Sub filesave()
PthAndName = ActiveWorkbook.Path & "\" & ActiveSheet.Name & ".xlsx"

MsgBox "The file willbe saved as: " & PthAndName & ".xlsx"

' vLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

'sBreakLinks

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

ActiveSheet.Copy
Set wb = ActiveWorkbook
wb.SaveAs PthAndName, FileFormat:=51, CreateBackup:=False
wb.Close False

'Open the file back up
'break the link
'Close the file

Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
Set wb = Nothing

End Sub

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial