CODEWITHSUNDEEP
vbaexcel
John Engelman
John Engelman

Reputation: 5

Export Excel Workbook in .xls Format

I have an Excel that gets updated with sales numbers daily. At the end of the week, when the Excel is complete, I export a PDF copy of the WEEKLY worksheet. Once I have a PDF copy, the sales numbers are transferred to another sheet within the workbook, emptying the WEEKLY worksheet.

In addition to this PDF copy of the WEEKLY worksheet, I'd like to export the entire workbook in a separate Excel file to the same location (.xls format is fine). I'd like to do this before emptying the WEEKLY worksheet. I've tried using a save as macro, but I want to remain in my original Excel - not the newly saved file.

For reference, here's the VBA code for my PDF export:

Sub SaveWeekly()
'
' SaveWeekly Macro
'

'
    Sheets("WEEKLY").Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "Z:\Excel New\Previous Excels\" & Range("A1") & " " & Range("H1") & ", " & Format(Date, "yyyy") & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

End Sub

Any help is appreciated.

Upvotes: 0

Views: 1842

Answers (1)

YasserKhalil
YasserKhalil

Reputation: 9538

Try this code

Sub Test()
Dim strDate As String
Dim strTime As String

strDate = Format(Date, "DD-MM-YYYY")
strTime = Format(Time, "hh.mm.ss")

Application.DisplayAlerts = False
    With ActiveWorkbook
        .SaveCopyAs fileName:=ThisWorkbook.Path & "\" & strDate & "_" & strTime & "_" & .Name
    End With
Application.DisplayAlerts = True
End Sub

Upvotes: 1

Related Questions