PDA

View Full Version : Need help in excel vba



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

jolivanes
08-17-2016, 09:48 AM
When you copy the sheet and rename it, it will try to rename it on the 2nd download to an existing name that is hard coded into the code.
One easy would be to name your new sheets "MyNewWebsheet" & wkbMyWorkbook.Sheets.Count.
Change this

wkbMyWorkbook.Sheets(ActiveSheet.Name).Name ="MyNewWebSheet"
to this

wkbMyWorkbook.Sheets(ActiveSheet.Name).Name ="MyNewWebSheet" & wkbMyWorkbook.Sheets.Count
of course you can't delete any sheets out of the workbook in the meantime.

vjvijay88
08-17-2016, 10:13 PM
thanks sir

vjvijay88
08-17-2016, 10:18 PM
h t t p s://w w w.dropbox.com/s/av2b9267sqppndb/REPORT_Activities.xlsx.xlsm?dl=0

i have attached my workbook and i have macro 2 code but not working properly can u correct me plz

Sub Macro2() Dim count As Integer
Dim count2 As Integer
Dim countR As Integer

Dim YYYY As String
Dim dd As String
Dim MMM As String
Dim ws As Worksheet
Dim i As Integer
Dim EndDate As Date
Dim wd As Long
On Error Resume Next
Application.ScreenUpdating = False
ActiveWorkbook.Sheets("Temp").Visible = True
Sheets("Summary").Activate


YYYY = Range("C19").Value


dd = Range("C17").Value
MMM = Range("C18").Value
'h t t p://w w w.nseindia.com/content/fo/fii_stats_01-Aug-2016.xls
'h t t p://w w w.nseindia.com/content/fo/fii_stats_01-Aug-2016.xls
Sheets("Data").Select
Range("A3:H25000").Select
Selection.Clear



Sheets("temp").Select
Sheets("temp").Cells.ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;h t t p://w w w.nseindia.com/content/fo/fii_stats_" & dd - MMM - YYYY & ".xls" _
, Destination:=Range("$A$1"))
.Name = _
"h t t p://w w w.nseindia.com/content/fo/fii_stats_" & dd - MMM - YYYY & ".xls"

.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

'ws.Name = "testing"

End Sub