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)