Import online xls data into MS access via VBA

Question

I need to import exchange rate data stored in an Excel spreadsheet online into an Access data table. However first I need to manipulate it so I would like to import it into an array and then write the array to the table. The code I used for Excel doesn't seem to work with Access...

Dim arr as variant
Workbook.Open ("http://www.rba.gov.au/statistics/tables/xls-hist/f11hist.xls")
arr=activeworkbook.worksheets("Data").Range("A12:X" & Range("A1045876").end(xldown).Row)

'data manipulation ommitted

'add to data table

Clearly this doesn't work in Access, but I've got no idea how to open the file and read the data. Any help appreciated!

Answer 1

Your question is overly broad, so the answer is generic. You can use Microsoft Excel object library in MS Access application by adding the reference to that library and start using its methods, similar to what you have done in Excel. More details in: https://msdn.microsoft.com/en-us/library/office/ff194944.aspx. Hope this may help.

Answer 2

I guess this line can help you. Docmd.Transferspreadsheet acImport,,"name of excel table","link to find the table (ex:c:.....)",true that will help to take a excel table and import its on access .

Answer 3

Excel does this by - behind the scene - first downloading the file then reading it.

Access can't do this, but you can use VBA to download the file Download file from URL and then create a link to a Worksheet or a Named Range in it. Or open the file via automation.