VB Code for macro in excel workbook to run a WinZip Self-Extractor (.exe) that unzips CSV data source file to persons hard drive

[Margie Burgett]

Hello all of you Experts,

I have been working (searching the web) on this in chunks for a couple of days and I am stumped. First off, I am a newbie to all this.

Here is my question?

I need to tweak an Macro (currently downloadsa CSV file type from the internet) in an excel workbook to trigger a WinZip Self-Extractor (.exe) located on a server that will run and copy a CSV data source file to a users hard drive.

Hope this makes sense.... here is a text staw dog of the VB code I require.

Summary:
Using Excel 2003
1. User opens excel (.XLS) workbook
2. Click on Macro button that runs
a. Data.ZIP exe file on \\Server\Reports\Pivot_Data_Source\
b. Winzip runs and saves extracted file to C:\Reports\Pivot_Source_Data\

Here is the VB code that does work beautifully for downloading files from an URL that was provided by Mav55th on this forum. I just need to figure out how to adjust it to address the ZIP executable (Data.EXE) question above:

Sub Copy_PSD_file_from_SP_Directory()
' PSD = Pivot Source Data
' Copy_PSD_file_from_SP_FC_Prf_Pltfrm
' Code below provided from Excel VBA User Forum (Mav55th)

' Status bar notification
Application.DisplayStatusBar = True
Application.StatusBar = "CSV data source file being copied from SharePoint, please be patient"
Dim i As Integer
Const strUrl As String = "http://enhanced1.sharepoint.com/teams/Pivot_Source_Data/Data.csv"
Dim strSavePath As String
Dim returnValue As Long

strSavePath = "C:\Reports\Pivot_Source_Data\Data.csv"
returnValue = URLDownloadToFile(0, strUrl, strSavePath, 0, 0)

' Status bar notification
Application.DisplayStatusBar = True
Application.StatusBar = "Refresh Process completed"

MsgBox "Pivot source data refresh process complete."

End Sub

------------------
Thanks!

 

[cgmojoco]

Curious why Winzip?

Also curious, what are specifically happening in a. and b.? what are you doing with the data.zip "exe" file? What is that file meant for?

Have you thought of using native windows xp "zipping", try?
http://www.rondebruin.nl/windowsxpunzip.htm
It depends, in my workplace I cannot expect everyone to have Winzip installed (we are not allowed to install ANYTHING), everyone is using Windows and are XP and above...so it works...and works for the better.


If you do in fact need Winzip, try:
http://www.rondebruin.nl/unzip.htm

HTH

 

[Margie Burgett]

Hi,

Let me answer your questions below.
1. Curious why Winzip? - This is a company standard

2. Also curious, what are specifically happening in a. and b.? -
a. I create a WinZip Self-Extractor (DATA.exe) and save it to our company server location of \\Server\Reports\Pivot_Data_Source\DATA.exe. This DATA.exe 'package' contains a zipped .CSV data source file that will be used in Excel Pivot tables by business customers. The CSV Files are refreshed each week with new data. The CSV files sizes are huge (500K to 1 million rows of data) so they are zipped for faster download.

b. Winzip runs and saves extracted file to C:\Reports\Pivot_Source_Data\ What I am looking for is the VB code for the business user click a Macro button in the business report to run the \\Server\Reports\Pivot_Data_Source\DATA.exe. WinZip then executes, unzips the CSV file and saves it to the business users computer folder named C:\Reports\Pivot_Source_Data\

The new version of the C:\Reports\Pivot_Source_Data\Data.CSV is now on the business users computer and they can then refresh the Excel pivot table report.


what are you doing with the data.zip "exe" file? zipping large CSV data source files to reduce size and improve portability.

What is that file meant for? - business user excel 2003 pivot table reports

I hope that this answers your questions.

Thank you, Margie.

 

[cgmojoco]

Thank you

Interesting-

So if I understand you correctly the file you download is already zipped or are you doing that as a manual step to move to a server more readily?

Not sure you need to execute the data.zip exe (not familiar with WinZip---perhaps you have some scripted code within the package that calls for specific provisions).

There are ways to automate zipping files as well as unzipping in VBA, so that might save you time so that this can be done in a one step process possibly.

The 2nd link I provided you has code on how to unzip a zipped file with Winzip to a specified directory utilizing VBA. Do you think that will suffice?

By the way, windows XP and above can handle zipped files...so it is not "3rd party" dependent---

 

[Margie Burgett]

Hi and thanks for the quick reply....

So if I understand you correctly the file you download is already zipped or are you doing that as a manual step to move to a server more readily? Correct -- I create the WinZip Self-Extractor (DATA.exe) and save it to our company server location \\Server\Reports\Pivot_Data_Source\DATA.exe .

I will definately take a look at your links you provided. I just haven't had a chance to.

We currently have users on XP but new/replacment company computers now have Vista and the 2007 suite. That is why I would really like to roll out a business user solution that supports both OS systems.

I have more testing to do as I was sleeping last night and thought of a couple of new options.

Thank you very much for your guidance.

Margie.