Excel - 每天从在线 xls 文件导入数据

Question

如何将数据从在线 .xls 文件(例如 http://www.rba.gov.au/statistics/hist-exchange-rates/2010-2013.xls )导入到 Excel 中？我无法使用“来自网络”数据连接。如果更合适的话，我可以访问 Access。

或者，我如何使用每日更新的网页中的数据并让它每次都保存带有日期的表格(而不是覆盖前几天的记录)？

Answer 1

您是否尝试过像以下这样简单的操作:

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 = "http://www.rba.gov.au/statistics/hist-exchange-rates/2010-2013.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.Add(After:=wbMe.Sheets(w))

'## 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