How do I import data from an online .xls file online (such as http://www.rba.gov.au/statistics/hist-exchange-rates/2010-2013.xls) into Excel? I can't use the "From Web" data connection. I have access to Access if that is more appropriate.
如何从在线(例如http://www.rba.gov.au/statistics/hist-exchange-rates/2010-2013.xls)将在线.xls文件中的数据导入Excel?我无法使用“From Web”数据连接。如果更合适,我可以访问Access。
Alternately, how can I use the data from a daily updated webpage and have it save the table with the date every time (instead of overwriting previous days' records)?
或者,我如何使用每日更新的网页中的数据,并使每次保存表格的日期(而不是覆盖前几天的记录)?
1 个解决方案
#1
6
Have you tried something as simple as:
你尝试过这样简单的事情:
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
#1
6
Have you tried something as simple as:
你尝试过这样简单的事情:
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