Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Publish

Posted on 2016-07-27
19
Medium Priority
?
81 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:Naresh Patel
  • 13
  • 6
19 Comments
 
LVL 8

Author Comment

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

Thanks
0
 
LVL 23

Accepted Solution

by:
Ejgil Hedegaard earned 2000 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:Naresh Patel
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 23

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:Naresh Patel
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:Naresh Patel
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:Naresh Patel
ID: 41734978
or do one thing change extension to .txt and post i will change extension to .xlsm and will check.

Thanks
0
 
LVL 23

Expert Comment

by:Ejgil Hedegaard
ID: 41734988
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 41734992
it not happening i guess bug is related to file size ...Bug
0
 
LVL 8

Author Comment

by:Naresh Patel
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 23

Expert Comment

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

Author Comment

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

Author Comment

by:Naresh Patel
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:Naresh Patel
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 23

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:Naresh Patel
ID: 41735066
ok noted,,

Thanks
0
 
LVL 8

Author Comment

by:Naresh Patel
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 23

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:Naresh Patel
ID: 41746762
Please look into my question as it is being abandon since 8 days.....Thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

926 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