Auto save files daily in pdf that has pi values

[kipras_b]

Hello,

This is my first time here so forgive me if there is already a thread on this. Now I am just ok with excel I know this and that but not a pro on it so I need help.

Here is what I got:

I created a report that records PI values of motors and DPs on different equipment daily and updates them automatically. Now I have to open that file each day and save it as daily as a pdf. I was wondering if there is a code that I can make that Excel would save that file for that day for example today it would save in the folder as " 06282013" as a pdf with out me going in there and saving it myself. So by the end of the week I could just open my folder and find 5 pdf files saved in the daily folder. Also, can I seperate the save files such that it would save in different month folder for example it would save each day in June but when July comes it would start saving in July folder? If its possible could you guide me to do the right code, I'm assuming this has to be done in Visual Basic but might be wrong. Thanks for your help again!

 

[John_w]

I don't have Excel 2007+ to save as PDF, so can't give you the complete code. Record a macro of you saving the workbook as a PDF then use the file name produced by the following code instead of the hard-coded file name generated by the macro recorder.

Sub Save_Daily_PDF()

    Dim monthFolder As String
    Dim fileName As String
    
    monthFolder = "C:\Path\To\Main\Folder\" & Format(Date, "mmmm") & "\"         'CHANGE MAIN FOLDER PATH
    If Dir(monthFolder, vbDirectory) = "" Then MkDir monthFolder
    fileName = monthFolder & Format(Date, "mmddyyyy") & ".pdf"
    
    MsgBox fileName
    
End Sub

Change the main folder path where indicated.

 

[kipras_b]

Thank you it worked I added this to the code and it started saving in .pdf format

    ActiveWorkbook.ExportAsFixedFormat xlTypePDF, fileName, xlQualityStandard, , , , , True

Now do I have to run excel and then run the macro everytime I save this file or is it possible to modify the code that it would save this everyday without me running the code manually or even opening exel?

 

[kipras_b]

To be more precise I would like to know if it is possible to open a macro at specific time when the excel is not opened? Or does the excel have to be always opened for it to save?

 

[John_w]

Yes, it is possible. Create a VBScript (.vbs) file which opens the workbook and runs Save_Daily_PDF. Use Windows Task Scheduler to run this .vbs file when required. There should be examples of this technique if you search.

 

[kipras_b]

Got it thank you.