Solved

Follow Up - Option

Posted on 2015-02-03
13
85 Views
Last Modified: 2016-02-11
Hi Expert,

 I have one WB which download file from Web and format, I need little modification in formatting.
 Current Code Perform this steps
  Delete all files from this location "D:\AmiBroker Data\NSE\F&O"
  1 Download Zip From Start date to End Date
  2 Unzip that files.
  3 Find text "NIFTY" in column B which is mostly 3 match found in column B
  4 Register that three dates in column C which match to "NIFTY" and gave name instead of date as "I" "II" & "III"
  5 Apply above to all in column C as per date i.e. "I" "II" "III".
  6 Delete other rows which is not same as earlier match i.e. step 3 and step 4.
  7 Apply formula in cell P2
=IF(OR(A2="FUTSTK",A2="FUTIDX"),B2&" "&C2&","&TEXT(O2,"DD-MMM-YY")&","&F2&","&G2&","&H2&","&I2&","&K2&","&M2,IF(OR(A2="OPTIDX",A2="OPTSTK"),B2&" "&C2&" "&D2&" "&E2&","&TEXT(O2,"DD-MMM-YY")&","&F2&","&G2&","&H2&","&I2&","&K2&","&M2,""))
 & formula drop down till end.
  8 copy whole column P and past to new .txt file and delete original .csv file
  9 next file till end.

 Modification which I looking to
  Need  .text file from .csv file.
  After step 7
  1 Sort A to Z Column E
  2 Copy column P data only where in column E cell value Not = "xx" with header
  3 past these data to new .txt file and name as "O" & actual .csv file name.
  4 save to this location "D:\AmiBroker Data\NSE\F&O\Option\"
  5 Next .csv file.

 Note as new buttons created named Future it delete all files from this location "D:\AmiBroker Data\NSE\F&O\Option\"
 and follow the steps.

 Thanks
Incoporated-Download-File-V16.xlsm
0
Comment
Question by:itjockey
  • 8
  • 5
13 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40586966
ok here it is:

You will need to put this code in Command5 of sheet Main

Private Sub CommandButton5_Click()
bTrace = CheckBox21.Value
bAudit = CheckBox22.Value
gstDestinationFolder = Range("B7")
If bTesting Then gstDestinationFolder = ActiveWorkbook.Path & "\Temp\"
dStartDate = Range("E7")
dEndDate = Range("F7")
sHTTP = Sheets("Settings").Range("B6")

'---> Clear Previous Trace
If Not bDoit Then
    Sheets("Main").Range("A14:I" & Sheets("Main").Rows.Count).ClearContents
End If

'---> Start Download
DownloadFileFO OverwriteRecycle, CommandButton5.Caption

'---> Part of Unzipping files
UnzipAllFiles CommandButton5.Caption

'---> Formating Future
FormatingFO CommandButton5.Caption, gstDestinationFolder

If Not bDoit Then
    MsgBox ("Process Options Completed")
End If

End Sub

Open in new window



Then  in module aStartFO you should delete all the code that is there and replace it by the below:

Function FormatingFO(sType As String, sFolder As String) As String
On Error GoTo ErrHandler

Dim WB As Workbook
Dim WS As Worksheet
Dim WSAudit As Worksheet
Dim WSMain As Worksheet
Dim MaxRow As Long, MaxCol As Long, MaxRowA As Long, I As Long, J As Long
Dim lUns As Long, lRows As Long
Dim Rng As Range, cRow As Range
Dim sFile As String, sDirName As String, sDate As String, Res As String, sTextFile As String
Dim dDates
Dim colFiles As New Collection
Dim vFile As Variant
    
'---> Set Variables
Set WSAudit = Sheets("Audit")
MaxRowA = WSAudit.Range("A" & WSAudit.Rows.Count).End(xlUp).Row
If MaxRowA = 1 Then MaxRowA = MaxRowA + 1
Set WSMain = ActiveSheet

'---> Get the Recursive Files and folders
RecursiveDir colFiles, sFolder, "*.csv", True

For Each vFile In colFiles
           
    '---> Get full name
    sFile = Dir(vFile)
    sDirName = Mid(vFile, 1, InStrRev(vFile, "\"))
    
    '---> Update Trace
    If Not bTrace Then
        WSMain.Cells(14, "A").EntireRow.Insert
        WSMain.Cells(14, "A") = sType
        WSMain.Cells(14, "B") = sDirName
        WSMain.Cells(14, "C") = sFile
    End If
    
    '---> Disable Events
    With Application
        .EnableEvents = False
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
    
    '---> Open workbook and affect variables
    Set WB = Workbooks.Open(vFile)
    Set WS = WB.ActiveSheet
    MaxRow = WS.UsedRange.Rows.Count
    MaxCol = WS.UsedRange.Columns.Count
    
    '---> Find Criteria Dates
    WS.UsedRange.AutoFilter Field:=2, Criteria1:="NIFTY"
    WS.UsedRange.AutoFilter Field:=5, Criteria1:="XX"
    
    For I = 2 To MaxRow
        If WS.Range("A" & I).EntireRow.Hidden = False Then
            If sdates <> "" Then sdates = sdates & ";"
            sdates = sdates & DateValue(WS.Cells(I, "C"))
            J = J + 1
        End If
    Next I
            
    dDates = Split(sdates, ";")
    sdates = ""
    
    '---> Loop Thru all the Criteria
    For I = LBound(dDates) To UBound(dDates)
        If WS.AutoFilterMode = True Then WS.ShowAllData
        
        '---> Apply Filter
        WS.UsedRange.AutoFilter Field:=3, Criteria1:=">=" & dDates(I), Operator:=xlAnd, Criteria2:="<=" & dDates(I)
        
        '---> Set the Current Range
        On Error Resume Next
        Set Rng = WS.Range(WS.Range("A2"), WS.Cells(MaxRow, MaxCol)).EntireRow.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        
        '---> Change Date in Col C by coresponding letter
        Select Case I
            Case 0
                sDate = "I"
            Case 1
                sDate = "II"
            Case 2
                sDate = "III"
            Case Else
                sDate = "I" & Str(I)
        End Select
        
        lUns = 0
        For J = 2 To MaxRow
            If WS.Range("A" & J).EntireRow.Hidden = False Then
                WS.Range("C" & J) = sDate
                lUns = lUns + 1
            End If
        Next J
        
        '---> Register the record found in Audit
        If Not bAudit Then
            WSAudit.Cells(MaxRowA, "A") = Now
            WSAudit.Cells(MaxRowA, "B") = sFile
            WSAudit.Cells(MaxRowA, "C") = dDates(I) & " " & sDate
            WSAudit.Cells(MaxRowA, "D") = Rng.Address
            MaxRowA = MaxRowA + 1
        End If
        
        '---> Update Trace
        If Not bTrace Then
            WSMain.Cells(14, "A").EntireRow.Insert
            WSMain.Cells(14, "A") = sType
            WSMain.Cells(14, "B") = sDirName
            WSMain.Cells(14, "C") = sFile
            WSMain.Cells(14, "D") = sDate
            WSMain.Cells(14, "E") = lUns
            WSMain.Cells(14, "F") = "Formated"
        End If
        DoEvents
    Next I
    
    '---> Delete All rows that have a date in Col C
    For I = MaxRow To 2 Step -1
        If IsDate(WS.Cells(I, "C")) Then
            WS.Range("C" & I).EntireRow.Delete
            lRows = lRows + 1
        End If
    Next I
    
    '---> Get the New Maximum Rows
    MaxRow = WS.UsedRange.Rows.Count
    
    If Not bAudit Then
        WSAudit.Cells(MaxRowA, "A") = Now
        WSAudit.Cells(MaxRowA, "B") = sFile
        WSAudit.Cells(MaxRowA, "C") = Deleted & lRows & " Rows"
        MaxRowA = MaxRowA + 1
    End If
        

    '---> Update Trace Status
    If Not bTrace Then
        WSMain.Cells(14, "A").EntireRow.Insert
        WSMain.Cells(14, "A") = sType
        WSMain.Cells(14, "B") = sDirName
        WSMain.Cells(14, "C") = sFile
        WSMain.Cells(14, "E") = lRows
        WSMain.Cells(14, "F") = "Deleted"
    End If
    
    '---> Remove Filtering
    WS.ShowAllData
    WS.AutoFilterMode = False
    'WS.UsedRange.EntireColumn.AutoFit
    
    '---> Create Text File depending on Origin requested.
    Select Case sType
        Case "Future & Options"
            sTextFile = Mid(sFile, 1, Len(sFile) - 3) & "txt"
            Res = CreateTXT(WS, sDirName & sTextFile, MaxRow, sType)
        
        Case "Future"
            sTextFile = "F" & Mid(sFile, 1, Len(sFile) - 3) & "txt"
            Res = CreateTXT(WS, sDirName & sTextFile, MaxRow, sType)
    
        Case "Options"
            sTextFile = "O" & Mid(sFile, 1, Len(sFile) - 3) & "txt"
            Res = CreateTXT(WS, sDirName & sTextFile, MaxRow, sType)

    End Select
    
    If Res <> "" Then
        If Not bTrace Then
            WSMain.Cells(14, "A").EntireRow.Insert
            WSMain.Cells(14, "A") = sType
            WSMain.Cells(14, "B") = sDirName
            WSMain.Cells(14, "C") = sTextFile
            WSMain.Cells(14, "F") = "Created"
        End If
    Else
        If Not bTrace Then
            WSMain.Cells(14, "A").EntireRow.Insert
            WSMain.Cells(14, "A") = sType
            WSMain.Cells(14, "B") = sDirName
            WSMain.Cells(14, "C") = "No File Created"
            WSMain.Cells(14, "F") = "Error"
        End If
    End If
    
    '---> Save workbook
    WB.Close savechanges:=True
    
    '---> If TXT successful then delete CSV
    If Res <> "" Then
        Kill (sDirName & sFile)
        
        If Not bTrace Then
            WSMain.Cells(14, "A").EntireRow.Insert
            WSMain.Cells(14, "A") = sType
            WSMain.Cells(14, "B") = sDirName
            WSMain.Cells(14, "C") = sFile
            WSMain.Cells(14, "F") = "Deleted"
        End If
    End If
    
    '---> reset Variables
    Set WS = Nothing
    Set WB = Nothing
    Set Rng = Nothing
    lUns = 0
    lRows = 0
    'MaxRowM = MaxRowM + 1
    
    '---> Enable Events
    With Application
        .EnableEvents = True
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    
Next vFile

'---> fix Layout
If Not bAudit Then
    WSAudit.UsedRange.EntireColumn.AutoFit
End If

'---> Set Flag to complete successful and exit
FormatingFO = ""
Exit Function

ErrHandler:
MsgBox (Error(Err))
FormatingFO = Error(Err)
Resume
On Error GoTo 0

End Function


Function CreateTXT(WS As Worksheet, sWBName As String, MaxRow As Long, sType As String) As String
On Error GoTo ErrCreateTXT
Dim Rng As Range
Dim rRow As Range

'---> Title In P1
'"Ticker,Date,Open,High,Low,Close,Volume,OI"
WS.Range("P1") = "Ticker,Date,Open,High,Low,Close,Volume,OI"

'---> Formula in P2 and down
'P2 "=IF(OR(A2="FUTSTK",A2="FUTIDX"),B2&" "&C2&","&TEXT(O2,"DD-MMM-YY")&","&F2&","&G2&","&H2&","&I2&","&K2&","&M2,IF(OR(A2="OPTIDX",A2="OPTSTK"),B2&" "&C2&" "&D2&" "&E2&","&TEXT(O2,"DD-MMM-YY")&","&F2&","&G2&","&H2&","&I2&","&K2&","&M2,""))
WS.Range("P2:P" & MaxRow).Formula = "=IF(OR(A2=""FUTSTK"",A2=""FUTIDX""),B2&"" ""&C2&"",""&TEXT(O2,""DD-MMM-YY"")&"",""&F2&"",""&G2&"",""&H2&"",""&I2&"",""&K2&"",""&M2,IF(OR(A2=""OPTIDX"",A2=""OPTSTK""),B2&"" ""&C2&"" ""&D2&"" ""&E2&"",""&TEXT(O2,""DD-MMM-YY"")&"",""&F2&"",""&G2&"",""&H2&"",""&I2&"",""&K2&"",""&M2,""""))"

Select Case sType
        Case "Future & Options"
            '---> Sort as per Col P Ascending
            WS.Range("A1:P" & MaxRow).Sort key1:=WS.Range("P1"), order1:=xlAscending, Header:=xlYes, MatchCase:=no
            
            
        Case "Future"
            '---> Sort as per Col E Ascending
            WS.Range("A1:P" & MaxRow).Sort key1:=WS.Range("E1"), order1:=xlAscending, Header:=xlYes, MatchCase:=no
            WS.Range("A1:P" & MaxRow).AutoFilter Field:=5, Criteria1:="<>xx"
            Set Rng = WS.Range("A2:P" & MaxRow).EntireRow.SpecialCells(xlCellTypeVisible)
            Rng.Delete
            Set Rng = Nothing
            WS.ShowAllData
            MaxRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row
            
        Case "Options"
            '---> Sort as per Col E Ascending
            WS.Range("A1:P" & MaxRow).Sort key1:=WS.Range("E1"), order1:=xlAscending, Header:=xlYes, MatchCase:=no
            WS.Range("A1:P" & MaxRow).AutoFilter Field:=5, Criteria1:="=xx"
            Set Rng = WS.Range("A2:P" & MaxRow).EntireRow.SpecialCells(xlCellTypeVisible)
            Rng.Delete
            Set Rng = Nothing
            WS.ShowAllData
            MaxRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row

            
End Select

'---> Copy Col P to A and set Rng the new Range
WS.Range("P2:P" & MaxRow).Copy
WS.Range("P2").PasteSpecial xlPasteValues

WS.Range("A:O").EntireColumn.Delete
Set Rng = WS.Range("A1:A" & MaxRow)

'---> Crete the TXT File
Open sWBName For Output As #1
For Each rRow In Rng
    Print #1, rRow.Value
Next rRow

Close #1

'WS.SaveAs Filename:=sWBName, FileFormat:=xlCSV

CreateTXT = sWBName

Set WS = Nothing
Set Rng = Nothing

Exit Function

ErrCreateTXT:
CreateTXT = ""
Err = 0
Resume
End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DownloadFileFO
' This downloads a file from a URL to a local filename.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function DownloadFileFO(Overwrite As DownloadFileDisposition, sType As String) 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
    If Not bDoit Then
        WSMain.Range("A14:I" & WSMain.Rows.Count).ClearContents
    End If
    MaxRowM = 14
    
    
    ErrorText = vbNullString

    strSavePath = gstDestinationFolder
    
    If Not bTrace Then
        WSMain.Cells(14, "A").EntireRow.Insert
        WSMain.Cells(14, "A") = sType
        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

    'iYear = 2014
    'Do
    '    On Error Resume Next
    '    iYear = InputBox("Please Enter Year to Process", "Year Input", Year(Now))
    'Loop Until Err = 0 And iYear >= 1900
    
    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
        'http://www.nseindia.com/content/historical/DERIVATIVES/2015/JAN/fo02JAN2015bhav.csv.zip
        strMonth = UCase(Format(datWorkDate, "MMM"))  ' upper case name of the month, like JAN
        iYear = Year(datWorkDate)
        strFileName = "fo" & Right("0" & Day(datWorkDate), 2) & strMonth & iYear & "bhav.csv.zip"    ' e.g. fo01JAN2013bhav.csv.zip
        'strFilePath = "http://www.nseindia.com/content/historical/DERIVATIVES/" & iYear & "/" & strMonth & "/" & strFileName
        strFilePath = sHTTP & iYear & "/" & strMonth & "/" & 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
                        DownloadFileFO = 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
                        DownloadFileFO = False
                        Exit Function
                    End If

                Case DoNotOverwrite
                    DownloadFileFO = 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."
                        DownloadFileFO = False
                        Exit Function
                    End If
                    B = RecycleFileOrFolder(DestinationFileName)
                    If B = False Then
                        ErrorText = "Error Recycling file '" & DestinationFileName & "." & vbCrLf & Err.Description
                        DownloadFileFO = 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
                    DownloadFileFO = True
                    sLastDownloadeddate = datWorkDate
                    
                    '---> Update Trace
                    If Not bTrace Then
                        WSMain.Cells(14, "A").EntireRow.Insert
                        WSMain.Cells(14, "A") = sType
                        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."
                    DownloadFileFO = False
                Case Is = -2146697211
                    ErrorText = "Domain not found."
                    DownloadFileFO = False
                Case Is = -2147467260
                    ErrorText = "Transfer aborted."
                    DownloadFileFO = False
                Case Else
                    ErrorText = "Buffer length invalid or not enough memory."
                    DownloadFileFO = 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
    Select Case sType
        Case "Future & Options"
            Range("E10").Value = DateAdd("d", 1, sLastDownloadeddate)
            Range("F10").Formula = "=Today()"
        
        Case "Future"
            Range("E6").Value = DateAdd("d", 1, sLastDownloadeddate)
            Range("F6").Formula = "=Today()"
            
        Case "Options"
            Range("E7").Value = DateAdd("d", 1, sLastDownloadeddate)
            Range("F7").Formula = "=Today()"
        
        
    End Select
Else
    Select Case sType
        Case "Future & Options"
            Range("F10").Formula = "=Today()"
        
        Case "Future"
            Range("F6").Formula = "=Today()"
            
        Case "Options"
            Range("F7").Formula = "=Today()"

    End Select
End If
End Function

Open in new window


SAVE and Exit this new workbook and try it.
gowlfow
0
 
LVL 8

Author Comment

by:itjockey
ID: 40587033
Sir.Gowflow,

There is no Command button 5 Sub in sheet main so I had added your code at the end.
seems to be perfect but location is different.Different locationLet me what to change?

Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 40587038
LocationThanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40587076
No !!!

open the workbook
choose developer menu click on Design to de activate macros
click on the button options and look in the top left corner cell what commandbutton you get ?

gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 40587129
Sorry My Mistake To Elaborate.
There is command button 5 in sheet main but in VBA Window there is not Sub Command Button 5. In past question you many time said delete code in command button x and put this in between. so I assumed that in Version 16 WB there is command button 5  in VBA code window but I dint find so I added whole code which you had posted in your earliest comment.

Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 40587141
This is the whole code for sheet "Main"
Private Sub CheckBox21_Click()
If CheckBox21.Value = True Then
    CheckBox21.Caption = "Skip Trace"
    bTrace = True
Else
    CheckBox21.Caption = "Show Trace"
    bTrace = False
End If
End Sub

Private Sub CheckBox22_Click()
If CheckBox22.Value = True Then
    CheckBox22.Caption = "Skip Audit"
    bAudit = True
Else
    CheckBox22.Caption = "Update Audit"
    bAudit = False
End If
End Sub

Private Sub CommandButton1_Click()
bTrace = CheckBox21.Value
bAudit = CheckBox22.Value
gstDestinationFolder = Range("B3")
If bTesting Then gstDestinationFolder = ActiveWorkbook.Path & "\Temp\"
dStartDate = Range("E3")
dEndDate = Range("F3")
sHTTP = Sheets("Settings").Range("B2")
StartDel
End Sub

Private Sub CommandButton10_Click()
'---> Clean Previous Trace
Range("A14:I" & Rows.Count).ClearContents
End Sub



Private Sub CommandButton11_Click()
Sheets("Audit").Range("2:" & Rows.Count).EntireRow.Delete
Sheets("Audit").UsedRange.EntireColumn.AutoFit
End Sub

Private Sub CommandButton15_Click()
bDoit = True

Sheets("Main").Range("A14:I" & Sheets("Main").Rows.Count).ClearContents

CommandButton1_Click
CommandButton2_Click
CommandButton3_Click
'CommandButton4_Click
'CommandButton5_Click
CommandButton6_Click
CommandButton7_Click

MsgBox "Do it all completed successfully !", vbInformation, "Do it All"

End Sub

Private Sub CommandButton2_Click()
Dim sResults As String

bTrace = CheckBox21.Value
bAudit = CheckBox22.Value
gstDestinationFolder = Range("B4")
If bTesting Then gstDestinationFolder = ActiveWorkbook.Path & "\Temp\"
dStartDate = Range("E4")
dEndDate = Range("F4")
sHTTP = Sheets("Settings").Range("B3")

'---> Part of Downloading Zipped Files
Start

'---> Part of Unzipping files
UnzipAllFiles CommandButton2.Caption

'---> Part of Deleting Rows
sResults = DelSpecRowCSV("Equ", gstDestinationFolder)
If sResults = "" Then
    'MsgBox "All Instances encountered in files in directory " & gstDestinationFolder & " have been successfully removed and archived. Please check sheet Audit for trace.", vbExclamation, "Delete Rows"
Else
    MsgBox "Delete Rows procedure encountered error in file " & sResults & " and was halted. Pls check and try again after correcting the error.", vbCritical, "Delete Rows"
End If

If Not bDoit Then
    MsgBox ("Process Equity Completed")
End If

End Sub

Private Sub CommandButton3_Click()
bTrace = CheckBox21.Value
bAudit = CheckBox22.Value
gstDestinationFolder = Range("B5")
If bTesting Then gstDestinationFolder = ActiveWorkbook.Path & "\Temp\"
dStartDate = Range("E5")
dEndDate = Range("F5")
sHTTP = Sheets("Settings").Range("B4")

ImportIndex
End Sub


Private Sub CommandButton4_Click()
bTrace = CheckBox21.Value
bAudit = CheckBox22.Value
gstDestinationFolder = Range("B6")
If bTesting Then gstDestinationFolder = ActiveWorkbook.Path & "\Temp\"
dStartDate = Range("E6")
dEndDate = Range("F6")
sHTTP = Sheets("Settings").Range("B5")

'---> Clear Previous Trace
If Not bDoit Then
    Sheets("Main").Range("A14:I" & Sheets("Main").Rows.Count).ClearContents
End If

'---> Start Download
DownloadFileFO OverwriteRecycle, CommandButton4.Caption

'---> Part of Unzipping files
UnzipAllFiles CommandButton4.Caption

'---> Formating Future
FormatingFO CommandButton4.Caption, gstDestinationFolder

If Not bDoit Then
    MsgBox ("Process Future Completed")
End If
End Sub

Private Sub 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
DownloadFileDeliveryBSE (OverwriteRecycle)

'---> Part of Unzipping files
UnzipAllFiles CommandButton6.Caption

'---> Format
FormatingDeliveryBSE

If Not bDoit Then
    MsgBox ("BSE Del Done")
End If
End Sub

Private Sub CommandButton7_Click()
bTrace = CheckBox21.Value
bAudit = CheckBox22.Value
gstDestinationFolder = Range("B9")
If bTesting Then gstDestinationFolder = ActiveWorkbook.Path & "\Temp\"
dStartDate = Range("E9")
dEndDate = Range("F9")
sHTTP = Sheets("Settings").Range("B8")

'---> Start Download
DownloadFileEquityBSE (OverwriteRecycle)

'---> Part of Unzipping files
UnzipAllFiles CommandButton7.Caption

'---> Formating Equity BSE
FormatingEquityBSE CommandButton7.Caption, gstDestinationFolder

If Not bDoit Then
    MsgBox ("Equity BSE Done")
End If
End Sub

Private Sub CommandButton8_Click()
bTrace = CheckBox21.Value
bAudit = CheckBox22.Value
gstDestinationFolder = Range("B10")
If bTesting Then gstDestinationFolder = ActiveWorkbook.Path & "\Temp\"
dStartDate = Range("E10")
dEndDate = Range("F10")
sHTTP = Sheets("Settings").Range("B9")

'---> Clear Previous Trace
If Not bDoit Then
    Sheets("Main").Range("A14:I" & Sheets("Main").Rows.Count).ClearContents
End If

'---> Start Download
DownloadFileFO OverwriteRecycle, CommandButton8.Caption

'---> Part of Unzipping files
UnzipAllFiles CommandButton8.Caption

'---> Formating FO
FormatingFO CommandButton8.Caption, gstDestinationFolder

If Not bDoit Then
    MsgBox ("Process Future & Options Completed")
End If

End Sub
Private Sub CommandButton5_Click()
bTrace = CheckBox21.Value
bAudit = CheckBox22.Value
gstDestinationFolder = Range("B7")
If bTesting Then gstDestinationFolder = ActiveWorkbook.Path & "\Temp\"
dStartDate = Range("E7")
dEndDate = Range("F7")
sHTTP = Sheets("Settings").Range("B6")

'---> Clear Previous Trace
If Not bDoit Then
    Sheets("Main").Range("A14:I" & Sheets("Main").Rows.Count).ClearContents
End If

'---> Start Download
DownloadFileFO OverwriteRecycle, CommandButton5.Caption

'---> Part of Unzipping files
UnzipAllFiles CommandButton5.Caption

'---> Formating Future
FormatingFO CommandButton5.Caption, gstDestinationFolder

If Not bDoit Then
    MsgBox ("Process Options Completed")
End If

End Sub

Open in new window

Thanks
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40587184
ok I was trying to avoid posting the whole workbook to preserve your biz from being copied.

Anyhow here is the file.
gowflow
Incoporated-Download-File-V17.xlsm
0
 
LVL 8

Author Comment

by:itjockey
ID: 40587198
preserve your biz from being copied

I learned new words in your many solution which enhance my vocabulary but this I dint get it?

Thanks
0
 
LVL 8

Author Closing Comment

by:itjockey
ID: 40587206
Perfect - May I ask probably last question on this series ?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40587267
yes sure while we are at it.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40587268
preserve your business = not hv this file in your competition hand as they will outperform you !! as this is I guess a very good tool for stock market traders right ??? As you can surely know that these question are available on google search to almost anyone !!

gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 40587290
hmmmm I will make it private one .....buy upgrading my subscription.

Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 40587304
here it is

Thanks
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

746 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

12 Experts available now in Live!

Get 1:1 Help Now