Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
When I open Excel, workbooks open automatically. How can I stop t | Excel Discussion (Misc queries) | |||
Excel 2003 Workbooks.Open with CorruptLoad=xlRepairFile fails on Excel 5.0/95 file due to Chart, with Error 1004 Method 'Open' of object 'Workbooks' failed | Excel Programming | |||
Workbooks.Open doesn't open file in ThisWorkbook | Excel Programming | |||
Workbooks.Open closes other workbooks | Excel Programming | |||
Workbooks.Open / .Open Text - How do you stop the .xls addition? | Excel Programming |