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
tesla764Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
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

0
tesla764Author Commented:
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?
0
Ejgil HedegaardCommented:
You could try breaking the link before saving
ActiveWorkbook.BreakLink Name:= "Path and filename to DRA Tool.xlsm", Type:=xlLinkTypeExcelLinks
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

tesla764Author Commented:
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?
0
tesla764Author Commented:
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
0
NorieAnalyst Assistant Commented:
How about this?
Sub filesave()
Dim wb As Workbook
Dim PthAndName As String
Dim Links As Variant
Dim I As Long

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

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

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

    ActiveSheet.Copy
    Set wb = ActiveWorkbook

    With wb
        Links = .LinkSources(Type:=xlLinkTypeExcelLinks)
        For I = 1 To UBound(Links)
            .BreakLink Name:=Links(I), Type:=xlLinkTypeExcelLinks
        Next I

        .SaveAs PthAndName, FileFormat:=51, CreateBackup:=False
        .Close False
    End With

    '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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.