Error Handling in VBA

Z

zaska

Well-known Member
Joined
Oct 24, 2010
Messages
1,046
Hi,


I have the following code and How it can be Error Handled when the following output turns out becuase of Server problem

<table border="0" cellpadding="0" cellspacing="0" width="668"><col style="mso-width-source:userset;mso-width-alt:3254;width:67pt" width="89"> <col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> <col style="mso-width-source:userset;mso-width-alt:2486; width:51pt" span="4" width="68"> <col style="mso-width-source:userset;mso-width-alt:3108;width:64pt" width="85"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:67pt;font-size: 11.0pt;color:white;font-weight:700;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" height="20" width="89">CNX INFRA</td> <td class="xl64" style="border-left:none;width:58pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" width="77">08-Jul-11</td> <td class="xl65" style="border-left:none;width:51pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" width="68">3177.05</td> <td class="xl65" style="border-left:none;width:51pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" width="68">3190.15</td> <td class="xl65" style="border-left:none;width:51pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" width="68">3144.35</td> <td class="xl65" style="border-left:none;width:51pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" width="68">3151.65</td> <td class="xl65" style="border-left:none;width:64pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" width="85">142856927</td> <td class="xl65" style="border-left:none;width:51pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" width="68">1752.97</td> <td class="xl66" style="border-left:none;width:58pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" width="77">Column1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="20">CNX IT</td> <td class="xl68" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">08-Jul-11</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">6793</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">6835.25</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">6714</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">6723.85</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">11587450</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">631.28</td> <td class="xl70" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" height="20">CNX MNC</td> <td class="xl69" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext"><HEAD>****** HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=ISO-8859-1"><TITLE>Not Found</TITLE></HEAD></td> <td class="xl69" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext"> </td> <td class="xl69" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext"> </td> <td class="xl69" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext"> </td> <td class="xl69" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext"> </td> <td class="xl69" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext"> </td> <td class="xl69" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext"> </td> <td class="xl70" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="20"><H1>Not Found</H1> The requested object does not exist on this server. The link you followed is either outdated</td> <td class="xl69" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none"> inaccurate</td> <td class="xl69" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none"> or the server has been instructed not to let you have it. CNX PHARMA</td> <td class="xl68" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">08-Jul-11</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">4897.4</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">4910.35</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">4840.15</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">4850.3</td> <td class="xl70" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">3161622</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" height="20">CNX PSE</td> <td class="xl69" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext"><HEAD>****** HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=ISO-8859-1"><TITLE>Not Found</TITLE></HEAD></td> <td class="xl69" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext"> </td> <td class="xl69" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext"> </td> <td class="xl69" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext"> </td> <td class="xl69" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext"> </td> <td class="xl69" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext"> </td> <td class="xl69" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext"> </td> <td class="xl70" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="20"><H1>Not Found</H1> The requested object does not exist on this server. The link you followed is either outdated</td> <td class="xl69" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none"> inaccurate</td> <td class="xl69" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none"> or the server has been instructed not to let you have it. CNX PSU BANK</td> <td class="xl68" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">08-Jul-11</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">4026.9</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">4074.85</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">4003.9</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">4018.2</td> <td class="xl70" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">9784008</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" height="20">CNX REALTY</td> <td class="xl68" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">08-Jul-11</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">291.75</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">302.2</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">290.45</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">297.75</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">77007835</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">624</td> <td class="xl70" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="20">CNX SERVICE</td> <td class="xl68" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">08-Jul-11</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">7044.95</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">7072.05</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">6953.4</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">6962.2</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">60769785</td> <td class="xl69" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">2944.68</td> <td class="xl70" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl71" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" height="20">S&P CNX DEFTY</td> <td class="xl72" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">08-Jul-11</td> <td class="xl73" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">4483.9</td> <td class="xl73" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">4486.6</td> <td class="xl73" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">4413.1</td> <td class="xl73" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">4422.05</td> <td class="xl73" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext"> </td> <td class="xl73" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext"> </td> <td class="xl74" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext"> </td> </tr> </tbody></table>

VBACode

Code: 
Public Sub downloadNse()
Dim arrURL() As String
Dim dtmDate As Date
Dim c As Range
Dim i As Long
Dim s As String
Dim bArray() As Byte
Dim hFile As Integer
Dim strLocalFile As String
Dim sTemp As String
Dim iPtr As Long

 
Dim oXMLHTTP As MSXML2.XMLHTTP '(reference to C:\Windows\System32\msxml2.dll for 32 bit systems)

    '-------------------------------------------------------------------------------
    'ENTER CONSTANTS HERE - NOTHING ELSE *SHOULD* NEED TO BE CHANGED
    Const CELL_WITH_DATE As String = "B1"
    Const RANGE_WITH_SYMBOLS As String = "A1:A19"
    Const SAVE_DIRECTORY As String = "E:\Macros\Output\Indices\" 'end with forward slash
    '-------------------------------------------------------------------------------
    
    dtmDate = Range(CELL_WITH_DATE).Value '//Date
    strLocalFile = SAVE_DIRECTORY & Format(dtmDate, "dd-mm-yyyy") & "_.csv"
    For Each c In Range(RANGE_WITH_SYMBOLS) '//11 cells with symbols
        If Len(c.Value) > 0 Then
            ReDim Preserve arrURL(1 To 2, 0 To i)
            arrURL(1, i) = "http://www.nseindia.com/content/indices/histdata/"
            arrURL(1, i) = arrURL(1, i) & UCase(c.Value)
            arrURL(2, i) = UCase(c.Value)
            arrURL(1, i) = arrURL(1, i) & Format(dtmDate, "dd-mm-yyyy") & "-" & Format(dtmDate, "dd-mm-yyyy") & ".csv"
            i = i + 1
        End If
    Next c
    
    'download the file from the web to the hardrive
    'loop through symbols in turn
    Set oXMLHTTP = New XMLHTTP
    hFile = FreeFile
    Open strLocalFile For Binary As #hFile
    For i = 0 To UBound(arrURL, 2)
        oXMLHTTP.Open "GET", arrURL(1, i), False
        oXMLHTTP.send
        'Wait for request to finish
        Do While oXMLHTTP.readyState <> 4
            DoEvents
        Loop
        
        bArray = oXMLHTTP.responseBody
       

        sTemp = ""

        For iPtr = LBound(bArray) To UBound(bArray)

          sTemp = sTemp & Chr(bArray(iPtr))

        Next iPtr

        sTemp = Replace(sTemp, "Date", "")

        sTemp = Replace(sTemp, "Open", "")

        sTemp = Replace(sTemp, "High", "")

        sTemp = Replace(sTemp, "Low", "")

        sTemp = Replace(sTemp, "Close", "")

        sTemp = Replace(sTemp, "Shares Traded", "")

        sTemp = Replace(sTemp, "Turnover (Rs. Cr)", "")

        If Left(sTemp, 2) = Chr(34) & Chr(34) Then

          sTemp = Mid(sTemp, 3)

        End If

        Do While Left(sTemp, 3) = "," & Chr(34) & Chr(34)

          sTemp = Mid(sTemp, 4)

        Loop

        If Left(sTemp, 1) = Chr(10) Then sTemp = Mid(sTemp, 2)

        ReDim bArray(Len(sTemp) - 1)

       

        Put #hFile, , arrURL(2, i) & "," & sTemp

 
              
       Next i

Handler:
On Error Resume Next
Close #hFile
Set oXMLHTTP = Nothing

End Sub

Kindly help me in Hanldling errors . I want this code to be Re-Run when the out put has errors till all the quotes are filled.

Is it possible?

Thank you
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Why not just check for Not Found?
 
Upvote 0
Hello Norie,

Yes Not found will do but how to implement it in VBA code.

If Not found is found then will the VBA code run again till the output is fetched. Or will it stop.

Here's my situation at present.

Everytime i find output with few symbols Not Found then i delete the output excel file and then Run the VBA code several times till i get the proper output and this is really hectic.

Kindly help me in this regard,

Have a nice day

Thanks for the response
 
Upvote 0
I take it you are looping through the symbols?

Just check for Not Found after each iteration and if required rerun the code for that symbol, and just that symbol.

Here's some psuedo-code.

For i = 0 To UBound(arrURL, 2)

Do

' code to send request and get response

Loop Until request doesn't return NOT FOUND

Next i

I don't actually know how you can check the request doesn't return NOT FOUND, perhaps you should look into the properties of the XMLHTTP object.
 
Upvote 0
Hi,

Can you kindly place your pseudo code into my VBA so that i can check what's happening.

Thank you
 
Upvote 0
It's psuedo- code but the first line of code is from your code.

Does that not help?
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
617
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
573
mumps
mumps
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
E
  • Question Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
518
esben
E

Forum statistics

Threads
1,224,521
Messages
6,179,291
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top