We help IT Professionals succeed at work.

We want MS Access to use a combo box to pull names from an Excel spreadsheet column

We have four MS Access databases that are used almost on a daily basis. Each database contains records that are input and maintained by members of the staff.  The person who updated the items are listed in a field on each database.  The names of staff members change frequently because of people leaving the job or people being hired.  

There is an official excel spreadsheet list that is kept on a shared drive and is updated frequently by the administrative staff.

I have decided that I want all of our databases to pull the staff members' names from the excel list and not depend on local lists created for each table in the databases.  I would like to have pull downs in each db on the field that holds the names and allow the users to select from that current list since it is always up-to-date.

My problem is that Access will not allow me to use the Excel spreadsheet to supply the names because it wants to use a list from an Access table.

Is it possible to create an Access combo box which will pull the names from the Excel spreadsheet column? We are using Access version 2007 in Windows.
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
have you tried linking to to Excel file and use it as the RowSource of the combo box?

Author

Commented:
Thanks capricorn1.

I had not thought of "linking".  Each time that I tried to make a combo box while in the database, it asks me to select from a list of tables or queries and I could see no connections to Excel or anything else.

I am now going to study help on linking Access to Excel and will report back soon.

Thanks again.
CERTIFIED EXPERT
Top Expert 2016
Commented:
to create a link to excel

external data > excel >>> then just follow the wizard

Author

Commented:
Hello Capricorn1,
THANKS.  I had gone to the Microsoft Help section and clicked the big Circle on the top left, clicked External data, then Excel.  I actually created a table.  I then created an query copy of the table for the following reason.

The Excel spreadsheet that has staff members names in field 1 also has names of the security office, conference rooms, etc.  Therefore I go permission to modify the original excel file by creating a special column where I inserted a "." in the rows that I do not want to be shown on my table.  I then changed to color to "white" and the periods can not be seen.  On the query version of the table, I plan to place  ( <>="." ) as a criteria.  Hopefully this will eliminate all items that are not names of staff members.  However, I am not sure if the code is ( <>="." ) or ( <>"." ) or ( not=".").

In any event, you have help a lot and I now feel that it is definately possible.

Author

Commented:
Your suggestion worked very well.  I was able to get the list from the Excel spreadsheet after making a link to it.  
There is one thing that I would like to change but if it can not be done; I will be happy with the results that I have.

When I called up the query which holds only the first two Excel columns; I put a ( ~ ) in the fields that I do not want to show.  In the criteria area I put <>"~" and it ignored the command and showed all of the records.  I even tried ( Not"~" ) and that also did not work.  Any suggestions?

If there are non, let me know and I will  close this thread.  Thanks again.

Author

Commented:
To capricorn1.

Thank you, Thank you Thank you.  Our bosses are so happy that they don't care if the last twelve line show things like "Conference Room", "HelpDesk", or "Main Office".  It does show all of the staff members.

I asked the admin person to temporily add a new name to the list and to temporarily remove a name.  When I opened my Accwess file, the changes were done as desired.

I did learn that even though I am not opening or doing anything to the original Excel file, whenever I have the database open, the Excel file is "locked".  Is that because the file is linked?  If that is the case, I may have to do something else because several people will be using this file and anyone of them may accidently leave it open. I don't want to lock the admin. Excel file for more than a few minutes.

In any event, this was a great learning experience and I know of many places and circumstances that I can use this procedure.  I also believe that other people reading this link will be able to use it also.

After I get a response from capricorn1, I will close this thread.  THANKS.
CERTIFIED EXPERT
Top Expert 2016

Commented:
instead of using  Not"~" , <>"~"

use

      not like "~*"

Author

Commented:
Thanks again.  "Not Like "~" did not work either.  Tonight, I going to change the color back to Black instead of White to see if that made a difference.  I am also going to change from "~" to "X" to see if it is not working because of the special characters "~" or "." .
Also....
If you get the chance, let me know if linking to a spreadsheet in Excel will normally lock the excel file as long as the DB is open.  If that is not normal, then I must have done something wrong.  If I have the database open; the administrator is not able to open the Excel file.  As soon as I close the DB, if can be opened immediately.  If this is normal, is there a way to have the program to automatically attach to the Excel file, do a quick update, then immediately unlink the file until the next time it is needed? We do not want to keep the administrator from accessing her file any more than a few minutes.

I will close this thread as soon as I get an answer to the above two questions.   Thank you.

Commented:
Sherman - do not forget to add the star so it should be: Not Like "~*"

To stop the locking of the excel file you could just "load" the excel file into a table using VBA or a macro, when the access database is opened.
That way you only have it "linked" for a very short time.
It will not update the excel list in the access database list while access is opened however... although you could just add a button or other form opening event or something to one of the forms that would update it.
CERTIFIED EXPERT
Top Expert 2016

Commented:

as i said from my post at http:#a37072901 


use

      not like "~*"

Open in new window

* can you upload a copy of the excel file..

is the excel file, always get updated ?
if this is the case you may
* import the record to an access table
* use disconnected recordset to populate your combo box




 

Author

Commented:
Thanks Wiesje and Capricorn1.

I have attached a sample similar excel file whire I have about 15 names and a few non name rows.  The real excel file has about 150 names with about 15 non names.  All information shown is false on purpose.  Only the first two columns are needed for the database.  The Rows that are not needed in the database have a ( ~ ) in the second row.  I changed the color to white so it would not show on the screen or print on paper.  If the query table worked with the "not like" all of the extra rows would not even show on the pulldown.  I did try the not like "~*" as suggested and it was ignored.  We can live with the extra rows on the pulldown.

My main concern now is that the Excel file is locked whenever the database is open.  From reading your suggestions, it seems that I need to learn the steps necessary to "Import" the Excel file when the database is opened then converting the copy into a db pulldown.  I also know that any changes made to the original Excel file while the database is open will not be reflected in the pulldown until the next time the database is opened.  I will now try to learn how to make a macro that will do the above copying and converting automatically.

Thanks.  I am learning a lot.



Author

Commented:
EE-TEST-List.xls

Here is the sample Excel file mentioned above.  Thanks.

Author

Commented:
Thanks you

Explore More ContentExplore courses, solutions, and other research materials related to this topic.