Pulling in data from Excel

[JonathanBr]

Hello.

Looking for some assistance. I have an access database that we pull in data from excel files and store. Currently we enter the data manually via a form. I want to automate it.

Context

Currently we have a statistical package that generates outputs in an excel file. Those outputs are the same layout each time, but we save each excel file with a different name to reflect the different model. Each batch of statistical modelling has over 200 models, hence 200+ excel files.

For other parts of ythe workflow we store outputs in a database - now Access.

Task

I want to automate it, by oppening up the excel file and taking the values from defined cells and populating it into associated fields in the current recordset in access.

I have figured out how to open a defined excel file - but need to make that a paramneter that is passed through the VBA code - (and selected by the user using an oen file dialog box. )

Whehn the file is opened, then I need to look a various cells in various worksheets and populate the data in the current recordset in the database.

Any pointers or assistancew ill be very much appreciated...

 

[arnelgp]

You said that output to excel is same format. Tell us which column heading goes to what Field on your table.

 

[Orthodox Dave]

If it works for you, you can use the Excel files as Link Tables, by selecting External Data tab, new data source - file - Excel (might be different in your version). Remember to select the radio button "Link to the database by creating a linked table". The advantage is your database data is always up to date with the spreadsheet data. And you can then use the link tables to populate recordsets, queries etc in the normal way.

If you want to use column headers as the field names, the default is Row 1 of the spreadsheet. If the column headers are on, say, the 3rd line, you can create a range in the spreadsheet, e.g. by selecting the Print Area. The disadvantage is if the spreadsheet grows. Or you can just create the link table and add the field names afterwards.

 

[JonathanBr]

Dear all

First - apologies that I have not replied sooner. It was a hectic week. A bit of further explanation.

1. We have a statistical package that we use say 200 times every quarter to create new statistic equations. The statistics package geneates outputs for each equation. These are generated in an excel file of the same format. An example is attached.

2. We have 200 separate files each time we revise our statistical equations.

3. While we have 200 files, we do need to store some information in a central location. I.e. a database. The content from the same cells in each file, for the individual equaion, is added into a record in a database.

4.We have a form at the moment to populate the data. Data is manually typed in...

5. What I would like to do is automate it. for example, the value in the "Model Output" sheet cell F15 will always be populated into the field "s error" of the current active record on the database. Similarly cll F35 of the "Chart" sheet will always be populated into the"Combined Band Score" field in the current active record of the database form.

6. As you can imagine, the attached excel file will be duplicated and stored in a different location and file name for every equation created. So - I will nead a command button that will allow the user to search for the file and then pull the data into the current record. It should then close the excel file. (P.. the excel file might be reas only)...

Once I get the idea of opening etc, I can then define all the fields in the vba.

For reference, the database table is called "Main_Table" and the example fields "S_error" and "Combined_Band_Score".

I do hope someone can help / get me started.

JB

 

[arnelgp]

on VBE, you set reference (Tools->Reference)
to Microsoft Office XX.X Object Library.
XX.X is the version of Excel you have.

Run form Main_Table.

 

[JonathanBr]

arnelgp

Thank you so so much. I have pasted it in and it looks like it is working. I need to ensure we then saee in the form - but that should be finew. And add in all the other fields we need to bring through.

Really really appreciate it. Fabulous

 

[arnelgp]

Your welcome!