1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

help with autosave on any chages to worksheet

Discussion in 'VBA Macros' started by 2011gerry007, Jan 12, 2017.

  1. 2011gerry007

    2011gerry007 Member

    Messages:
    42
    I need help with the coding part of autosave to Pdf if any changes occurs on on a worksheet and also to overwrite the newly created existing pdf , I managed to find vba to save save pdf (via WWW-Google)
    as below

    Code (vb):
    Sub RDB_Workbook_To_PDF1()
    'Stop
       Dim filename As String
        '///////////////////////////////////////////////
           Dim PDF_date As String
                PDF_date = Format(Now, "dd-mmm-yy") & "_OV.pdf"
    '
    '
    Application.DisplayAlerts = False '' turn warning messages off.
       filename = RDB_Create_PDF(Source:=ActiveWorkbook, _
                                  FixedFilePathName:="P:\?????\Daily?????" & PDF_date, _
                                  OverwriteIfFileExist:=True, _
                                  OpenPDFAfterPublish:=False)
    '
       'For a fixed file name use this in the FixedFilePathName argument
       'FixedFilePathName:="C:\Users\Ron\Test\YourPdfFile.pdf"
    '
    '
       If filename <> "" Then
    '        'Ok, you find the PDF where you saved it
    '        'You can call the mail macro here if you want
       Else
            MsgBox "Not possible to create the PDF, possible reasons:" & vbNewLine & _
                  "Microsoft Add-in is not installed" & vbNewLine & _
                  "You Canceled the GetSaveAsFilename dialog" & vbNewLine & _
                  "The path to Save the file in arg 2 is not correct" & vbNewLine & _
                  "You didn't want to overwrite the existing PDF if it exist"
        End If
        Application.DisplayAlerts = True '' turn warning messages on.
    End Sub
    But can't find a way of executing it via on change or ????"

    This workbook is a newly created workbook/sheet each day and is therefore will have a different name each day it is downloaded,

    I have tried posting this same question in an other forum, but I have had no response as yet? So, I can only assume that is way to complex a question for the other Forum.
    url MrExcel.com
    http://www.mrexcel.com/forum/excel-...-worksheet-any-cell-changes-pdf-set-path.html...

    Any suggestion will be very much appreciated


    ▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

  2. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    Hi,

    Assuming I understood correctly, you want to save to PDF every time you make changes to the worksheet.
    That being the case, I would not recommend going the "on change" route as it would keep saving on every single change you made.

    I suggest you use the "Workbook_BeforeClose" event and place the code there to save to PDF (writing over the existing file if you so wish). This will execute the subroutine every time you hit close on the workbook, effectively saving every time you close (even if no changes were made to the worksheet).

    For that, simply place the code in the "ThisWorkbook" excel object using:
    Code (vb):
    Private Sub Workbook_BeforeClose(Cancel As Boolean)

    ... your code here ...

    End Sub
    Hope this helps
  3. 2011gerry007

    2011gerry007 Member

    Messages:
    42

    So Sorry, but I actually need it to save to a pdf with every change and overwrite. Thanks on advance and do you any suggestions for this.
  4. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    Hi,

    Ok... you can also do that but, just to be clear, it will save and overwrite at every change you do. For instance, if you change one cell's value, it will save, you change the next cell's value, it saves... and so on (I strongly advise against doing it this way as it may seriously break your workflow because it is constantly saving to PDF).

    In any case, code is very similar but placed in a different excel object.
    Right click in the sheet's name (the Tab at the bottom) and "View code"... there, place the following:
    Code (vb):
    Private Sub Worksheet_Change(ByVal Target As Range)

    ... your code here ...

    End Sub
    This works on a "per sheet" level so you would need to paste the code to every worksheet (if there is more than one) in the workbook.

    Hope this helps
  5. 2011gerry007

    2011gerry007 Member

    Messages:
    42
    Yes, sorry But I need it to save on every change
    Unfortunately. I am trying to automate the vba on a new Daily downloaded worksheet. So can.t tell users to paste every day...thanks for your suggestion though
  6. Marc L

    Marc L Excel Ninja

    Messages:
    3,027
    The only real change is when workbook is saved !

    Imagine if a pdf saving is achieved each time a cell is changed
    and at end when closing workbook the user do not save it :
    the pdf saving no reflects anymore the worksheet !

    So IMO the right event is Workbook_BeforeSave
  7. 2011gerry007

    2011gerry007 Member

    Messages:
    42
    Yep. I hear you. But i do need the worsheet to save on every change to a pdf. As i need other people supervisors to ne able to see the last recorded times in real-time via the saved Pdf
    And please also before you suggest.i dont wish to share the workbook
    ...also if the user closes without saving then it.ll still be backed up as a pdf with the latest data/times.

    Thanks though for your IMO...I hope thos makes more sense...many thanks
  8. Marc L

    Marc L Excel Ninja

    Messages:
    3,027

    So activating Macro recorder and saving worksheet as a pdf file :
    you'll have your own free code to insert in worksheet Change event …

    See also VBA inner help of ExportAsFixedFormat method …
  9. 2011gerry007

    2011gerry007 Member

    Messages:
    42
    Does anyone know the best way. To automate this vba. As i said in an earlier post i cant have my user s paste code every morning. Most are not pc savi. And just have difficulty finding and running a crystal report and extracting to csv format. And running a shortcut key on the keyboard. So i cant ask everyone workers to also go and find and paste vba. .
    ....so i need to automate this down as much as possible. After the users

    The report is ran and downloaded that days prisoners list each and every day is a different newly creatrd file.csv. then has a fields via vba where they can double click a range for time s in and out and so on....so i need it automated for the 300 or so users/ officer's that may be allocated this position on any given day on night. If its cutting and pasting they'll syart using a pen instead. Which is like stepping Backwards in today's modern age...
  10. 2011gerry007

    2011gerry007 Member

    Messages:
    42
    Thanks, for your suggestions, I did try recording a macro, but the "Daily_extracted_datasheet098230928348.csv" that I record a macro on will have a different number appended to it each time it is ran even if its on the same day, so the worksheet change event somehow doesn't work and doesn't run for me after recording and using a keyboard shortcut, Also I can't get the on double_click to work either to insert times into a specified range. So I must be doing something wrong and need a little more guidance on a step by step basis on how to do this.

    something like
    extract to csv
    rename to ????_todaysdate
    rename and save to network as ???_Todaysdate.xlsm/s
    get rid of unwanted columns in the csv
    manipulate data into ledgable columns
    bold
    borders
    print area
    set 3 0r 4 columns to double click ..insert current time
    save each time change to pdf or as above ExportAsFixedFormat
    save on workbook on close.xlsm
    etc etc.

    Sorry if I sound stupid, but I don't do vba or IT for a living ,I am just trying to make my fellow colleagues work/life that little bit easier and also attempt to get one more step away from users over-use of paper which as you know has no real value when it comes to historical record keeping and stats reporting :)
    Thanks in advance for this.
  11. 2011gerry007

    2011gerry007 Member

    Messages:
    42
    Hi, So is there no way to automate/achieve the outcome I am after then? thanks
  12. 2011gerry007

    2011gerry007 Member

    Messages:
    42
    Hi,
    Is there a way to Automate and achieve my goal, save to pdf and overwrite on every single change?
  13. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    Hi,

    There is a way, by using the "personal workbook" (Personal.xlsb)...
    You can find some info about this here:
    https://support.office.com/en-us/ar...Workbook-aa439b90-f836-4381-97f0-6e4c3f5ee566

    The way it works is by storing your macro in this "special" workbook... this way, all macros stored there will be available in every workbook you open.
    Easy enough for some code stored in a module... not so much for an event!

    So, for that, I created a personalized "Personal.xlsb" you can use to achieve your specific goal. Now all you have to do is:

    1) Navigate to "C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART" and paste the attached file there;

    2) Open the file and press ALT+F11 to enter VBA;

    3) In the "CAppEventHandler" class module, add your code by replacing:
    Code (vb):
        MsgBox "It's working!", vbInformation
     
    4) As a precaution, this was made to only work with sheets called "MySheet", so either change this to the worksheet name (if it is the same across all "Daily_extracted_datasheets" or if you plan to have the users change the sheet name to the one you specify here) or simply remove the condition below (which I strongly advise against) for the code to work with every worksheet of every workbook you open from now on:
    Code (vb):
        If Sh.Name <> "MySheet" Then
            Exit Sub
        End If
    Hope this helps
    If you have any further questions feel free to ask.
    Also, I advise you to take a look at the link below for more on "Events":
    http://www.cpearson.com/excel/Events.aspx

    Attached Files:

    Last edited: Jan 23, 2017
  14. 2011gerry007

    2011gerry007 Member

    Messages:
    42
    Your a champ, this has been bugging me for months. Will give it a go and thanks for the info links.
  15. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    You are welcome... hopefully it will work as intended ;)
  16. 2011gerry007

    2011gerry007 Member

    Messages:
    42
    I have a few questions to your code
    re - just replace MsgBox "It's working!", vbInformation
    with my code
    I tried using
    Code (vb):
    call RDB_Workbook_To_PDF
    but nothing happened, is there a way, or am I just not calling the sub properly, as i'd like to call 2X subs, the other would call another sub to save the workbook on change. I also like to specify a range, such as

    Code (vb):
    Private Sub Worksheet_BeforeDoubleClick_01(ByVal Target As Range, Cancel As Boolean)

    Dim MyRange As Range
    Dim IntersectRange As Range
    Dim EndRow As Long
    '
    '
    Application.ScreenUpdating = False
    '
    EndRow = Range("D" & Rows.Count).End(xlUp).Row
    Set MyRange = Range("C2:D" & EndRow)  'last row
    Set IntersectRange = Intersect(Target, MyRange)
    If it's not possible then no worries, you've been a great help ::>((
    Last edited: Jan 26, 2017
  17. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    Hi,

    Sorry for the delayed response... work :(
    It may have something to do with the way you are calling the subroutine... or where you have that subroutine stored.
    Is the RDB_Workbook_To_PDF routine also in the personal.xlsb file?
    Was the message box working when you tested?

    If so, can you upload the updated personal.xlsb file so I can take a look?

    Thanks
  18. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    I can't see why not... but since it is also a worksheet event, it may not be as simple as placing the code in the file.
    I would have to test it to be honest... working with application level events is a bit trickier as you may have noticed :)

    Please upload the file if and whenever you can, and I will gladly try to help

    Thanks
  19. 2011gerry007

    2011gerry007 Member

    Messages:
    42
    Thanks for your response, I am working 12hr shifts at the mo ,so no time to test or upload ,but I am really really greatful for your response, i will do a,s,a

    Thanks

Share This Page