Importing an Excel spreadsheet

[Guest]

Hello

I am trying to import an excel table into a dropdown box called "lender"

This box contains the names of our current lending staff. I have input the
names into Access using the properties box, however, overtime this would
become tedious as we add or loose lenders from our team. I know there is a
way of importing an excel spreadsheet into access, however I do not know how.
I have created a spreadsheet called tbl_lenders_headoffice.xls. This spread
sheet lists the lenders names in column "A" and occupies rows 1 ~ 6.

Can anyone assist with this??

 

[Douglas J. Steele]

Through the GUI, you select File | Get External Data | Import and the wizard
will help you with the rest. (You also have the option of simply linking to
your spreadsheet, rather than duplicating the data within Access)

Through VBA, you can use the TransferSpreadsheet method.

 

[Guest]

Douglas

That didnt seem to work. I dont think that I explained myself properly....I
want the database to access the excel spreadsheet eachtime the dropdown box
is activated/ That way, if a new user is placed on the spreadsheet, then
he/she will appear. The data base has to be able to read the spreadsheet
eachtime someone clicks on the dropdown box.

 

[Ronald Roberts]

If you only have 6 rows in Excel, why don't you create a table in your
Access DB, enter the information, then use the table as the
row source for the drop down box.

You can create a form for updating the lending staff table. Updating
the table vs updating the spreadsheet would be the same. This way
you don't need Excel.

 

[Guest]

Sounds logical. How woudl I set this up???

If you only have 6 rows in Excel, why don't you create a table in your
Access DB, enter the information, then use the table as the
row source for the drop down box.

You can create a form for updating the lending staff table. Updating
the table vs updating the spreadsheet would be the same. This way
you don't need Excel.

 

[Ronald Roberts]

This is air code, so there may be missing items or steps.

1. Create your table in Access: tblLender
2. Import the excel spreadsheet into this table.
File/Get External Data/Import
3. Select your spreadsheet and import into "An Extisting Table"

4. Change your dropdown box like below. If you need to
modify the table data or concatenate your data, you can do this
in a query:

FullName: FirstName & " " & LastName

Then use FullName in the row source and not the first and last name.


Row Source Type : Table/Query
Row Source: SELECT tblLender.ColumnName1, tblLender.ColumnName2,
tblLender.ColumnName3, tblLender.ColumnName4,
tblLender.ColumnName5,tblLender.ColumnName6 FROM tblLender;

or

Row Source: SELECT Query1.ColumnName1, Query1.ColumnName2,
Query1.ColumnName3, Query1.ColumnName4,
Query1.ColumnName5,Query1.ColumnName6,
Query1.FullName FROM tblLender;

You don't need to include all of the columns from the table or query,
but make sure the column count and column width match the number of
columns from your query

Column Count: 6
Column Heads: Yes
Column Width: 0.5";0.5";0.5";0.5";0.5";0.5"
If you don't want a column to show, make it 0.0"
Bound Column: 0 this will return the first column form the dropdown box.
a 1 will return the second and so on. VBA is zero based indexing.

Control Source: the field in the table that will contain the selecting.

When you create a control on a form there are (...) to the right of some
of the properties. by clicking the (...) will open a wizard and allow
you to answer questions that will complete information for the property.

In the AfterUpdate event of the dropdown box, if you need other
information other than the selected item, you can do this.

SomeVaraible = Me!DropDownBox.Column(3)

HTH,
Ron