VBA Code to save new file without LINKS

[CharleneM]

Hello All. I am new to Excel Forum and VBA. I managed to create an Excel 2010 macro enabled user form and via VBA code saves the form as a normal Excel Worksheet (xlsx) with a new file name. My problem is that when the new file is opened it is still linked. I figured out how to manually break the link and disable macro notice but this is after the new file has been created, and would have to be done manually for every file saved. Is there a VBA code I can add that will do this before saving the new file.

This is the code I am using to save the new file:

[code]

Sub SaveInvWithNewName()
Dim NewFN As Variant
' Copy Invoice to a new workbook
ActiveSheet.Shapes.Range(Array("Bevel 2")).Delete
ActiveSheet.Shapes.Range(Array("Bevel 3")).Delete
ActiveSheet.Copy
NewFN = "C:\XXXXX\" & Range("I4").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NextInvoice
End Sub

[code]

[scott.s.fower]

try this...

Please Login or Register  to view this content.

ps you need a "/" before code in your close code tag

[CharleneM]

Thanks for you help Scott. I'm not quite there yet. When I open the new saved file, it says "Excel found unreadable content in file. Do you want to recover the contents of this workbook? If you trust the source of this workbook click yes". I clicked Yes and a repair message said "Removed Feature: Data validation from /xl/worksheets/sheet1.xml part".

I glad it removed the data validation, but is there a way to avoid the error messages?

[scott.s.fower]

I think it may hve to do with the save as part, maybe just save as .xlsx instead of " FileFormat:=xlOpenXMLWorkbook" (not sure what the syntax would be off the top of my head as im replying from my phone right now, i cant look it up efficiently) you could try "FileFormat:=*.xlsx"

[CharleneM]

Scott, it would be even better if I could just save the NEW FILE as a pdf. I don't need the xlsx if PDF is do-able, but I have read hundreds of threads and can't get it to work.

[scott.s.fower]

try

Please Login or Register  to view this content.

[CharleneM]

Thanks Scott. I'm getting an error message: Compile Error, Syntax Error

This section came back in red:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:= _
NewFN, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True _
End Sub

[scott.s.fower]

there shouldn't be an underscore after the True

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:= _
NewFN, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True _
End Sub

do you have a value in range I4 if not you don't have a name

[CharleneM]

If I take out the underscore I get the error message: Run-time error'-2147024773(8007007b)': Document not saved. Also, cell I4 represents the invoice number