+ Reply to Thread
Results 1 to 34 of 34

Monthly data to PDF

  1. #1
    Registered User
    Join Date
    09-25-2021
    Location
    India
    MS-Off Ver
    2019
    Posts
    55

    Monthly data to PDF

    Hello

    Can someone please help me with this ?

    I found a VBA code online that converts the file to PDF. But the range has to be manually defined.

    How to define the range based on the month.(every new month)



    Thank you !

    Regards
    Attached Files Attached Files
    Last edited by Fulminare; 10-01-2021 at 05:43 AM.

  2. #2
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Pdf

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    09-25-2021
    Location
    India
    MS-Off Ver
    2019
    Posts
    55

    Re: Pdf

    Hi !

    Yes it's converting fine. But how to define it by new month ?

    (I apologise for not being clear in my previous post)

    Only The column CDE's data of every month should get converted along with the headers.

    i.e if it is October, all the entries in that month, in CDE columns, should get converted to PDF. Not the rest of the file. (The resulting PDF would only contain the entries of October)

    The headers are in the first row. So 1st row of CDE columns + all the entries in that particular month (again in the columns CDE), each month.


    I am sorry for causing additional trouble due to my poor wording in my first question.
    Last edited by Fulminare; 10-01-2021 at 12:58 AM.

  4. #4
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Pdf

    You want to save Columns C, D and E to PDF.
    In your example, Column E is empty. Do you mean Columns B, C and D?

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: Pdf

    Administrative Note:

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  6. #6
    Registered User
    Join Date
    09-25-2021
    Location
    India
    MS-Off Ver
    2019
    Posts
    55

    Re: Pdf

    Thank you for pointing it out. I must have accidentally typed "PDF" in the subject section, when it was meant for the "tags"

    I have changed the Subject in my original post as per your advice. I will reframe my question and post it once again.

    Sorry for the inconvenience.

    Regards
    Last edited by AliGW; 10-01-2021 at 05:51 AM. Reason: PLEASE don't quote unnecessarily!

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: Monthly data to PDF

    Please do NOT start a new thread - if you do, it will be closed as a duplicate.

    Just continue here.

  8. #8
    Registered User
    Join Date
    09-25-2021
    Location
    India
    MS-Off Ver
    2019
    Posts
    55

    Re: Monthly data to PDF

    Okay this is what I am trying to do.

    Monthly data of excel should get converted to PDF.

    The data should be taken only from columns : B C D.

    Row 1 contains the headers.

    All the entries in that month along with the headers should get converted. (the pdf should contain only that particular month's data. not the entire file.)

    So every month The pdf must contain 1st row headers and that month's data (again, of columns B C D)

    This should happen for all sheets other than the "Master Sheet". (in future if there are new sheets, even that data should get converted)

    A new file is attached for reference.

    I request the experts/gurus to kindly guide me.

    Regards.
    Attached Files Attached Files

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: Monthly data to PDF

    And what is your reply to post #2?

  10. #10
    Registered User
    Join Date
    09-25-2021
    Location
    India
    MS-Off Ver
    2019
    Posts
    55

    Re: Pdf

    Quote Originally Posted by jolivanes View Post
    You want to save Columns C, D and E to PDF.
    In your example, Column E is empty. Do you mean Columns B, C and D?
    Yes.

    I apologise. I have posted the question again explaining my requirement clearly.

  11. #11
    Registered User
    Join Date
    09-25-2021
    Location
    India
    MS-Off Ver
    2019
    Posts
    55

    Re: Monthly data to PDF

    Quote Originally Posted by AliGW View Post
    And what is your reply to post #2?
    I am confused. The reply for post #2 is given in the following post, #3.

  12. #12
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Monthly data to PDF

    It's a good thing that AliGW forced you to go over your whole problem again.
    Your first Post should have been like Post #8.
    The requests between the 2 posts are quite a bit different, so for the future, please take some time to be to the point and cover all problem areas you want to achieve.
    That way you will not waste helpers time.

    To come back to your problem, try this.

    Please Login or Register  to view this content.
    Re: "I am confused." Join the club. I think it is age related so get used to it!!!!!!!
    Last edited by jolivanes; 10-01-2021 at 11:40 AM.

  13. #13
    Registered User
    Join Date
    09-25-2021
    Location
    India
    MS-Off Ver
    2019
    Posts
    55

    Re: Monthly data to PDF

    Yes I know.

    I made a big mess.

    So that code creates PDFs for all the sheets other than the master sheet isn't it.

    the file I am using already has macros. So I inserted a new module and pasted your code.

    It appears to be running, but I am not able to figure out where the files are getting stored.

    I have changed the locations from "C:\E-Mail Downloads\" to "C:\Users\user\Downloads". Still no results.

  14. #14
    Registered User
    Join Date
    09-25-2021
    Location
    India
    MS-Off Ver
    2019
    Posts
    55

    Re: Monthly data to PDF

    wait I think it is working.

    I will post an update tomorrow.

    Thanks a lot for your help and patience

  15. #15
    Registered User
    Join Date
    09-25-2021
    Location
    India
    MS-Off Ver
    2019
    Posts
    55

    Re: Monthly data to PDF

    Hi jolivanes !

    Your code works.

    couple of things I realised after running it :

    1.

    This code runs at the end of every month. So the last month's data should be printed not the current month's (I know I asked for the current month's data. only after I ran the code It struck to me that there will be no new data for the current month.)
    I tried changing some values to -1. It's not working properly.


    2.

    In my actual file there are two sheets that need to be excluded. i.e "master sheet" + "another sheet"
    I gave a shot at adding the "another sheet" 's name by using "&" "," e.t.c.


    3.
    How to have a fixed name for the files ? (not the name of the sheets) Like "Fixed Name" "Last Month".PDF ( I was able to assign the last month to the name of the PDF by MonthName(Month(Now) - 1) & ".PDF"
    couldn't figure out how to get the "Fixed Name" though.

    (I am thinking of a method to identify the files when all are having the same name.)

    4 Lastly, how to specify the location for saving the files.



    Please note, I tried to solve at least a couple of them by searching the net. I didn't want to trouble you unnecessarily. Only after failing to modify the code I am approaching you/the forum once again.

    The answers, in addition to solving my problem also help me to understand and learn the code bit by bit.

    Regards
    Last edited by Fulminare; 10-02-2021 at 12:47 AM.

  16. #16
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Monthly data to PDF

    Change this
    Please Login or Register  to view this content.
    to this
    Please Login or Register  to view this content.
    Change this
    Please Login or Register  to view this content.
    to this for the previous month
    Please Login or Register  to view this content.
    If you use a fixed name, they will just be overwritten.
    Location is as it shows in the code in Post #2
    For Location, it is as shown previously.
    [code]"C:\Some_Folder\Some_SubFolder\Some_Other_SubFolder\" & "Some_Name_Here " & MonthName(Month(Now)) & ".PDF"

  17. #17
    Registered User
    Join Date
    09-25-2021
    Location
    India
    MS-Off Ver
    2019
    Posts
    55

    Re: Monthly data to PDF

    Please Login or Register  to view this content.

    I did as you suggested. It is creating a blank PDF with nothing but the headers.

    I understand that assigning a same name to all the files will result in they getting overwritten. So the sheets' name itself can be used. But how to save it to this folder : "C:\Users\user\Desktop\PDF"
    pasting that into the code is starting the name of the pdfs with "PDF" and they are getting saved on the desktop when they are supposed to be saved in intended folder named "PDF"

  18. #18
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Monthly data to PDF

    If the files go into a Folder named "PDF" (without double quotationmarks) on the Desktop:
    Please Login or Register  to view this content.
    If the files go onto the Desktop:
    Please Login or Register  to view this content.
    Re: It is creating a blank PDF with nothing but the headers.
    How is Column C formatted?
    Do you have September, properly spelled, in Column C?
    Attach a copy of the workbook, without changes, that does not save properly.
    Last edited by jolivanes; 10-02-2021 at 01:28 PM.

  19. #19
    Registered User
    Join Date
    09-25-2021
    Location
    India
    MS-Off Ver
    2019
    Posts
    55

    Re: Monthly data to PDF

    I don't understand. The data of the file that has been uploaded here is taken from the original file. only the names are different.

    It is working with the file I have uploaded here. i.e "TEST.xlsm" but not with the original file.

    Column C is formatted as "date"

    and yes all the spellings are correct as another program enters the data into the file (AutoHotkey)

    I will upload a copy of the file with the names changed. Rest of the data will remain the same.
    Attached Files Attached Files

  20. #20
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Monthly data to PDF

    That it does not work tells us nothing
    What does not work?

  21. #21
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Monthly data to PDF

    This works for me.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    09-25-2021
    Location
    India
    MS-Off Ver
    2019
    Posts
    55

    Re: Monthly data to PDF

    it's creating a blank pdf with nothing but the headers.

    could it be because my version of excel if different ? (2107)

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: Monthly data to PDF

    2107 is just a release number. In your profile you say you have Excel 2019 - is that still correct? Or do you have MS365?

  24. #24
    Registered User
    Join Date
    09-25-2021
    Location
    India
    MS-Off Ver
    2019
    Posts
    55

    Re: Monthly data to PDF

    The package containing the product key has the following printed

    Office Home & Student

    1Pc/Mac
    2019
    Last edited by Fulminare; 10-03-2021 at 05:05 AM.

  25. #25
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: Monthly data to PDF

    Thanks. Excel 2019 is correct, then. 2107 is just the realease of that product that you have installed - it is VERY unlikely to be relevant here.

  26. #26
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Monthly data to PDF

    If your data sheets that are to be saved in PDF format don't have September anywhere in Column C, you will end up with blank sheets.
    I also noticed that you changed code that was provided, tested and worked.
    All I can say from this end is that the code works on the example provided by you.

  27. #27
    Registered User
    Join Date
    09-25-2021
    Location
    India
    MS-Off Ver
    2019
    Posts
    55

    Re: Monthly data to PDF

    No C column has "September" in it.

    I was trying to change some values to see if anything worked. But it didn't

    This works for me.
    I tested the workbook you have attached in post #21

    even that is not working.(i.e blank pdf with headers)

    Is it working for you because of different excel version or something ?

  28. #28
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Monthly data to PDF

    If you read your Post #15 again where you will find this: So the last month's data should be printed not the current month's.
    We are now in October so last month's data would be for September.
    Now you say "No C column has "September" in it."
    Does it surprise you that you get empty files?
    Or am I missing something.

  29. #29
    Registered User
    Join Date
    09-25-2021
    Location
    India
    MS-Off Ver
    2019
    Posts
    55

    Re: Monthly data to PDF

    Are you saying that as there is no previous month i.e August, the PDF will be blank as there is no data of the previous month to print ?

  30. #30
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Monthly data to PDF

    The code does what you asked for.
    If you need it different, please explain.

  31. #31
    Registered User
    Join Date
    09-25-2021
    Location
    India
    MS-Off Ver
    2019
    Posts
    55

    Re: Monthly data to PDF

    Okay. This is what I wanted.

    The code should take the previous month's data of the columns BCD and convert it to pdf.

    If I run the code on 1st of October, All the entries in the month of September should be targeted.

    Even if there is October's data entered, the code should ignore that and only focus on the previous month, i.e September. (Same case if there is no October's data. Either way the month targeted should be September)

    (other parameters are same. they work well with the code you already gave. My only problem is with the month selection)

  32. #32
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Monthly data to PDF

    See attached.
    Notice that the three sheets, Barry, John and Leonard, have both data with September and October in Column C.
    PDF files will be saved in a folder named "Classes" on the desktop.
    If you don't have a folder with that name on your desktop, make it.
    Button to export to PDF is on "Master Sheet" sheet.

    In the attached, delete the code you don't want.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  33. #33
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Monthly data to PDF

    Since we don't seem to be able to agree on the month to be saved, have a look at the attached.
    Takes away all the possible wrong months to be saved.
    Select the month in the pop-up userform listbox.
    Attached Files Attached Files

  34. #34
    Registered User
    Join Date
    09-25-2021
    Location
    India
    MS-Off Ver
    2019
    Posts
    55

    Re: Monthly data to PDF

    Dear jolivanes.

    Your code is working perfectly in the files you have attached. However, when I copy past the code in my actual workbook, the same case is getting repeated, blank pdf.

    It is not right on my part to keep you engaged to this question any further.

    First I will go through my file and try to understand why the data is not getting printed. Then I will post an update here.

    I can't thank you enough for your patience and the trouble you have taken to help me out.

    I will mark this topic as "SOLVED", as the solution to the main problem has been given. There seems to be something amiss with my file which has got nothing to do with your code / solution.

    Thank you once again

    Regards.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] save files to specific folders and creates folders based on month
    By MKLAQ in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-21-2021, 05:11 PM
  2. macro to loop through all folders and sub folders of outlook and save to excel
    By excelhelpseeker in forum Outlook Formatting & Functions
    Replies: 0
    Last Post: 10-27-2016, 03:24 AM
  3. Save the open sheet in a path with a save as dialogue box and also create two folders
    By Biplab1985 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-19-2015, 05:19 PM
  4. [SOLVED] Code to create Folders and sub folders based on variable cell values
    By maxwell13 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-03-2015, 09:28 PM
  5. Create folders and save text files to these folders
    By bloomingcarrot in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-03-2014, 08:14 PM
  6. [SOLVED] Create folders and Sub folders and Sub-Sub folders from 5 columns with VBA
    By arleutwyler in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-11-2014, 04:16 PM
  7. Replies: 0
    Last Post: 12-04-2012, 01:01 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1