Inserting data from web to excel sheet - VBA

user_8650034 0
Viewed 82

I need to insert data from xls file from web, to my current opened workbook. The problem is that if I run this macro several time, it always create new sheet. Ideally I would like to delete old content and replaced it with new content on same sheet. It is possible? Here is my code

Sub downloadData()

Dim wkbMyWorkbook As Workbook
Dim wkbWebWorkbook As Workbook
Dim wksWebWorkSheet As Worksheet
Dim currentDate As Date

Set wkbMyWorkbook = ActiveWorkbook

currentDate = Date

Workbooks.Open ("https://www.somewebpage.com/file.xls")

Set wkbWebWorkbook = ActiveWorkbook
Set wksWebWorkSheet = ActiveSheet

wksWebWorkSheet.Copy After:=wkbMyWorkbook.Sheets(Sheets.Count)

wkbMyWorkbook.Activate
wkbWebWorkbook.Close

End Sub
vba excel
1 Answers

Assuming the sheet dumped from the web will always be the last sheet in the workbook, use this line:

Application.DisplayAlerts = False
wkbMyWorkbook.Worksheets(wkbMyWorkbook.Worksheets.Count).Delete
Application.DisplayAlerts = True

before copying new sheet.

user_15695360
Related