?
Solved

Downloading Index

Posted on 2015-01-02
10
Medium Priority
?
160 Views
Last Modified: 2015-01-03
Hi Experts,

I have WB which download data from Web. I just have to put start date  and end date & it will download files for that time range & save to active WB path. I need little modification.
1 Save Path changes to "D:\AmiBroker Data\NSE\Index\"
2 Before downloading it clears all file from above location.
3 start downloading
4 print last date of available  file in Start date.
5 End

See attached file.

Thanks
NSE-Index-Importer.xlsm
0
Comment
Question by:Naresh Patel
[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
  • 6
  • 2
  • 2
10 Comments
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40528529
Apology I won't be able to reply...as here 4:00 AM. Feeling sleepy.
0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 1300 total points
ID: 40528686
I wish you had not password protected the VBA code, as I had to remove the protection prior to seeing what needed to be changed.

The code can really only be tested by you, given its dependence on certain data being available, but I believe the required changes are in the two subs below:
Sub DeleteFiles(flPath As String)       'Deletes all files from this folder
Dim fl As String
fl = Dir(flPath & "*.*")
Do Until fl = ""
    Kill flPath & fl
    fl = Dir
Loop
End Sub

Sub Macro1()
Dim flPath As String
Dim rgStart As Range
Dim WkBk As Excel.Workbook
Dim lastDate As Date
txtFileName As String

Application.ScreenUpdating = False
flPath = "D:\AmiBroker Data\NSE\Index\"
DeleteFiles flPath

Set rgStart = Range("C2")
startDate = Range("C2").Value
stopdate = Range("C3").Value

For xx = startDate To stopdate

    If HttpExists("http://nseindia.com/content/indices/ind_close_all_" & Format(xx, "ddmmyyyy") & ".csv") Then
        txtFileName = Format(xx, "ddmmyyyy")
        lastDate = xx
        
        Set WkBk = Workbooks.Add
    
        With ActiveSheet.QueryTables.Add(Connection:="URL;http://nseindia.com/content/indices/ind_close_all_" & Format(xx, "ddmmyyyy") & ".csv", Destination:=Range("$A$1"))
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlEntirePage
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
        Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
            Semicolon:=False, Comma:=True, Space:=False, Other:=False
        Application.DisplayAlerts = False
        WkBk.SaveAs Filename:=flPath & "\" & txtFileName & ".csv", FileFormat:=xlCSV, CreateBackup:=False
        WkBk.Close (False)
        Application.DisplayAlerts = True
    End If

Next xx

rgStart.Value = lastDate
Application.ScreenUpdating = True

MsgBox "Done"

End Sub

Open in new window

NSE-Index-ImporterQ28589869.xlsm
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40528799
Sir.Byundt,i got this error meassage.Thanks
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 81

Assisted Solution

by:byundt
byundt earned 1300 total points
ID: 40528816
That statement is missing the "Dim" keyword. I should have caught that, even without being able to test.
Sub Macro1()
Dim flPath As String
Dim rgStart As Range
Dim WkBk As Excel.Workbook
Dim lastDate As Date
Dim txtFileName As String

Open in new window

0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40528991
Sir.Byundt,

Downloading files - Perfect. But when I clicked button it wont clear (Delete) Files from directory. Apology for delay in reply & please one favor - as start date print at the end of process is = last file available date, just need to add one day i.e. last date of file available = 1 jan 2015 then print date is +1 day which is 2 jan 2015.
I required in this manner  
1 when I clicked it clears all files from "D:\AmiBroker Data\NSE\Index" this directory.
2 Download files from start date to End date.
3 Print start date as last available file date plus one day.
4 End


Thank You
0
 
LVL 31

Accepted Solution

by:
gowflow earned 700 total points
ID: 40529024
I had to use byundt file as yours was pass protected.

Made necessary modification in sub delete files and in Macro as per your request.
gowflow
NSE-Index-Importer-V01.xlsm
0
 
LVL 8

Author Closing Comment

by:Naresh Patel
ID: 40529052
Awesome
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40529053
May I ask Follow Up?
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40529080
yes but pls no protected workbook
gowflow
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40529093
Here it is.

Thanks
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

777 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