|
Workbooks.Open(URL)
It could have to do with settings regional settings. What has changed.
Your dates may not be interpreted correctly. I would put in code like this: Msgbox URL Set BHAVCOPY = Workbooks.Open(URL) and see if the URL is being formed properly. -- Regards, Tom Ogilvy "BHARATH RAJAMANI" wrote in message ... 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 |
Workbooks.Open(URL)
In the Debug watch window, URL =
"http://www.nseindia.com/content/historical/EQUITIES/2004/SEP/cm22SEP2004bhav.csv" This string works fine if I copy-paste onto the browser address bar. The only alterations I have made to regional settings are in Currency display (USD - INR). Any ideas? Should I use the Workbooks.OpenText method instead of Workbooks.Open? TIA!! Regards, BR "Tom Ogilvy" wrote: It could have to do with settings regional settings. What has changed. Your dates may not be interpreted correctly. I would put in code like this: Msgbox URL Set BHAVCOPY = Workbooks.Open(URL) and see if the URL is being formed properly. -- Regards, Tom Ogilvy "BHARATH RAJAMANI" wrote in message ... 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 |
Workbooks.Open(URL)
You can try whatever you want. I don't see where that would add/change
anything. In any event, both Open and OpenText worked for me with your URL. If it was working and now it doesn't and nothing has changed, then it is a mystery. Perhaps clean out your temp folder. -- Regards, Tom Ogilvy "BHARATH RAJAMANI" wrote in message ... In the Debug watch window, URL = "http://www.nseindia.com/content/historical/EQUITIES/2004/SEP/cm22SEP2004bha v.csv" This string works fine if I copy-paste onto the browser address bar. The only alterations I have made to regional settings are in Currency display (USD - INR). Any ideas? Should I use the Workbooks.OpenText method instead of Workbooks.Open? TIA!! Regards, BR "Tom Ogilvy" wrote: It could have to do with settings regional settings. What has changed. Your dates may not be interpreted correctly. I would put in code like this: Msgbox URL Set BHAVCOPY = Workbooks.Open(URL) and see if the URL is being formed properly. -- Regards, Tom Ogilvy "BHARATH RAJAMANI" wrote in message ... 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 |
Workbooks.Open(URL)
Another thought. Do you have a download limit set by your ISP? Perhaps
they monitored a large volume of downloads and blocked the site (although, it it works in your browser, that would seem less likely). -- Regards, Tom Ogilvy "BHARATH RAJAMANI" wrote in message ... In the Debug watch window, URL = "http://www.nseindia.com/content/historical/EQUITIES/2004/SEP/cm22SEP2004bha v.csv" This string works fine if I copy-paste onto the browser address bar. The only alterations I have made to regional settings are in Currency display (USD - INR). Any ideas? Should I use the Workbooks.OpenText method instead of Workbooks.Open? TIA!! Regards, BR "Tom Ogilvy" wrote: It could have to do with settings regional settings. What has changed. Your dates may not be interpreted correctly. I would put in code like this: Msgbox URL Set BHAVCOPY = Workbooks.Open(URL) and see if the URL is being formed properly. -- Regards, Tom Ogilvy "BHARATH RAJAMANI" wrote in message ... 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 |
| All times are GMT +1. The time now is 07:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com