Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to create a macro to import data from a zip file into an existing spreadsheet?

Posted on 2014-07-22
22
Medium Priority
?
635 Views
Last Modified: 2014-07-31
Zip file location: The same directory as the file that contains this macro when ran
Zip file name: “Daily_Report.zip” (Overwrite existing file)
Unzip destination: Current directory
File name: “Daily_Report.xlsx”

Import data
From sheet: “New”
Import to: Current workbook
Destination sheet: “New”

From sheet: “Old”
Import to: Current workbook
Destination sheet: “Old”

Note: Unable to upload a zip fie so I just updated the sample file
Daily-Report.xlsx
Report.xlsx
0
Comment
Question by:kbay808
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 10
22 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 40216457
what is the zip file here ? Daily-Report.xls or Report.xlsx

gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40216462
I presume Daily-Report.xlsx is the zipped file but your posted Report.xlsx has no data is that an error or it should be like this ?
Where you want the data to be added at the end of each sheet ?
gowlfow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40216811
1 more still not clear where do you need to data to be saved to.

I understand that Daily-Report.xlsx is inside the Daily-Report.zip file and you need to read from Daily-Report.xlsx and save data where to Report.xlsx ??? or ... ???

pls clarify as I have the macro ready but just missing what to copy and to where.
gowflow
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:kbay808
ID: 40217162
I need the macro in the Report.xlsx to pull the data from the Daily_Report.xlsx(zip) and populate the 2 cleared tabs (New & Old).
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40217175
ok let me get this right.

Macro is in Report.xlsm (as macro enabled workbook are xlsm and not xslx) the 2 tabs are empty and we copy over the data that exist from the zip file onto these 2 tabs.

Right ??

ok my question then is what happens next ??? the next day you have in Report.xlsm the data that we imported previously how to import the new data ???

You are missing something in your logic.
gowflow
0
 

Author Comment

by:kbay808
ID: 40217199
Good point.  I need to clear all previous data before importing.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40217365
ok then the macro will, each time activated:
1) Clear all previous data
2) Import new data

ok ?
gowflow
0
 

Author Comment

by:kbay808
ID: 40217412
Perfect
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40218081
ok here is your solution.

This macro requires 7Zip that could be downloaded at this location as it works best with. Do not know if WinZip would give same results as tested it on WinRAR and it didn't perform correctly.

It requires 7-Zip (free / open source):
http://www.7-zip.org/


Here is the code for this macro:

Sub ImportDailyData()
Dim strFileName As String, str7ZIP As String, strZipFile As String, strDestinationFolder As String, strCMD As String
Dim WshShell As Object, fso As Object
Dim WB As Workbook
Dim ThisWB As Workbook
Dim WS As Worksheet
Dim ThisWS As Worksheet

strFileName = "Daily-Report.xlsx"
str7ZIP = "C:\Program Files\7-Zip\7z.exe"
strDestinationFolder = ActiveWorkbook.Path
strZipFile = strDestinationFolder & "\Daily-Report.zip"
Set ThisWB = ActiveWorkbook

If Right(strDestinationFolder, 1) <> "\" Then strDestinationFolder = strDestinationFolder & "\"

Set WshShell = CreateObject("Wscript.Shell")
Set fso = CreateObject("Scripting.FileSystemObject")

If Not fso.FileExists(str7ZIP) Then
    MsgBox "Could not find 7-Zip:  " & vbCrLf & vbCrLf & str7ZIP, vbExclamation, "Daily-Report.xlsx"
    Exit Sub
End If

strCMD = Chr(34) & str7ZIP & Chr(34) & " e -i!" & _
        Chr(34) & strFileName & Chr(34) & " -o" & _
        Chr(34) & strDestinationFolder & Chr(34) & " " & _
        Chr(34) & strZipFile & Chr(34) & " -y"


WshShell.Run strCMD, 0, True

If Not fso.FileExists(strDestinationFolder & strFileName) Then
    MsgBox "Failed to get file:  " & strDestinationFolder & strFileName, vbExclamation, "Daily-Report.xlsx"
    Exit Sub
Else
    '---> Stop Events
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    '---> Open the Import Workbook
    Set WB = Workbooks.Open(strDestinationFolder & strFileName)
    
    '---> Clean Current Data in present workbook
    For Each WS In ThisWB.Worksheets
        If WS.Name <> "Main" Then
            WS.UsedRange.EntireRow.Delete
        End If
    Next WS
    '---> Get Data
    For Each WS In WB.Worksheets
        Set ThisWS = ThisWB.Worksheets(WS.Name)
        WS.UsedRange.Copy ThisWS.Range("A1")
        ThisWS.UsedRange.EntireColumn.AutoFit
    Next WS
    
    '---> Close WB
    Application.DisplayAlerts = False
    WB.Close savechanges:=False
    Kill strDestinationFolder & strFileName
    Application.DisplayAlerts = True
    
    '---> Clean Variables
    Set WB = Nothing
    Set WS = Nothing
    
    '---> Enable Events
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    
    '---> Advise user
    MsgBox ("Import Daily data successfull.")
End If


End Sub

Open in new window



As you can see you should replace in this code (if you don't use 7Zip)
this line
str7ZIP = "C:\Program Files\7-Zip\7z.exe"

by the location of the Win Zip or Zip program on your pc

You will notice in the attach file that I added the sheet Main that holds a button that once you enable macros simply activate the button and it will import the data that is in the file: "Daily-Report.zip" in the New and Old Tabs in this workbook.

Let me know your comments.
gowflow
Report.xlsm
0
 

Author Comment

by:kbay808
ID: 40219029
This is being used on my company’s computer and I’m unable to install new software.  We only have WinZip.  Unfortunately the scrip does not work.  All it did was make a copy of the zip file.  I attached a word document with all of the screen shots of the process when I clicked on the button that you created.  I also added notes.

Here is the change that I made to the code
str7ZIP = "C:\Program Files (x86)\WinZip\WINZIP32.exe"  

Additional info:
Name of the folder that contains the zip file and the report file: “Report”
Folder Path: "C:\%userprofile%\desktop\Report"
Screen-shots.docx
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40219054
ok noted

try this doubleclick manually on the zip file do you see the file Daily-Report.xlsx in there ? it should be named exactly like this as it is hardcoded in the macro.

I will try to look for a copy of WinZip32 and make some tests.

Pls let me know first answer to my question.
gowlfow
0
 

Author Comment

by:kbay808
ID: 40219069
The file name is "Daily-Report.xlsx".  I attached a screen shot too.
Screen-shot.JPG
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40219111
ok I tried downloading WINZIP64.exe but seems it does not work as need a registered version.

Is your version on your pc trial or registered ?
gowflow
0
 

Author Comment

by:kbay808
ID: 40219625
I have a registered verson of WINZIP32.EXE.
0
 

Author Comment

by:kbay808
ID: 40219630
The program location is "C:\Program Files (x86)\WinZip\WINZIP32.EXE"
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40220153
Yes I know problem is I cannot test it as do not have the pgm and to hv a registered version u need to buy it !!! :)
Will look for switches and advise you a solution just hang on with me.
gowflow
0
 

Author Comment

by:kbay808
ID: 40220302
Thanks
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40220471
Lets try the following:

Comment out this instruction (put a single quote in the first line so it look like this:

'strCMD = Chr(34) & str7ZIP & Chr(34) & " e -i!" & _
        Chr(34) & strFileName & Chr(34) & " -o" & _
        Chr(34) & strDestinationFolder & Chr(34) & " " & _
        Chr(34) & strZipFile & Chr(34) & " -y"

and add this line just after it

strCMD = Chr(34) & str7ZIP & Chr(34) & " -min -e " & _
        Chr(34) & strFileName & Chr(34) & " -o" & _
        Chr(34) & strDestinationFolder & Chr(34) & " " & _
        Chr(34) & strZipFile & Chr(34) & " -y"

save the macro and try running it
Let me know
gowflow
0
 

Author Comment

by:kbay808
ID: 40220694
Should it look like this?  I tried it without the ' before "'strCMD = Chr(34) & str7ZIP & Chr(34) & " e -i!" & _" too.  Both ways did nothing.

'Comment out this instruction (put a single quote in the first line so it look like this:

 'strCMD = Chr(34) & str7ZIP & Chr(34) & " e -i!" & _
         Chr(34) & strFileName & Chr(34) & " -o" & _
         Chr(34) & strDestinationFolder & Chr(34) & " " & _
         Chr(34) & strZipFile & Chr(34) & " -y"

 strCMD = Chr(34) & str7ZIP & Chr(34) & " -min -e " & _
         Chr(34) & strFileName & Chr(34) & " -o" & _
         Chr(34) & strDestinationFolder & Chr(34) & " " & _
         Chr(34) & strZipFile & Chr(34) & " -y"

End Sub

Open in new window

0
 
LVL 31

Expert Comment

by:gowflow
ID: 40221059
yes the first item should look green and the second one black
It is more difficult as do not have winzip to test.
gowflow
0
 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 40221061
ok lets try this one:
Include this code

strCMD = Chr(34) & str7ZIP & Chr(34) & " -min -e " & _
        Chr(34) & strZipFile & Chr(34) & _
        Chr(34) & strDestinationFolder & Chr(34)

Open in new window


instead of this code

strCMD = Chr(34) & str7ZIP & Chr(34) & " -min -e " & _
         Chr(34) & strFileName & Chr(34) & " -o" & _
         Chr(34) & strDestinationFolder & Chr(34) & " " & _
         Chr(34) & strZipFile & Chr(34) & " -y"

Open in new window


Let me know
gowflow
0
 

Author Closing Comment

by:kbay808
ID: 40232439
Thanks for keeping with it
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question