Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Workbooks.Open(URL)
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 41
Default 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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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





Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
when i open an Excell file 3 workbooks open.Why? Iain40 Excel Discussion (Misc queries) 2 February 3rd 09 05:04 PM
When I open Excel, workbooks open automatically. How can I stop t Rhealbird Excel Discussion (Misc queries) 2 February 23rd 06 10:08 AM
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 Frank Jones Excel Programming 2 June 15th 04 03:21 AM
Workbooks.Open closes other workbooks S. Daum Excel Programming 1 August 21st 03 07:47 PM
Workbooks.Open / .Open Text - How do you stop the .xls addition? Dave[_20_] Excel Programming 2 July 31st 03 04:03 AM


All times are GMT +1. The time now is 02:33 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement