Access - collecting info from furthest right cell in table

[Guest]

We have an existing excel spread sheet which is updated every month with new
prices, and would like to integrate with an Access database. We need to
collect info from the furthest right cell with info in it in the excel spread
sheet and put it into a query. Any suggestions?

 

[Pat Hartman\(MVP\)]

You can't do this in a query without knowing the name of the column. You
will need to write VBA code using OLE to interrogate the spreadsheet to
identify the last populated column. You can then obtain its "Name" from the
first row of the spreadsheet (assuming that the spreadsheet has column names
in the first row). Finally, you can build an SQL string with the
information obtained and run it or use it as the RecordSource of a form or
Report. You will need to alias the column name when you build the SQL
string otherwise, you'll also need to change the form or report.

I think you should consider making Access the entry point of the data and
then exporting data to spreadsheets if they users aren't happy with using
pivot tables in Access to play with their data.

If you decide to develop the data entry in Access, don't make the mistake of
simply importing the spreadsheets. You will need to normalize them. The
spreadsheet has a repeating group that causes new columns to be continually
added to the sheet. Once you convert this data into tables, that will no
longer be necessary. The new "column" will actually end up being just
another row of data and will not require ANY changes to the application.
This is the biggest advantage to a RDBMS over a spreadsheet. You Never need
to add new columns on the fly so you never need to modify your calculations.