Pivot Details into New WorkBook

[Magerator]

Hello,

Just got my first problem solved and another one arises. (Thanks again to DonkeyOte)

So with the code below I can now get pivot details with a mouse click and simultenously it deletes "unwanted" columns. This works perfectly.

Now my question is : can I get pivot details open into new workbook rather than ?

If this is possible, then the second question is as follows :

This is not mandatory, but would be really cool.
Is there a way to give the new workbook a name ? ideally it would be Waste Proposal + Current datetime.

Please Login or Register  to view this content.

[DonkeyOte]

AFAIK you can't get the drill through to open in a new workbook automatically... you can however use code however to move the sheet to a new book as part of the routine:

Please Login or Register  to view this content.

(point being when the drilldown is activated the detail sheet becomes the active sheet)

In terms of file name - yes that's possible but where do you wish to save it ?

[Magerator]

Hey,

Thanks for the reply. Don't have the file with me now, I'm not at the office anymore. I will test first thing tomorrow morning.

About that file name and save location, that's yet to be decided where this file will be saved, but it will be a network drive.
If that is possible to save on a network drive will it make any difference if we refer it to with UNC-path or just a letter mapped to the drive.
So it will start either with Y:\ or with UNC path \\servername\and so on...

I will revert tomorrow,

Jyri

[DonkeyOte]

UNC is always best given it's possible (if not desired) for different clients to use different mappings.

[Magerator]

UNC is always best given it's possible (if not desired) for different clients to use different mappings.

Morning,

Okay the code works just as wanted, as usual

I have just talked with our Business Controller and she asked if this scenario would be possible :

1) User clicks on a macro it will open up a new workbook, let's say "WasteProposal.xls" and pivot details is copied there.
(This is allready done by you, just without that naming and saving.)

2) Every new waste proposal is copied to that same file(WasteProposal.xls) to the same sheet(WasteProposals), but they are just copied right after each other(couple of empty lines in between).

3) Could we get a timestamp and windows username to each proposal too.

End result would look something like I put in attached file.


Thank you again.

Jyri

[Magerator]

UNC is always best given it's possible (if not desired) for different clients to use different mappings.

And the save location for that file would be : \\servername\yhteiset\Wasteproposals

[DonkeyOte]

In reality - untested:

Please Login or Register  to view this content.

[Magerator]

You write that kind of code, not tested...send it here...and every single time it works perfectly ! That's just awesome stuff man

Couple of questions/development request still about the code.
Now I have a little problem if some user has that file(wasteproposals.xls) allready open, then of course it won't work if and when user has write rights to the file.

What I would like to do is to give all users "read" rights to the folder and the file, and tell the username and password in the code that will do the saving. Then it doesn't matter if some people have the file open, when they only have read rights to it.

Is this possible to tell in the code one username and password which will do the saving ?

Other questions are :
At the moment when user clicks on a macro, it really isnt' telling user enything, just does what it was supposed to do and might leave them wondering if something happened.
So what would be nice is :

1) When user first clicks on the macro, messagebox would appear saying : "Are you really sure you want to make a waste proposal of the selected items ! " Or something in that direction... answer "yes" will continue...answer "no" will abort the code.

2)If user answers "yes" then after the code is complete, message box would say : "Waste Proposal Complete , file can be found in "Waste Proposal" directory on the Common Files drive"....or something in that direction.

Jyri

[DonkeyOte]

Now I have a little problem if some user has that file(wasteproposals.xls) allready open, then of course it won't work if and when user has write rights to the file.

What I would like to do is to give all users "read" rights to the folder and the file, and tell the username and password in the code that will do the saving. Then it doesn't matter if some people have the file open, when they only have read rights to it.

Is this possible to tell in the code one username and password which will do the saving ?

It might be simplest to apply a write reserved password to the file that only you know - this way the file will be opened by others as read-only but the code can use the password to write to the file.
(you can set this password via File -> Save As -> General Options -> Password to Modify)

The modified code line would be:

Please Login or Register  to view this content.

remember though that those determined enough can circumvent security if they really want to.

1) When user first clicks on the macro, messagebox would appear saying : "Are you really sure you want to make a waste proposal of the selected items ! " Or something in that direction...
answer "yes" will continue...answer "no" will abort the code.

You can use a pre-emptive MsgBox to do this (prior to first With Application line) - in general terms:

Please Login or Register  to view this content.

2)If user answers "yes" then after the code is complete, message box would say : "Waste Proposal Complete , file can be found in "Waste Proposal" directory on the Common Files drive"....or something in that direction

Again a msgbox should suffice... in general terms prior to ExitPoint: line

Please Login or Register  to view this content.

[Magerator]

Okay, this is just cool. Now it's exactly as needed.

I know one thing still might be that, when script is run and the waste proposal has been made, Business Controller might want to have an email that "new waste proposal has been made". no attachments , nothing fancy, just e-mail with subject field : "New Waste Proposal has been made"

If this is a bigger issue, then we can leave it for later development idea.
Just a thought that would be icing on the cake

- Jyri

[DonkeyOte]

Bit of feature creep here... for info. on emailing see: http://www.rondebruin.nl/sendmail.htm for plenty of examples...

eg: http://www.rondebruin.nl/mail/folder3/smallmessage.htm

Obviously code rather depends on your mail client.

[Magerator]

I found a solution from the link you gave me for the outlook sending.
Don't implement it yet, but I'm ready when asked

Now all I'm worried is how I can contribute back to this site anything.

It won't be coding, because I just don't even understand what I have copy pasted last few days.../cry.

My speciality is Crystal Reports, which is my passion. So I can give tips to that, but I guess it's not needed here.
Maybe if you have some donate thing in this site, that could be useful. With this kind of help, I'm sure many of us is willing to donate few dollars so this site keeps active and as helpful as it is now.

Will mark this topic solved , was awesome stuff donkeyote...just awesome.

- Jyri

[DonkeyOte]

Any assistance you get here is free of charge (the owners make their money through advertising etc)

If you would like to make a donation to a charity of your choice (in the name of ExcelForum.com) in recognition of the help you've had that would be great.