CODEWITHSUNDEEP
vbaexcel
joe32
joe32

Reputation: 43

Inserting data from web to excel sheet - VBA

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

Upvotes: 4

Views: 113

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27239

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.

Upvotes: 2

Related Questions