vjvijay88
08-17-2016, 03:41 AM
Option Explicit
PublicSub OpenWebXLS()
' *************************************************
' Define Workbook and Worksheet Variables
' *************************************************
Dim wkbMyWorkbook As Workbook
Dim wkbWebWorkbook As Workbook
Dim wksWebWorkSheet As Worksheet
Set wkbMyWorkbook = ActiveWorkbook
' *************************************************
' Open The Web Workbook
' *************************************************
Workbooks.Open ("h t t p: // w w w.nseindia. c o m/content/fo/fii_stats_01-Aug-2016.xls")
' *************************************************
' Set the Web Workbook and Worksheet Variables
' *************************************************
Set wkbWebWorkbook = ActiveWorkbook
Set wksWebWorkSheet = ActiveSheet
' *************************************************
' Copy The Web Worksheet To My Workbook and Rename
' *************************************************
wksWebWorkSheet.Copy After:=wkbMyWorkbook.Sheets(Sheets.Count)
wkbMyWorkbook.Sheets(ActiveSheet.Name).Name ="MyNewWebSheet"
' *************************************************
' Close the Web Workbook
' *************************************************
wkbMyWorkbook.Activate
wkbWebWorkbook.Close
End Sub
This vba code works for only one time when again i run it its shows error as worksheet exists and need to overwrite in same work sheet, and i need to add that i have to download date wise xls file and i need some thing like daily i have to download data in date wise manner can any one help me ( i have to change data month year ) in column so that i can change and get data daily
PublicSub OpenWebXLS()
' *************************************************
' Define Workbook and Worksheet Variables
' *************************************************
Dim wkbMyWorkbook As Workbook
Dim wkbWebWorkbook As Workbook
Dim wksWebWorkSheet As Worksheet
Set wkbMyWorkbook = ActiveWorkbook
' *************************************************
' Open The Web Workbook
' *************************************************
Workbooks.Open ("h t t p: // w w w.nseindia. c o m/content/fo/fii_stats_01-Aug-2016.xls")
' *************************************************
' Set the Web Workbook and Worksheet Variables
' *************************************************
Set wkbWebWorkbook = ActiveWorkbook
Set wksWebWorkSheet = ActiveSheet
' *************************************************
' Copy The Web Worksheet To My Workbook and Rename
' *************************************************
wksWebWorkSheet.Copy After:=wkbMyWorkbook.Sheets(Sheets.Count)
wkbMyWorkbook.Sheets(ActiveSheet.Name).Name ="MyNewWebSheet"
' *************************************************
' Close the Web Workbook
' *************************************************
wkbMyWorkbook.Activate
wkbWebWorkbook.Close
End Sub
This vba code works for only one time when again i run it its shows error as worksheet exists and need to overwrite in same work sheet, and i need to add that i have to download date wise xls file and i need some thing like daily i have to download data in date wise manner can any one help me ( i have to change data month year ) in column so that i can change and get data daily