Looping through tables in access database

[phonic]

Hi,

Aim:
I am building a database that picks up the results of a table in excel. (The table in excel is created by combo boxes and list boxes). I have about 100 of these excel tables in different files.

Problem 1: I want to go into each file, and pick up data, and populate a master table in the database. How do I do this? (all fields in excel are labelled - eg comboboxdate etc)

Problem 2: Each excel file has a different name, and I have a table in access with all these names. I want to loop through each name in the access table, and pick up data from each excel file. How can I do this?

Any hints to get me started?

Thanks

 

[Uncle Gizmo]

Are all of your excel files in exactly the same format? And is the format tabular?

 

[phonic]

Hi, yes, all my files are in the same format. they are essentially forms with comboboxes which are populated by a hidden list.

 

[Uncle Gizmo]

I think I would be inclined to import the hidden lists into access and append them into one table.

 

[phonic]

Hi, I've already done this. The hidden lists are now tables in access.

How do I do the loop to go through each name?

 

[DCrake]

If you are confident enough to use VBA code then you can do the following

AirCode

Dim Rs As DAO.RecordSet
Dim xlFile As String

Set Rs = CurrentDb.OpenRecordset("Your Table Name Here")

If Not Rs.EOF Then

Do While Rs.EOF
xlFile = Rs("Field that holds your file name and path")
....Your Code here to import data
Rs.MoveNext
Loop
Rs.Close
End If

Set Rs = Nothing

 

[Uncle Gizmo]

The above idea looks good, and as an alternative you may like to try something like this:

 

[phonic]

Thanks DCrake and Uncle Gizmo. DCrake, I'm comfortable with VB, so i tried your method, but it enters the Do Loop, and immediately exits without looking at the field name and path. Do I need anything specific switched on in my reference library?

Thanks

 

[DCrake]

Have you referenced the DAO library in your references.

are you getting error messages or is it not finding any records?

Post a sample of your code please

 

[Rabbie]

Try amending DCrake's code as follows (looks like there was a typo)

Dim Rs As DAO.RecordSet
Dim xlFile As String

Set Rs = CurrentDb.OpenRecordset("Your Table Name Here")

If Not Rs.EOF Then

Do [B]Until[/B] Rs.EOF
xlFile = Rs("Field that holds your file name and path")
....Your Code here to import data
Rs.MoveNext
Loop
Rs.Close
End If

Set Rs = Nothing

 

[DCrake]

Thanks Rabbie. While accompanies Wend and Until is Logical

 

[phonic]

Thanks! Thats great, and it helps with problem 2 (the loop).

any ideas on problem 1? How can I pick up the data from the excel file?

Thanks again

 

[DCrake]

Just had a thought whilst reading the threads If you know the names of the spreadsheets and their locations why not simply link them to your db and pick the data from the linked files?

If the data you want to move is in various cells and does not consist of many rows then create a new worksheet and reference them one one line and choose that worksheet to link to.

How you tackle proplem 1 depends on how the data is contructed in the worksheets. A description of this will lead to a more suitable solution.

David

 

[phonic]

I want to keep the excel spreadsheets and the database independent. Once I store all the information on access, I can delete the excel spreadsheets, because there will be a hundred new ones every month.

So, my excel spreadsheet looks like a form. It is made of lots of comboboxes, textboxes, memos etc. Each labelled very well.

I want MS Access to pick this information up. So, my master table in the database has a field called month, I want some code to go to the relevant excel spreadsheet (by the loop above) and pick up the textboxMonth.value, and populate that field, and the others.

Any ideas?

 

[DCrake]

Before we progress any further I am just thinking when you use the combo boxes to make selections is the selected month, for example, actually referende din another cell? My reason for this is that access will need to know which cell(s) contain the data you want to transfer. If the answer is simply held in a combo box I am not sure if it can be referenced. I have never attempted this in the past. There may be some out there that have and may be able to shed some light on this topic.

If you could send a sample spreadsheet even if it contains sample data and colour the cells you want to transfer I will be able to give you a solution easier.

David

 

[phonic]

Hi, I've included an attachment of a very simple version of my spreadsheet. I have hundreds of these filled out, and I want to populate a database with these.

Any ideas?

I thought, since everything is labelled, and populated, I could do something like pick up comboboxXYZ.value. Just not sure how to code it in Access.

Thanks in advance for any help!

 

[phonic]

anyone have any ideas on how i can do this? I want to bring in selected combobox results from the table attached into an access database.

Thanks!

 

[chergh]

I'm having real difficulty understanding what you are wanting to actually do.

Are you just wanting to take the values that are selected in the boxes in your spreadsheet and add them to a access table?

 

[phonic]

Hi,

yes, thats exactly what I'm trying to do.

any ideas?

 

[chergh]

thats easy enough, to get a value out of one of your boxes it would be:

debug.print workbooks("SampleReview.xls").worksheets("Review").ComboBoxPeriod.value

This will output the value to the immediate window. To get it into a table you could do something like:

strSQL = "INSERT INTO TableName (FieldName) " & _
"Values (" & workbooks("SampleReview.xls").worksheets("Review").ComboBoxPeriod.value & ")"