Does the current path have more than one zip file?more than one csv file?
How do you determine which zip file (and csv file) you will process?(is it the
latest file?)
You can use the FileSystem object Set fso =
CreateObject("Scripting.FileSystemObject")
to cycle through the folder, looking for the latest zip file:
Sub Find_Zip()
Dim fso, fldr, file, f, ext, zFile, zDate
Dim dVal
Set fso = CreateObject("Scripting.FileSystemObject")
zDate = DateValue("1/1/1960")
Set fldr = fso.getfolder(ActiveWorkbook.Path)
For Each file In fldr.Files
ext = fso.getextensionname(file)
If (UCase(ext) = "XLSX") Then
If (file.datelastmodified > zDate) Then
zDate = file.datelastmodified
zFile = file.Name
End If
End If
Next file
Debug.Print zDate & " : " & zFile
End Sub
Paul-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
On Saturday, January 7, 2017 2:11 PM, 'vijay v.j.r' via MS EXCEL AND VBA
MACROS <excel-macros@googlegroups.com> wrote:
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.
--
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.
