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

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

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

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

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?

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

The above idea looks good, and as an alternative you may like to try something like this: (http://www.access-programmers.co.uk/forums/showthread.php?t=72638)

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

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

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 Until 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

Thanks Rabbie. While accompanies Wend and Until is Logical

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

Just had a thought whilst reading the threads:rolleyes: 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

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?

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

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!

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!

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?

Hi,

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

any ideas?

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 & ")"

Thank you, this was exactly the type of code I was looking for. Its so obvious, I'm just not very confident in Access as I am in Excel.

I've tried that line in my code, and I get "subscript out of range" error message. I've define strsql as string, so why am I getting this message?

Is the workbook open?

no, its shut. should it be open?

Yeah you can't get a value out of a combobox in a closed excel spreadsheet.

so how do I open the worksheet in access, and retrieve the information.

Also, wouldn't this slow down my database if I have to do about a hundred a month?

Do a few searches on the forum as your going to have to come up with some code to find and then loop through each workbook.

Why not just store the information in cells in excel instead of a comboboxes and textboxes?

If you had all the information in a single excel spreadsheet you could just create a named range for it and then import it with a simple bit of SQL.

In all honesty I don't even know if a value entered into a textbox in excel is retained after a spreadsheet closes.

Thanks Chergh, the value in a combobox is retained.

Just finally, I've found something to open the file. Next error is in the syntax you gave. any ideas.

Otherwise, I'm just going to redesign this whole thing. i thought it should be easy enough to pick up data, but its causing more troubles than its worth!

Thanks!

Post all the actual code you have so far and I'll take a look at it this weekend, I'm off down the pub.