Solved

Publish

Posted on 2016-07-27
19
59 Views
Last Modified: 2016-08-08
I had this question after viewing Fasten The Updating Process Watch 11.

Hi Experts,

i have one file --were macro create tables in 4 sheets ....i need that sheets to be saved as PDF files in this path "C:\Users\itjockey\Documents\Performance Watch" if this folder dosent exit then save files to Workbook location.

See attached Thanks
Funds-Watch-v10.xlsm
0
Comment
Question by:itjockey
  • 13
  • 6
19 Comments
 
LVL 8

Author Comment

by:itjockey
ID: 41732525
Forgot to mention if PDF file is already exist then over past...

Thanks
0
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 41733555
Try attached file.
Start button on Setting sheet.

The first creation of the 16 pdf files used more than 15 minutes, so I have changed the code to use manual calculation for dashboards and rolling returns also.
It only calculate the calculation sheet when data is transferred, before copy to result.
And calculate all when each procedure ends.
Then the pdf files are created in a few minutes.
Existing files in folder are replaced without warning.
It would be quite easy to add a feature where you could select the output folder.

To be able to catch all types on the Rolling sheets, the validation list are moved to the hidden sheet Lists, with the range name ValidationList.
Funds-Watch-v11.xlsm
0
 
LVL 8

Author Comment

by:itjockey
ID: 41734167
Few Questions..
It would be quite easy to add a feature where you could select the output folder.

1) As A Code writer what you suggest do we go for that feature or current scenario is ok? what logically it should  be ?

2) Some PDF have Extra pages ......it is purposely left blanks For  in future if i add more schemes  So there is  room for that data table...???

3) It possible to Add Fund type in each page Upper Left ...See attached & my contact information in each pages Lower Right of PDF file?Snap
And Above all 3 ...i am happy with current WB if above 3 takes too much coding & time.

Thank You
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 41734774
1) Another way is to let the program make a subfolder to the Funds-Watch folder, for each new day with the format "Pdf yyyy-mm-dd", so for today it will be Pdf 2016-07-29.
If folder exist, files in folder are replaced.
I have used that in attached version.
Try that, but if you prefer folder selection, just let me know.

2) It is not intended to have blank pages.
I used last used cell to set the print range, but Excel does not always clean up, and I did not check all pdf files, so I have changed to use the last cell in column A for dashboards and column B for rolling.

3) Fund type and contact information added.
Funds-Watch-v11.xlsm
0
 
LVL 8

Author Comment

by:itjockey
ID: 41734927
Sir i cant able to download file ......may be some issue with EE server ...i am chatting with EE Representative .....will revert you soon.

Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 41734969
One Favor please if you dont mind it ....Send file on my email as not only me others too facing same problem i.e. cant able  to download macro enable file.

Bug

Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 41734978
or do one thing change extension to .txt and post i will change extension to .xlsm and will check.

Thanks
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 41734988
0
 
LVL 8

Author Comment

by:itjockey
ID: 41734992
it not happening i guess bug is related to file size ...Bug
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 8

Author Comment

by:itjockey
ID: 41734996
EE support able to download but i cant ....they said sending me in email with file but still not got email ...
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 41735011
E-mail send.
0
 
LVL 8

Author Comment

by:itjockey
ID: 41735035
ok thanks got it ...my main email is itjockey@hotmail.com
0
 
LVL 8

Author Comment

by:itjockey
ID: 41735044
Sir one request -  need to stay with fix location or if  location is not their then save to workbook location ...as old files have no use for  me ...and it will be data overflow.

Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 41735057
This weekend will frame new series of workbook .....this will be big but it may also use for you ...if you trade in stock market in your country ...i mean OMX Exchange. So please be a part of it..

Thanks
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 41735058
In module M13_MakePdf, sub MakePdfFiles, change these lines in the beginning of the sub

   
strFolderForPdf = ThisWorkbook.Path & "\Pdf " & WorksheetFunction.Text(Now, "yyyy-mm-dd")
    If Dir(strFolderForPdf, vbDirectory) = "" Then
        MkDir strFolderForPdf
    End If

Open in new window


to

   
 If Dir("C:\Users\itjockey\Documents\Performance Watch", vbDirectory) <> "" Then
        strFolderForPdf = "C:\Users\itjockey\Documents\Performance Watch"
    Else
        strFolderForPdf = ThisWorkbook.Path
    End If

Open in new window

0
 
LVL 8

Author Comment

by:itjockey
ID: 41735066
ok noted,,

Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 41739596
Will You Take On This Question?i Greatly Respect Sir.Glowflow And I Never Invite Other Expert If Someone Is working On It....But It Seems Sir.GowFlow Is Busy Now Days.

Thanks
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 41739790
It looks as an expansion of a long series of questions gowflow has answered.
His question to you shows he knows what is happening when the subs run.
I don't, and it will take some time to find out.
And his question could mean you should ask a question for the change of BuySel first.

I respect gowflow too, so I will not do anything for the moment, expect set the monitor on for the question.
I nothing happens, perhaps look at it then.
0
 
LVL 8

Author Comment

by:itjockey
ID: 41746762
Please look into my question as it is being abandon since 8 days.....Thanks
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This is an Add-On procedure to be used in conjunction with the code provided in Reducing EE Email Clutter using Outlook (http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/A_3146-Outlook-Processing-EE-emails-on-Receive.…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now