Solved

Download From Web

Posted on 2015-01-04
16
149 Views
Last Modified: 2015-01-06
Hi Expert,

I have one link which leads me to download zip file from server for particular date I need one excel WB in which I put start date and end date & Code it self downloads files from that date rang. please help me out.
 this is the link29 Dec2014.

My Saving directory is "D:\AmiBroker Data\BSE\Eq\"

Thanks
0
Comment
Question by:itjockey
  • 8
  • 7
16 Comments
 
LVL 31

Expert Comment

by:Paul Sauvé
Comment Utility
what is it exactly you need help with???

The target file in your link is EQ291214_CSV.ZIP.

You can save this file to your pc by clicking on the link, then Save as... and navigate to the folder D:\AmiBroker Data\BSE\Eq\.

Now you have to extract the EQ291214_CSV from the zip file...

Open MS Excel and import the document from the Data tab -> From Text option and navigate to D:\AmiBroker Data\BSE\Eq\ to find the extracted downloaded file, i.e. EQ291214.CSV.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
I can see that this one you have nothing done for it and want to do from scratch ?
Presumen this is Equity BSE ?
gowflow
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
Yes Sir.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok is this what you want ?

This sub should do the trick

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DownloadFileEquityBSE
' This downloads a file from a URL to a local filename.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function DownloadFileEquityBSE(Overwrite As DownloadFileDisposition) As Boolean
    Dim WSMain As Worksheet
   
    Dim DestinationFileName As String
    Dim Disp As DownloadFileDisposition
    Dim Res As VbMsgBoxResult
    Dim ErrorText As String
    Dim B As Boolean
    Dim S As String
    Dim L As Long
    Dim MaxRowM As Long
    
    'Dim strStartDate As String
    'Dim strEndDate As String
    Dim datLastDate As Date
    Dim datWorkDate As Date
    Dim iYear As Integer
    Dim strMonth As String
    Dim strDay As String
    Dim strFileName As String
    Dim strFilePath As String
    Dim strSavePath As String
    Dim sLastDownloadeddate As String
    Dim oFso As Object

    
    Set WSMain = ActiveSheet

    '---> Clean Previous Trace
    WSMain.Range("A14:I" & WSMain.Rows.Count).ClearContents
    MaxRowM = 14
    
    
    ErrorText = vbNullString

    strSavePath = gstDestinationFolder
    
    If Not bTrace Then
        WSMain.Cells(14, "A").EntireRow.Insert
        WSMain.Cells(14, "A") = "Equity BSE"
        WSMain.Cells(14, "B") = strSavePath
        WSMain.Cells(14, "C") = "*.*"
        WSMain.Cells(14, "F") = "Deleting"
        'MaxRowM = MaxRowM + 1
    End If
    
    '---> Delete All files in Directory prior to proceeding
    DeleteFiles strSavePath
    
    Set oFso = CreateObject("Scripting.FileSystemObject")

    With oFso
        If Not .FolderExists(strSavePath) Then
            MakeMultiStepDirectory strSavePath
        End If
    End With

    
    If dEndDate = vbEmpty Or dStartDate = vbEmpty Then
        MsgBox "Missing either Strat Date or End Date procedure will exit.", vbCritical
        Exit Function
    End If
    
    datLastDate = DateValue(dEndDate)      'DateSerial(iYear, 4, 9)
    datWorkDate = DateValue(dStartDate)    'DateSerial(iYear, 1, 1)
    
    
    Do
        ' EG http://www.bseindia.com/download/BhavCopy/Equity/EQ291214_CSV.ZIP
        strFileName = "EQ" & Format(datWorkDate, "ddmmyy") & "_CSV.ZIP"    ' e.g. EQ291214_CSV.ZIP
        strFilePath = sHTTP & strFileName
        
        If Right(strSavePath, 1) <> "\" Then strSavePath = strSavePath & "\"

        DestinationFileName = strSavePath & strFileName

        If Dir(DestinationFileName, vbNormal) <> vbNullString Then
            Select Case Overwrite
                Case OverwriteKill
                    On Error Resume Next
                    Err.Clear
                    Kill DestinationFileName
                    If Err.Number <> 0 Then
                        ErrorText = "Error Killing file '" & DestinationFileName & "'." & vbCrLf & Err.Description
                        DownloadFileEquityBSE = False
                    End If

                Case OverwriteRecycle
                    On Error Resume Next
                    Err.Clear
                    B = RecycleFileOrFolder(DestinationFileName)
                    If B = False Then
                        ErrorText = "Error Recycleing file '" & DestinationFileName & "." & vbCrLf & Err.Description
                        DownloadFileEquityBSE = False
                        Exit Function
                    End If

                Case DoNotOverwrite
                    DownloadFileEquityBSE = False
                    ErrorText = "File '" & DestinationFileName & "' exists and disposition is set to DoNotOverwrite."
                    Exit Function

                    'Case PromptUser
                Case Else
                    S = "The destination file '" & DestinationFileName & "' already exists." & vbCrLf & _
                        "Do you want to overwrite the existing file?"
                    Res = MsgBox(S, vbYesNo, "Download File")
                    If Res = vbNo Then
                        ErrorText = "User selected not to overwrite existing file."
                        DownloadFileEquityBSE = False
                        Exit Function
                    End If
                    B = RecycleFileOrFolder(DestinationFileName)
                    If B = False Then
                        ErrorText = "Error Recycling file '" & DestinationFileName & "." & vbCrLf & Err.Description
                        DownloadFileEquityBSE = False
                        Exit Function
                    End If
            End Select
        End If
        Debug.Print strFilePath & " validity: " & GetURLStatus(strFilePath)
        If GetURLStatus(strFilePath) = "200 - OK" Then
            L = DeleteUrlCacheEntry(strFilePath)
            L = URLDownloadToFile(0&, strFilePath, DestinationFileName, 0&, 0&)
            Select Case L
                Case Is = 0
                    DownloadFileEquityBSE = True
                    sLastDownloadeddate = datWorkDate
                    
                    '---> Update Trace
                    If Not bTrace Then
                        WSMain.Cells(14, "A").EntireRow.Insert
                        WSMain.Cells(14, "A") = "Equity BSE"
                        WSMain.Cells(14, "B") = strSavePath
                        WSMain.Cells(14, "C") = strFileName
                        WSMain.Cells(14, "F") = "Created"
                        'MaxRowM = MaxRowM + 1
                    End If
                Case Is = -2146697210
                    ErrorText = "File not found."
                    DownloadFileEquityBSE = False
                Case Is = -2146697211
                    ErrorText = "Domain not found."
                    DownloadFileEquityBSE = False
                Case Is = -2147467260
                    ErrorText = "Transfer aborted."
                    DownloadFileEquityBSE = False
                Case Else
                    ErrorText = "Buffer length invalid or not enough memory."
                    DownloadFileEquityBSE = False
            End Select
        End If
        datWorkDate = DateAdd("d", 1, datWorkDate)
    Loop Until datWorkDate > datLastDate

'---> Set New Start Date = last successful date + Clear End Date
If sLastDownloadeddate <> "" Then
    Range("E8").Value = DateAdd("d", 1, sLastDownloadeddate)
    Range("F8").Formula = "=Today()"
    'MsgBox "Last Successful downloaded file was on " & sLastDownloadeddate & Chr(10) & "The next set date for next run of the routine will be set at " & Range("G3").Value
Else
    Range("F8").Formula = "=Today()"
    'MsgBox "No Files where found in this Interval try later."
End If

End Function

Open in new window


gowflow
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
Sir.Gowflow,

I had attached above code to new WB & created button but I cant see macro while I am assigning to that button.

See attached



Thanks
BSEeq-V01.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok sorry forgot to give you steps to implement in your production workbook.

1) Make a copy of your last production workbook the new one that comprises all options and give it a new name.
2) Goto VBA ALT F11
3) Create a new Module and from the View Menu select Property Window
4) click on the new Module created it should be Module1 change its name to aStartEquityBSE
5) doubleclick on this new module and paste the code I posted in my last comment in this new module.
6) Save the workbook.
7) We need now to add the call to this procedure. doubleclick on the sheet Main in the left pane
8) Display the code for CommandButton6
9) Delete all the code that is between CommandButton6 and End Sub.
10) Paste the below in there just after CommandButton6_click

bTrace = CheckBox21.Value
bAudit = CheckBox22.Value
gstDestinationFolder = Range("B8")
If bTesting Then gstDestinationFolder = ActiveWorkbook.Path & "\Temp\"
dStartDate = Range("E8")
dEndDate = Range("F8")
sHTTP = Sheets("Settings").Range("B7")

'---> Start Download
DownloadFileEquityBSE (OverwriteRecycle)

MsgBox ("Equity BSE Done")

Open in new window


11) You will also need to replace a Sub. goto VBA doubleclick on the Module aStartDelivery locate the Sub DeleteFiles and delete it from the first line till End Sub.
12) Paste the below new Sub after any End Sub in this module.

Sub DeleteFiles(sStrPath As String)
If sStrPath = "" Or sStrPath <> gstDestinationFolder Then
    MsgBox "Warning !!!! file path to delete is: [" & sStrPath & "] Files will not be deleted from this location.", vbCritical
    Exit Sub
Else
    On Error Resume Next
    Kill sStrPath & "*.csv"
    Kill sStrPath & "*.zip"
    On Error GoTo 0
End If

End Sub

Open in new window

13) Save and Exit the workbook.
14) open this new version and try the Equity BSE.

Let me know.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Did you try to incorporate the code ?
gowflow
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
yes I did it Sir...sorry for delay.ErrorSee attached.

Thanks
Incoporated-Download-File-V02.xlsm
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Try this. Didn't want to attach your full file felt you wanted to protect your achievement. Recommend for the future to proceed like this one. When you get an error you specify what is the error and what line.
gowflow
Incoporated-Download-File-V02.xlsm
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
This time no error but what mi looking for not done.Done messageBut in actual directory there is nothing.Thanks
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
ok here it is.
jus needed to remove the file name from the link in settings.
gowflow
Incoporated-Download-File-V02.xlsm
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
I will revert you back as soon as possible ...right now out of the desk.

Thank you Sir.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok
0
 
LVL 8

Author Closing Comment

by:itjockey
Comment Utility
Perfect - May I ask follow up? it is downloading Zip files need to add zip procedure.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
yes pls go ahead
gowflow
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
here it is.

Thanks
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now