I have created a pivot report against a .CSV data file.

Everymonth I want to email the combination of the CSV file and the
Excel pivot report to our users. So once the user clicks on the zip
file it must save the CSV file in say c:\temp and then save the Excel
pivot report as well in c:\temp and the Excel workbook pivot report
start against the file in C:\temp

Please help with code how I can do this.

Thanks
Karen

wrote in message oups.com
I have created a pivot report against a .CSV data file.

Everymonth I want to email the combination of the CSV file and the
Excel pivot report to our users. So once the user clicks on the zip
file it must save the CSV file in say c:\temp and then save the Excel
pivot report as well in c:\temp and the Excel workbook pivot report
start against the file in C:\temp

Please help with code how I can do this.

Hi Karen,

How about using self extracting cabinet file instead of zip file?
You can write desired post setup procedure in "Setup.vbs" at
the source folder.

See this post on microsoft.public.scripting.wsh
" Duplicate folder tree"


Some meaningless characters are included at the line using MsgBox
function, though.

--
Miyahn (Masataka Miya****a) JPN
Microsoft MVP for Microsoft Office - Excel(Jan 2005 - Dec 2005)

Most zip programs (in addition to CAB) will also
create self-extracting files (SFX), which you can have
unpacked to the TEMP folder, with or without user
interaction, and then set a file to be opened after
unpacking. But either way it has to go as an EXE file.
A plain zip won't work. So the target machine has to
be set up to allow EXEs in email.
I use PowerArchiver 6 and, if I'm not mistaken, I
think that it will open any file from the SFX. So you
could have it run a .VBS setup script or just have
it open an excel file directly, if that works.

It used to be easy to use that kind of method, but
many people now have over-zealous security and
email filters. Many people can't receive an EXE and
may not know how to override that restriction. Many
more people will have anti-virus that acts up if you
try to run a VBS file after unpacking. You can, ironically,
avoid that problem by using a compiled EXE inside
the SFX instead!

--

(Remove Xs for return email.)
wrote in message
oups.com...
I have created a pivot report against a .CSV data file.

Everymonth I want to email the combination of the CSV file and the
Excel pivot report to our users. So once the user clicks on the zip
file it must save the CSV file in say c:\temp and then save the Excel
pivot report as well in c:\temp and the Excel workbook pivot report
start against the file in C:\temp

Please help with code how I can do this.

Thanks
Karen

Miyahn & Mayayana

Thanks for your response can you show me a script or tool
that enables me to create a self extracting exe with the
Excel pivot report and the data file included in it.

So when the user clicks on it will start Excel and open
the report.

Appreciate your help.

Thanks
Karen

Are you familiar with making zip files?
WinZip, PowerArchiver, or something like
that? I can give you directions for P.A.
and you can extrapolate from that. I've
never used Excel but I assume that you just need
the two file to be in the same place when
one is run. I don't know what a pivot report is.
Presumably that's a single file? An XLS or
something like that?

Open Power Archiver

Click New and start a new zip file.

Add your 2 files to the zip.

Click Actions - Make EXE File.
A small window will open showing that
the EXE will unload to the Temp folder.

In the box "Command Line After
Extracting" put the file name that you want
to open. (Name only, not full path.)

Click OK and it will create an EXE with the
same name as the zip. When opened, the EXE
will dump its contents into the Temp folder and
run the file that you named in Command Line.


--

(Remove Xs for return email.)
wrote in message
oups.com...
Miyahn & Mayayana

Thanks for your response can you show me a script or tool
that enables me to create a self extracting exe with the
Excel pivot report and the data file included in it.

So when the user clicks on it will start Excel and open
the report.

Appreciate your help.

Thanks
Karen

wrote in message oups.com...
Miyahn & Mayayana

Thanks for your response can you show me a script or tool
that enables me to create a self extracting exe with the
Excel pivot report and the data file included in it.

So when the user clicks on it will start Excel and open
the report.

Don't you see my script in the article on microsoft.public.scripting.wsh?

You can write a script to manipulate Excel in "Setup.vbs" .
Setup.vbs will be executed after extracting files.

Prepare appropriate folder and save all files + Setup.vbs.
Then Drag & Drop the folder icon to "MakeSCab.vbs".

--
Miyahn (Masataka Miya****a) JPN
Microsoft MVP for Microsoft Office - Excel(Jan 2005 - Dec 2005)

Hi Miyahn

I can see the script you have posted today I could not see it
yesterday.

But the script is so big not sure where I need to make changes and what
changes.

All I have is a file test.xls which the pivot report against a Access
database testdb.mdb.

The pivot report test.xls runs against the testdb.mdb I want to package
both together
and send them in a email and users to open the CAB file they must be
able to access
the pivot report and report of it straightaway.

Please clarify what changes I need to do to the script you have
provided to
implement this.

Do users need to have WSH installed on their PC for this to work how
can they
open the CAB file with what application ?

Thanks
Karen



Miyahn wrote:
wrote in message oups.com...
Miyahn & Mayayana

Thanks for your response can you show me a script or tool
that enables me to create a self extracting exe with the
Excel pivot report and the data file included in it.

So when the user clicks on it will start Excel and open
the report.

Don't you see my script in the article on microsoft.public.scripting.wsh?

You can write a script to manipulate Excel in "Setup.vbs" .
Setup.vbs will be executed after extracting files.

Prepare appropriate folder and save all files + Setup.vbs.
Then Drag & Drop the folder icon to "MakeSCab.vbs".

--
Miyahn (Masataka Miya****a) JPN
Microsoft MVP for Microsoft Office - Excel(Jan 2005 - Dec 2005)

wrote in message oups.com
Hi Miyahn

I can see the script you have posted today I could not see it
yesterday.

But the script is so big not sure where I need to make changes and what
changes.

Hi Karen,

The only line you must change is the line declaring constant DESTROOT.
| Const DESTROOT = "C:\Program Files\" ' Install Target

All I have is a file test.xls which the pivot report against a Access
database testdb.mdb.

Here are two sample code for 'Setup.vbs'.
1: Open specified Excel file and run macro.
With CreateObject("Excel.Application")
.Visible = True
.Workbooks.Open "C:\temp\srcfolder\test.xls" ' <-need modify this line
.Run "macroname", arg1, arg2 ' <-need modify this line
End With

2: Open the installed folder by Explorer.
Const DESKTOP = &H0
With CreateObject("Shell.Application")
.Explore .NameSpace(DESKTOP).ParseName(WScript.ScriptFullNa me).Parent
End With

Do users need to have WSH installed on their PC for this to work how
can they
open the CAB file with what application ?

There are no need of WSH to execute self-extracting cabinet file.
Extracting can be done on Win95 or lator.(not sure)

But if you use post-setup script (Setup.vbs), users need to have WSH
installed on their PC.

--
Miyahn (Masataka Miya****a) JPN
Microsoft MVP for Microsoft Office - Excel(Jan 2005 - Dec 2005)

Hi Miyahn

Many thanks for the procedure.

Ok this is what I did:

I created a folder c:\temp\scr and put the file you have posted in your
earlier email
as createcab.vbs and I changed the destination as C:\TEMP

I created another folder c:\temp\scr2 and put the following files
Test.xls and the
test.mdb that the .xls file reports on and the setup.vbs file you
provided.

I dragged and dropped c:\temp\scr2 on to the file
c:\temp\scr\createcab.vbs and I
see a script running not sure were it put the .CAB file on the C: drive
and with
what name.

Also, instead of dragging and dropping I want to script the creation of
the
..CAB file. What I mean is I want to run the CAB creation script file
and I want it
to create the .CAB file at a defined location instead of dragging and
dropping. Not
sure what changes to the script are needed.

Thanks
Karen


Miyahn wrote:
wrote in message oups.com
Hi Miyahn

I can see the script you have posted today I could not see it
yesterday.

But the script is so big not sure where I need to make changes and what
changes.

Hi Karen,

The only line you must change is the line declaring constant DESTROOT.
| Const DESTROOT = "C:\Program Files\" ' Install Target

All I have is a file test.xls which the pivot report against a Access
database testdb.mdb.

Here are two sample code for 'Setup.vbs'.
1: Open specified Excel file and run macro.
With CreateObject("Excel.Application")
.Visible = True
.Workbooks.Open "C:\temp\srcfolder\test.xls" ' <-need modify this line
.Run "macroname", arg1, arg2 ' <-need modify this line
End With

2: Open the installed folder by Explorer.
Const DESKTOP = &H0
With CreateObject("Shell.Application")
.Explore .NameSpace(DESKTOP).ParseName(WScript.ScriptFullNa me).Parent
End With

Do users need to have WSH installed on their PC for this to work how
can they
open the CAB file with what application ?

There are no need of WSH to execute self-extracting cabinet file.
Extracting can be done on Win95 or lator.(not sure)

But if you use post-setup script (Setup.vbs), users need to have WSH
installed on their PC.

--
Miyahn (Masataka Miya****a) JPN
Microsoft MVP for Microsoft Office - Excel(Jan 2005 - Dec 2005)

Hi Miyahn

Here is the script changes I did:

Const DESTROOT = "C:\TEMP\" ' Install Target


and the following is the setup.vbs

With CreateObject("Excel.Application")
.Visible = True
.Workbooks.Open "C:\temp\test.xls"
End With


In c:\temp\scr2 I have the CreateCAB.vbs file and in c:\temp\scr I have
the test.xls and test.mdb as well as the setup.vbs.

I drag and drop c:\temp\scr onto c:\temp\scr2\createcab.vbs

and it created c:\temp\scr.exe when I run scr.exe looks like it is
running setup.vbs
but it throws the following errors:

script: c:\temp\scr\setup.vbs
line 3
char 3
Error: c:\temp\test.xls could not be opened.

When I check c:\temp for the files test.xls and test.mdb which the CAB
creation script must first place it there and then start setup.vbs
looks like the script did not put the .xls and .mdb file in c:\temp

Also, instead of dragging and dropping the folder to be packaged onto
createcab.vbs script can you please let me know what changes I must do
to it so I can run in batch and specify say a command line argument of
the folder to be packaged and the destination and file name of the .exe
file.

Thanks
Karen


Miyahn wrote:
wrote in message oups.com
Hi Miyahn

I can see the script you have posted today I could not see it
yesterday.

But the script is so big not sure where I need to make changes and what
changes.

Hi Karen,

The only line you must change is the line declaring constant DESTROOT.
| Const DESTROOT = "C:\Program Files\" ' Install Target

All I have is a file test.xls which the pivot report against a Access
database testdb.mdb.

Here are two sample code for 'Setup.vbs'.
1: Open specified Excel file and run macro.
With CreateObject("Excel.Application")
.Visible = True
.Workbooks.Open "C:\temp\srcfolder\test.xls" ' <-need modify this line
.Run "macroname", arg1, arg2 ' <-need modify this line
End With

2: Open the installed folder by Explorer.
Const DESKTOP = &H0
With CreateObject("Shell.Application")
.Explore .NameSpace(DESKTOP).ParseName(WScript.ScriptFullNa me).Parent
End With

Do users need to have WSH installed on their PC for this to work how
can they
open the CAB file with what application ?

There are no need of WSH to execute self-extracting cabinet file.
Extracting can be done on Win95 or lator.(not sure)

But if you use post-setup script (Setup.vbs), users need to have WSH
installed on their PC.

--
Miyahn (Masataka Miya****a) JPN
Microsoft MVP for Microsoft Office - Excel(Jan 2005 - Dec 2005)

wrote in message oups.com
Hi Miyahn

Here is the script changes I did:

Const DESTROOT = "C:\TEMP\" ' Install Target


and the following is the setup.vbs

With CreateObject("Excel.Application")
.Visible = True
.Workbooks.Open "C:\temp\test.xls"
End With

Path Should be "C:\temp\scr\test.xls

The self-extracting cabinet file create a folder under DESTROOT which
has same name of the source folder.
So in yor case, the self-extracting cabinet file is over writing already existing
files.(test.xls,Setup.vbs,etc.)

Also, instead of dragging and dropping the folder to be packaged onto
createcab.vbs script can you please let me know what changes I must do
to it so I can run in batch and specify say a command line argument of
the folder to be packaged and the destination and file name of the .exe
file.

I recommend to save MakeSCab.vbs or short-cut to the script into
SendTo folder.
Then you can invoke the script by right-clicking source folder - 'SendTo'
- MakeSCab.vbs.

If you want 2nd or 3rd parameter to specify the destination and EXE name,
change DESTROOT from constant to variable, and using Wscript.Argumets'
item, change DESTROOT value and rename the output file.

--
Miyahn (Masataka Miya****a) JPN
Microsoft MVP for Microsoft Office - Excel(Jan 2005 - Dec 2005)

Hi Miyahn

Many thanks for taking time to clarify everything works fine the only
issue I have is I do not want to run the MakeSCab.vbs by right clicking
on it.

Is it possible I can specify c:\temp\scr which is the folder I am
packaging as an argument to MakeSCab.vbs and I want to create the
package by running MakeScab.vbs from command line.

Kindly, can you explain what I need to change in which code so I can
pass the folder to be packaged as a command line parameter.

Thanks
Karen

wrote in message ups.com
Hi Miyahn

Many thanks for taking time to clarify everything works fine the only
issue I have is I do not want to run the MakeSCab.vbs by right clicking
on it.

Is it possible I can specify c:\temp\scr which is the folder I am
packaging as an argument to MakeSCab.vbs and I want to create the
package by running MakeScab.vbs from command line.

Hi Karen,

My script accept one command line parameter, as it is.
If you drag and drop or use sendto, shell program invoke the script
passing the full path of the source folder as the 1st. parameter.
The script is accepting this parameter by Wscript.Arguments(0).
Please read carefully the code( and post).

So you can run the script by this syntax.
wscript full-path-of-MakeSCab.vbs full-path-of-source-folder
Note:
If full path is including space, you must put it in double quotation marks.

For example,
wscript "C:\Program Files\VBS\MakeSCab.vbs" C:\temp\scr

# I think, this way is hard to use.

--
Miyahn (Masataka Miya****a) JPN
Microsoft MVP for Microsoft Office - Excel(Jan 2005 - Dec 2005)

Miyahn

Many thanks for taking time to clarify the issues on this script.

Is there a small change we can make to this script to password protect
this self extracting executable please clarify.

So for example if I want to set the password to 19thOct05 for the self
extracting exe to protect the data so it is not open can you please
clarify how I can do it.

Thanks
Karen

wrote in message oups.com
Is there a small change we can make to this script to password protect
this self extracting executable please clarify.

Sorry, self-extracting cabinet file does not support password protection.
(As far as I know.)

--
Miyahn (Masataka Miya****a) JPN
Microsoft MVP for Microsoft Office - Excel(Jan 2005 - Dec 2005)

Hi Miyahn

If not password is it possible to encrypt a self extracting file or is
there any form of securing the self extracting file.

Thanks
Karen

wrote in message ups.com
Hi Miyahn

If not password is it possible to encrypt a self extracting file or is
there any form of securing the self extracting file.

Hi Karen,

I don't know the way to do that for cabinet file.
If you need passward protection, use the third party zip utility which
support commandline execution and password protection to make
self-extracting EXE file.

I am using the posted script because it doesn't need to install any software
manufactured by the third party.(on Win2k and WinXP)

I recommend you to clarify your needs and your environments in the first post.

--
Miyahn (Masataka Miya****a) JPN
Microsoft MVP for Microsoft Office - Excel(Jan 2005 - Dec 2005)

Hi Miyahn

My only additional requirement to the original post is security. Since
the file will have data in it any 3rd party should not be able to open
the self extracting executable.

How complex is it to include userid based security assuming my Windows
login is
-MIDDLEK. I want to put this in the self extracting executable that it
does not run the unarchive unless the user is MIDDLEK and from PC Name
- MIDDLEK.xyz.com

So all I am proposing is a additional check for username and PC name
before the unarchive runs.

Thanks for your time.

Thanks
Karen

Hi Miyahn


My only additional requirement to the original post is security. Since
the file will have data in it any 3rd party should not be able to open
the self extracting executable.


How complex is it to include userid based security assuming my Windows
login is
-MIDDLEK. I want to put this in the self extracting executable that it
does not run the unarchive unless the user is MIDDLEK and from PC Name
- MIDDLEK.xyz.com


So all I am proposing is a additional check for username and PC name
before the unarchive runs.


Thanks for your time.


Thanks
Karen