Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i open a zip file using a macro
i would like to open a zip file when a command button is pressed any ideas
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i open a zip file using a macro
http://www.rondebruin.nl/tips.htm
near the bottom are three links for information on working with zip files. -- Regards, Tom Ogilvy "pete the greek" wrote in message ... i would like to open a zip file when a command button is pressed any ideas |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i open a zip file using a macro
Hi Pete
If you are a WinZip user try http://www.rondebruin.nl/unzip.htm If you use Windows Xp zip program http://www.rondebruin.nl/windowsxpzip.htm -- Regards Ron de Bruin http://www.rondebruin.nl "pete the greek" wrote in message ... i would like to open a zip file when a command button is pressed any ideas |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i open a zip file using a macro
Hi Ron,
Finally a solution on using XPs compressed folders! However McAfee VirusScan was complaining on your NewZip procedure. Apparently FSO's CreateTextFile is suspect! Here's my alternative, which McAfee allows without complaints (shorter and simpler too) Sub NewZip(sPath) 'Create empty Zip File If Len(Dir(sPath)) 0 Then Kill sPath Open sPath For Output As #1 Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0) Close #1 End Sub Sub newziptest() ' Call NewZip("c:\test.zip") End Sub HTH -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Ron de Bruin wrote : Hi Pete If you are a WinZip user try http://www.rondebruin.nl/unzip.htm If you use Windows Xp zip program http://www.rondebruin.nl/windowsxpzip.htm |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i open a zip file using a macro
Hi keepITcool
McAfee A few years ago McAfee start deleting my mail in my inbox. Since then I use another program, first Norton and now Trend Micro. I test your code and try to update the site this evening Thanks for the feedback -- Regards Ron de Bruin http://www.rondebruin.nl "keepITcool" wrote in message .com... Hi Ron, Finally a solution on using XPs compressed folders! However McAfee VirusScan was complaining on your NewZip procedure. Apparently FSO's CreateTextFile is suspect! Here's my alternative, which McAfee allows without complaints (shorter and simpler too) Sub NewZip(sPath) 'Create empty Zip File If Len(Dir(sPath)) 0 Then Kill sPath Open sPath For Output As #1 Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0) Close #1 End Sub Sub newziptest() ' Call NewZip("c:\test.zip") End Sub HTH -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Ron de Bruin wrote : Hi Pete If you are a WinZip user try http://www.rondebruin.nl/unzip.htm If you use Windows Xp zip program http://www.rondebruin.nl/windowsxpzip.htm |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i open a zip file using a macro
Hi keepITcool
You code example is working Ok and is shorter and simpler . BTW : do you have better idea about the wait code on my site http://www.rondebruin.nl/windowsxpzip.htm#Wait I test a few other things but this is the only thing that is working for me (also with big Excel files 5-10 mb) -- Regards Ron de Bruin http://www.rondebruin.nl "keepITcool" wrote in message .com... Hi Ron, Finally a solution on using XPs compressed folders! However McAfee VirusScan was complaining on your NewZip procedure. Apparently FSO's CreateTextFile is suspect! Here's my alternative, which McAfee allows without complaints (shorter and simpler too) Sub NewZip(sPath) 'Create empty Zip File If Len(Dir(sPath)) 0 Then Kill sPath Open sPath For Output As #1 Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0) Close #1 End Sub Sub newziptest() ' Call NewZip("c:\test.zip") End Sub HTH -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Ron de Bruin wrote : Hi Pete If you are a WinZip user try http://www.rondebruin.nl/unzip.htm If you use Windows Xp zip program http://www.rondebruin.nl/windowsxpzip.htm |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i open a zip file using a macro
Ron.. the Wait solution...Counting! I also noted that the NameSpace method fails if you pass it a string variable.... Sub Zip_All_Files_in_Folder() Dim sDefPath$, sDate$ Dim vZipPath, vFldPath 'MUST be Variants! Dim lFldCount& Dim oApp As Object Dim oZip As Object Dim oFld As Object vFldPath = "e:\testje\" '<< Change sDefPath = Application.DefaultFilePath sDefPath = "e:\" If Right(sDefPath, 1) < "\" Then sDefPath = sDefPath & "\" End If sDate = Format(Now, " dd-mmm-yy h-mm-ss") vZipPath = sDefPath & "MyFilesZip " & sDate & ".zip" 'Create empty Zip File Call NewZip(vZipPath) Set oApp = CreateObject("Shell.Application") Set oFld = oApp.Namespace(vFldPath) Set oZip = oApp.Namespace(vZipPath) 'Get the count of files to be compressed lFldCount = oFld.Items.Count 'Copy the files to the compressed folder oZip.CopyHere oFld.Items 'Keep script waiting until Compressing is done Do Until oZip.Items.Count = lFldCount Application.Wait (Now + TimeValue("0:00:01")) Loop MsgBox "You find the zipfile he" & vbLf & vZipPath Set oFld = Nothing Set oZip = Nothing Set oApp = Nothing End Sub -- -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Ron de Bruin wrote : Hi keepITcool You code example is working Ok and is shorter and simpler . BTW : do you have better idea about the wait code on my site http://www.rondebruin.nl/windowsxpzip.htm#Wait I test a few other things but this is the only thing that is working for me (also with big Excel files 5-10 mb) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i open a zip file using a macro
Good suggestion
I change the NewZip macro to yours on the site Thanks -- Regards Ron de Bruin http://www.rondebruin.nl "keepITcool" wrote in message .com... Ron.. the Wait solution...Counting! I also noted that the NameSpace method fails if you pass it a string variable.... Sub Zip_All_Files_in_Folder() Dim sDefPath$, sDate$ Dim vZipPath, vFldPath 'MUST be Variants! Dim lFldCount& Dim oApp As Object Dim oZip As Object Dim oFld As Object vFldPath = "e:\testje\" '<< Change sDefPath = Application.DefaultFilePath sDefPath = "e:\" If Right(sDefPath, 1) < "\" Then sDefPath = sDefPath & "\" End If sDate = Format(Now, " dd-mmm-yy h-mm-ss") vZipPath = sDefPath & "MyFilesZip " & sDate & ".zip" 'Create empty Zip File Call NewZip(vZipPath) Set oApp = CreateObject("Shell.Application") Set oFld = oApp.Namespace(vFldPath) Set oZip = oApp.Namespace(vZipPath) 'Get the count of files to be compressed lFldCount = oFld.Items.Count 'Copy the files to the compressed folder oZip.CopyHere oFld.Items 'Keep script waiting until Compressing is done Do Until oZip.Items.Count = lFldCount Application.Wait (Now + TimeValue("0:00:01")) Loop MsgBox "You find the zipfile he" & vbLf & vZipPath Set oFld = Nothing Set oZip = Nothing Set oApp = Nothing End Sub -- -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Ron de Bruin wrote : Hi keepITcool You code example is working Ok and is shorter and simpler . BTW : do you have better idea about the wait code on my site http://www.rondebruin.nl/windowsxpzip.htm#Wait I test a few other things but this is the only thing that is working for me (also with big Excel files 5-10 mb) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i open a zip file using a macro
cheer folks i look forwrd to trying these out
"Tom Ogilvy" wrote: http://www.rondebruin.nl/tips.htm near the bottom are three links for information on working with zip files. -- Regards, Tom Ogilvy "pete the greek" wrote in message ... i would like to open a zip file when a command button is pressed any ideas |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i open a zip file using a macro
I can't tell you how much I apprecaite your help on zipping files. I will be
sending these out to my locations tomorrow. Starting the first of the year, all the locations will be sending in their reports (hopefully zipped using your code) by email. I use Outlook but do not have WinZip installed. I looked at your code to use the XP unzip, but I think I need a little bit more. My emails with the attached reports come into a specific folder. I need to open the attached zip files, open the worksheet, copy data from 17 cells on sheet 2 into a "master file" on my computer, and then save the zip file to an archive folder (static location without changing the file name). What's the possibility of writing one macro that will do all of that? I have one now that does everything once I open the attached file. The zipped file adds a wrinkle and I would like to further automate it to look in the specified inbox in Outlook and if it finds any email with a zipped attachement (can partially test the filename) run the whole procedure. Thanks much for all your help so far! "Ron de Bruin" wrote: Hi keepITcool McAfee A few years ago McAfee start deleting my mail in my inbox. Since then I use another program, first Norton and now Trend Micro. I test your code and try to update the site this evening Thanks for the feedback -- Regards Ron de Bruin http://www.rondebruin.nl "keepITcool" wrote in message .com... Hi Ron, Finally a solution on using XPs compressed folders! However McAfee VirusScan was complaining on your NewZip procedure. Apparently FSO's CreateTextFile is suspect! Here's my alternative, which McAfee allows without complaints (shorter and simpler too) Sub NewZip(sPath) 'Create empty Zip File If Len(Dir(sPath)) 0 Then Kill sPath Open sPath For Output As #1 Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0) Close #1 End Sub Sub newziptest() ' Call NewZip("c:\test.zip") End Sub HTH -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Ron de Bruin wrote : Hi Pete If you are a WinZip user try http://www.rondebruin.nl/unzip.htm If you use Windows Xp zip program http://www.rondebruin.nl/windowsxpzip.htm |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i open a zip file using a macro
Look on Dick's site
http://www.dicks-clicks.com/excel/olRetrieving.htm -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I can't tell you how much I apprecaite your help on zipping files. I will be sending these out to my locations tomorrow. Starting the first of the year, all the locations will be sending in their reports (hopefully zipped using your code) by email. I use Outlook but do not have WinZip installed. I looked at your code to use the XP unzip, but I think I need a little bit more. My emails with the attached reports come into a specific folder. I need to open the attached zip files, open the worksheet, copy data from 17 cells on sheet 2 into a "master file" on my computer, and then save the zip file to an archive folder (static location without changing the file name). What's the possibility of writing one macro that will do all of that? I have one now that does everything once I open the attached file. The zipped file adds a wrinkle and I would like to further automate it to look in the specified inbox in Outlook and if it finds any email with a zipped attachement (can partially test the filename) run the whole procedure. Thanks much for all your help so far! "Ron de Bruin" wrote: Hi keepITcool McAfee A few years ago McAfee start deleting my mail in my inbox. Since then I use another program, first Norton and now Trend Micro. I test your code and try to update the site this evening Thanks for the feedback -- Regards Ron de Bruin http://www.rondebruin.nl "keepITcool" wrote in message .com... Hi Ron, Finally a solution on using XPs compressed folders! However McAfee VirusScan was complaining on your NewZip procedure. Apparently FSO's CreateTextFile is suspect! Here's my alternative, which McAfee allows without complaints (shorter and simpler too) Sub NewZip(sPath) 'Create empty Zip File If Len(Dir(sPath)) 0 Then Kill sPath Open sPath For Output As #1 Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0) Close #1 End Sub Sub newziptest() ' Call NewZip("c:\test.zip") End Sub HTH -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Ron de Bruin wrote : Hi Pete If you are a WinZip user try http://www.rondebruin.nl/unzip.htm If you use Windows Xp zip program http://www.rondebruin.nl/windowsxpzip.htm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open Excel file from VB and open MACRO | Excel Discussion (Misc queries) | |||
Open a file do a macro ( made) and open next succesive file | Excel Programming | |||
Macro to call a file that has a auto open macro in the file itself | Excel Programming | |||
Automate open file, update links, run macro, close and save file | Excel Programming |