Workbooks.Open(URL)
 

This macro dies at the Workbooks.Open(URL) statement, when I run it on
Windows XP at home, but works fine on my Windows 2000 machine at the Bank.
Any ideas? It was working well on XP until last week. (I am on an
active broadband T1 line to the Internet at the execution of this macro.)

Sub get_cm_bhavcopy_From_nse_website()

Dim CTR1 As Date
Dim START_DT As Date
Dim END_DT As Date
Dim URL As String
Dim ROWCOUNT As Integer
Dim BHAVCOPY As Workbook
Dim ERRBOOK As Workbook
Dim ERRSHEET As Worksheet
Dim FILENAME As String
Dim csvFilename As String
Dim xlsFilename As String

Set ERRBOOK = Workbooks.Open("C:\INVESTMENTS\ERRORS.XLS")
Set ERRSHEET = ERRBOOK.Worksheets(1)
ERRSHEET.Cells(1, 1).Value = "NO BHAVCOPY FOR THESE DAYS"
ROWCOUNT = 2


START_DT = InputBox("START FROM DATE:")
END_DT = InputBox("END ON DATE:")

For CTR1 = START_DT To END_DT
If (Weekday(CTR1) = 2 And Weekday(CTR1) <= 6) Then
URL = CStr("http://www.nseindia.com/content/historical/EQUITIES/" &
CStr(Year(CTR1)) & "/" & UCase(CStr(Format(CTR1, "MMM"))) & "/cm" &
CStr(Format(CTR1, "D")) & UCase(CStr(Format(CTR1, "MMM"))) &
CStr(Format(CTR1, "YYYY")) & "bhav.csv")
Set BHAVCOPY = Workbooks.Open(URL)
On Error GoTo ErrHandler
csvFilename = CStr("C:\INVESTMENTS\CSV\cm" & CStr(Format(CTR1, "D")) &
UCase(CStr(Format(CTR1, "MMM"))) & CStr(Format(CTR1, "YYYY")) & "bhav.csv")
xlsFilename = CStr("C:\INVESTMENTS\XLS\cm" & CStr(Format(CTR1, "D")) &
UCase(CStr(Format(CTR1, "MMM"))) & CStr(Format(CTR1, "YYYY")) & "bhav.xls")
With BHAVCOPY
..SaveAs FILENAME:=CStr("C:\INVESTMENTS\CSV\cm" & CStr(Format(CTR1, "D")) &
UCase(CStr(Format(CTR1, "MMM"))) & CStr(Format(CTR1, "YYYY")) & "bhav.csv"), _
FileFormat:=xlCSV
..Close savechanges:=False
End With
Workbooks.OpenText FILENAME:=csvFilename, DataType:=xlDelimited, tab:=False,
semicolon:=False, comma:=True, Space:=False, other:=False
With Workbooks(3)
..SaveAs FILENAME:=xlsFilename, FileFormat:=xlWorkbookNormal
..Close savechanges:=False
End With
End If
NEXTDAY:
Next CTR1
With ERRBOOK
..Close savechanges:=True
End With
Workbooks.Close
Exit Sub
ErrHandler:
If Err.Number = 1004 Then
ROWCOUNT = ROWCOUNT + 1
ERRSHEET.Cells(ROWCOUNT, 1).Value = URL
'MsgBox "CANNOT FIND " & URL
End If
Resume Next
Workbooks.Close
End Sub



I'll buy cookies for the person who answers this post :-) TIA!!

Regards,
BR

--
Manager, International Private Banking, International Banking Group, ICICI
Bank
East Wing 8th floor South, ICICI Towers, Bandra Kurla Complex, Mumbai India
400051