将数据从Web插入Excel工作表 - 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

我需要将来自web的xls文件中的数据插入到我当前打开的工作簿中。问题是如果我多次运行这个宏，它总是创建新的表。理想情况下，我想删除旧内容，并将其替换为同一张纸上的新内容。有可能的？这是我的代码

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

1 个解决方案

#1

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

假设从Web转储的工作表始终是工作簿中的最后一个工作表，请使用以下行：

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

before copying new sheet.

在复制新表之前。

#1