Below VBA CODE working properly to extract data from zip file if my zip
file presents in C:\Users\max\Downloads..
i.e if i import data , it unzip the zipped file and copy the csv data
inside zip file and paste it in the workbook which i need, but the problem
is "fo26DEC2016bhav.csv.zip" zip name will change daily ,and i need to
download the data and update it , in my workbook worksheet, so my vba code
has to be changed as per it can daily update data irrespective of zip name
and worksheet name in my workbook, so kindly help me to correct
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 = "fo26DEC2016bhav.csv"
str7ZIP = "C:\Program Files (x86)\7-Zip\7z.exe"
strDestinationFolder = ActiveWorkbook.Path
strZipFile = strDestinationFolder & "\fo26DEC2016bhav.csv.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, "fo26DEC2016bhav.csv"
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, "fo26DEC2016bhav.csv"
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
below is zip file and have upload my excel file
https://www.nseindia.com/content/historical/DERIVATIVES/2016/DEC/fo26DEC2016bhav.csv.zip
my excel file
https://drive.google.com/file/d/0BxNxUBpCACzNN01mSGM0VlpQN1k
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
