VBA to copy data from excel worksheet in zip file

[gee83]

Hi I'm wondering if there is a reasonably simple way of doing a straight forward task. I have been looking at VBA examples of opening zip files but they are overly complex for what I want to do. If its too complex a task for VBA I may just do manually but thought I would ask for advice first.

I currently have a macro that runs to save an email attachment from an email that is received each evening, the attachment (zip) is saved in the Documents folder and thats it. What I wanted to do was (in the morning, the email is sent in the evening) open an excel macro and run it so that it goes to the zip which contains an excel file and copies the data from the sheet (theres only one tab), ideally with a filter applied first on a particular column, then pastes that data to a new worksheet. Any help/advice would be greatly appreciated.

[karedog]

For example, for attached files (EF1162840.xls and File1.zip), File1.zip is placed on the same folder with EF1162840.xls.
For your actual file, change this line to location of your .zip file : ReadZip ThisWorkbook.Path & "\File1.zip"

The macro is copying sheet "DATA" in the .zip file to current workbook.
You said that you want to filter the column first, but I don't know what your data layout is, and what filter(s) to applied, so let this be a seperate job (not "reading inside zipped excel file" job).

Please Login or Register  to view this content.

[gee83]

Thanks so much, this works perfectly!
The filter I was looking to add was on column F and is a text filter of 'begins with' the letters 'BT'. Is it possible to apply the filter before the data is selected, so that only the filtered data is then coped across?

[karedog]

You are welcome, thanks for the rep.points.
This code assumes that your data in .zip file start on cell A1 with continous region.
If this doesn't work, you need to upload your zip file.

Please Login or Register  to view this content.

[gee83]

No worries and thanks again. So does the code in your last post fully replace the original code or does it just need added? As I notice the 'Readzip...' command is no longer there, is it not needed to point to the right file?

[karedog]

You need to delete all the code first, then add the code from post #4, then add only Sub Test() code (3 lines) from post #2.
In short, replace Sub ReadZip() from post #2 with the new one in post #4.

[gee83]

Again, works perfectly, thanks for your help!

[karedog]

You are welcome.

Don't forget to mark this thread as solved :
please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.