Export data Automatically

[sahel]

Hello
I have an Excel sheet connected with controller, its not close (24 H running) and updated every some times with new data,
i need to export all data as PDF or XPS automatically every day at 12:00 AM without i close the original file or touch it.
so could any one can help me or send me full macros to be add with excel-vb

Thanx

 

[lrobbo314]

This saves it to the desktop. You can change where it saves to as one of the comments in the code states. See other comments as well.

Just run the 'Schedule' subroutine.

Sub Schedule()
Application.OnTime "12:00 AM", "ExportToPDF", "12:05 AM", True
End Sub


Sub ExportToPDF()
Dim Path As String
Dim FileName As String
Dim ws As Worksheet


Set ws = ActiveSheet 'Can change to any sheet in workbook
FileName = ActiveWorkbook.Name 'Can change to any name you want the file to be called
Path = Environ("UserProfile") & "\Desktop\" & FileName 'Can change the location you want the PDF to be saved to


ws.ExportAsFixedFormat xlTypePDF, Path, xlQualityStandard


End Sub

 

[sahel]

Hi lrobbo314

Thank you very much, i try this code its working fine
but still i have small problem that i need a PDF file name is Current date, i mean i no need to replace existing one.
Could you help me please
thanks

 

[lrobbo314]

Sub Schedule()
Application.OnTime "12:00 AM", "ExportToPDF", "12:05 AM", True
End Sub


Sub ExportToPDF()
Dim Path As String
Dim FileName As String
Dim ws As Worksheet


Set ws = ActiveSheet 'Can change to any sheet in workbook
FileName = Format(Now(),"MM.DD.YYYY")
Path = Environ("UserProfile") & "\Desktop\" & FileName 'Can change the location you want the PDF to be saved to


ws.ExportAsFixedFormat xlTypePDF, Path, xlQualityStandard


End Sub