Real-Time Database Updating

0
votes

I have ~300 Identical Excel Spreadsheets (.xlsx) that all have ten different cells I would like to project to an Access database. Some are single cells, a few ranges-- all of them are named in each individual workbook. The database should hold all values from the same worksheet on the same line, and all numbers should update in real time.

My goal is to use access to keep a running record on the contents of these cells. I have attempted to link the workbooks to the Master Access database using: External Data->Import and Link->Excel->Link to the data source by creating a linked table-> and here I would select one of the named ranges and click 'Finish', but I can only do this once per spreadsheet. This makes repeating this process for all spreadsheets unfeasible.

Is there a way to use Access VBA to create a linked Excel Table for each Workbook in the folder?

I am sort of teaching myself Access, and am still relatively new at it so any insight will help.

Cheers.

1
databasems-accessvba
So... you're asking how to turn Excel into a database? – Mathieu Guindon
Basically. I'm asking how to project values from hundreds of different spreadsheets onto an Access Database using VBA Code or otherwise. – VBAmazing
You should import it once, and then let the data live in the database; a spreadsheet should read from the db, not the other way around. Having data live in a spreadsheet is extremely error-prone. What if a row is accidentally deleted and all row ID's are now offset? Referencial integrity requires a database, trying to have it in a spreadsheet is a waste of time. – Mathieu Guindon
I'm afraid that whatever you do with Access and Excel it isn't going to work "in real time". Excel will take a lock on the spreadsheet when it is opened, and Access won't be able to see changes until the spreadsheet is saved and closed, and whatever query you are using to display the results is re-executed.Sounds like you need a database and not hundreds of spreadsheets. – Lord Peter

1 Answers

2
votes

Luckily, I had just that lying around somewhere. This links all excel files in the same folder as the database.

Note that this just uses the file name as the tablename, and links everything using default settings. Change the DoCmd.TransferSpreadsheet line to customize it. Originally I have this linked to a form so I can choose what to import, and how it is handled.

Public Sub ImportExcel()
    Dim objFSO As Object 'FileSystemObject
    Dim databasefolderlocation As String
    Dim objFolder As Object 'Folder
    Dim objFile As Variant
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    databasefolderlocation = Application.CurrentProject.Path 'This sets the folder, you can change it for another folder
    Set objFolder = objFSO.GetFolder(databasefolderlocation)
    For Each objFile In objFolder.Files
        If objFile.Name Like "*.xls" Or objFile.Name Like "*.xlsx" Then
            DoCmd.TransferSpreadsheet acLink, , objFile.Name, objFile.Path
        End If
    Next objFile
End Sub