Excel – import i dati da un file xls in linea

Sto cercando di get i dati da un Excel che si trova online in un foglio sul mio computer. Ho provato lo script da questo post ( Excel – import i dati da un file online xls each giorno ), ma vorrei ricevere i dati nello stesso foglio invece di creare un nuovo foglio / aprire una nuova cartella di lavoro each volta che voglio eseguire la macro.

Ho provato a sostituire questa linea:

Set wsNew = wbMe.Sheets.Add(After:=wbMe.Sheets(w)) 

con: Set wsNew = wbMe.Sheets("Sheet9")

In questo modo, ha cercato di aggiornare sempre il foglio9, anziché aprire un nuovo foglio di lavoro each volta che eseguo la macro.

Il codice VBA completo che ho usato è stato:

 Sub OpenXLSfromURL() Dim wbMe As Workbook Dim wsNew As Worksheet Dim w As Integer Dim wbURL As Workbook Dim url As String Set wbMe = ThisWorkbook url = "https://google.com/test.xls" Set wbURL = Workbooks.Open(url) '## Add code to copy this data to your workbook and/or manipulate the data...' w = wbMe.Sheets.Count '## Add a new worksheet to the end of ThisWorkbook:' Set wsNew = wbMe.Sheets(Sheet9) '## Copy & Paste this data in to our new worksheet:' wbURL.Sheets(1).Cells.Copy Destination:=wsNew.Range("A1") '## Close the downloaded version which we no longer need:' wbURL.Close End Sub Dim wsNew come foglio di lavoro Sub OpenXLSfromURL() Dim wbMe As Workbook Dim wsNew As Worksheet Dim w As Integer Dim wbURL As Workbook Dim url As String Set wbMe = ThisWorkbook url = "https://google.com/test.xls" Set wbURL = Workbooks.Open(url) '## Add code to copy this data to your workbook and/or manipulate the data...' w = wbMe.Sheets.Count '## Add a new worksheet to the end of ThisWorkbook:' Set wsNew = wbMe.Sheets(Sheet9) '## Copy & Paste this data in to our new worksheet:' wbURL.Sheets(1).Cells.Copy Destination:=wsNew.Range("A1") '## Close the downloaded version which we no longer need:' wbURL.Close End Sub Dim url Come string Sub OpenXLSfromURL() Dim wbMe As Workbook Dim wsNew As Worksheet Dim w As Integer Dim wbURL As Workbook Dim url As String Set wbMe = ThisWorkbook url = "https://google.com/test.xls" Set wbURL = Workbooks.Open(url) '## Add code to copy this data to your workbook and/or manipulate the data...' w = wbMe.Sheets.Count '## Add a new worksheet to the end of ThisWorkbook:' Set wsNew = wbMe.Sheets(Sheet9) '## Copy & Paste this data in to our new worksheet:' wbURL.Sheets(1).Cells.Copy Destination:=wsNew.Range("A1") '## Close the downloaded version which we no longer need:' wbURL.Close End Sub 

Microsoft Office Excel Spreadsheet è il miglior software di Office, Excel VBA e formule Excel rendono il foglio di calcolo più veloce.