Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Publish

Posted on 2016-07-27
19
Medium Priority
?
80 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

705 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