Karen Schaefer
asked on
Save As worksheet from active workbook as new xlxs in new folder removing the linked datasources
Looking for the correct syntax to saveas a workbook with out the linked files. what is the correct Type:=?????
This is what I have so far
This is what I have so far
ActiveSheet.ExportAsFixedFormat Type:=xlOpenXMLWorkbook, FileName:=FilePath3, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
ASKER
Roy I found the solution within my own code, thanks for your time.
Sub SaveFinalData()
Dim nm As Name
Dim ws As Worksheet
Dim nDate As String
Dim aCell As Range
Dim FilePath As String
Dim FilePath2 As String
Dim FilePath3 As String
Dim nMth As String
Dim curDate As String
Dim nName As String
Application.DisplayAlerts = True
nDate = Format(Date, "mmddyyyy")
nMth = Format(Range("InvoiceForm!D5"), "mmm yyyy")
nName = Sheets("MainForm").Range("B3").Value2 & "_" & Sheets("MainForm").Range("D3").Value2 & "_" & "Invoice"
FilePath = Sheets("MainForm").Range("G3").Value & "\" & nMth
FilePath2 = FilePath & "\" & nName & "_" & ReplaceAll(nMth, " ", "_") & ".xlsx"
With Application
.ScreenUpdating = False
On Error GoTo ErrCatcher
Sheets("InvoiceForm").Visible = True
Sheets("InvoiceForm").Activate
Sheets(Array("InvoiceForm")).Copy
On Error GoTo 0
If FileFolderExists(FilePath) = False Then
If _
MsgBox("File not found, do you wish to create a directory for" _
& " " & nMth & "?", vbYesNo, "Create New Directory") = _
vbYes Then
CreateNewDirectory (FilePath2)
End If
End If
UnlockSheets
For Each ws In ActiveWorkbook.Worksheets
'ws.Cells.Copy
Selection.Copy
ws.[A1].PasteSpecial Paste:=xlPasteValues
ws.Cells.Hyperlinks.Delete
Application.CutCopyMode = False
Cells(1, 1).Select
ws.Activate
Next ws
Cells(1, 1).Select
FilePath3 = FilePath & "\" & nName & "_" & ReplaceAll(nMth, " ", "_")
Sheets("InvoiceForm").Visible = True
Sheets("InvoiceForm").Activate
ActiveWorkbook.SaveCopyAs FilePath3 & ".xlsx"
ActiveWorkbook.Close SaveChanges:=False
.ScreenUpdating = True
End With
Exit Sub
LockSheets
Application.DisplayAlerts = False
ErrCatcher:
MsgBox "Specified sheets do not exist within this workbook"
End Sub
ASKER
Thanks Roy for your input found solution within my existing code.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for the clean up, this works great I was able to eliminate some other additional code to create the PDF by modifying your code
ActiveWorkbook.SaveCopyAs FilePath3 & ".xlsx"
ActiveWorkbook.SaveCopyAs FilePath3 & ".pdf"
ActiveWorkbook.SaveCopyAs FilePath3 & ".xlsx"
ActiveWorkbook.SaveCopyAs FilePath3 & ".pdf"
Pleased to help
Open in new window
Combine it with your code
Open in new window