• 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...

  • 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

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:
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 !
 
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:
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
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:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

... your code here ...

End Sub

Hope this helps
 
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:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

... your code here ...

End Sub

Hope this helps


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.
 
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.
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:
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
 
Hi,
Assuming I understood correctly, you want to save to PDF every time you make changes to the worksheet.

Yes, sorry But I need it to save on every change
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:
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

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
 
I actually need it to save to a pdf with every change and overwrite.
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
 
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
 

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 …
 
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...
 
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 …

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.
 
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:
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

Hi, So is there no way to automate/achieve the outcome I am after then? thanks
 
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:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

... your code here ...

End Sub

Hope this helps
Hi,
Is there a way to Automate and achieve my goal, save to pdf and overwrite on every single change?
 
Hi,
Is there a way to Automate and achieve my goal, save to pdf and overwrite on every single change?
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:
    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:
    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
 

Attachments

  • PERSONAL.XLSB
    14.3 KB · Views: 13
Last edited:
You are welcome... hopefully it will work as intended ;)

I have a few questions to your code
re - just replace MsgBox "It's working!", vbInformation
with my code
I tried using
Code:
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:
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:
I have a few questions to your code
re - just replace MsgBox "It's working!", vbInformation
with my code
I tried using
Code:
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.
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
 
...I also like to specify a range, such as

Code:
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 ::>((

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
 
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
 
Back
Top