Link Excel to Access

[Guest]

I have not seen a question dealing with this type of linking. It seems from
answers to other questions on this subject, that when you link an excel
spreadsheet to access database (Access is the source, excel is the
destination) that you must import ALL records into excel. I would like to
link a subset of Access records into multiple excel files. E.G. Access
records 1-25 go into Spreadsheet A, Access records 26-50 go into Spreadsheet
B, Access records 51-100 go into Spreadsheet C, Access records 101 - 115 go
into Spreadsheet C, etc, etc.
I really don't want to have to import ALL records into ALL the spreadsheets
then do some weird filter (or VLOOKUP) in Excel to filter just the records I
want. That will create multiple huge Excel spreadsheets.
Yes, I know I could have multiple access tables and import each table into
the respective Excel spreadsheets, but that is cumbersome when you need to
change multiple records in multiple Access tables.

Any advice.

 

[Guest]

You might want to try MS Query to query the data in Access directly out of
Excel. With MS Query you can specify fields & criteria, save queries that
you utilize on a regular basis and set the query up so that it refreshes the
data each time the workbook is opened.

By default this is installed with MS Office.

Click DATA on the menu, select IMPORT EXTERNAL DATA and from the cascade
menu select NEW DATABASE QUERY. In the dialog box select MS Access as your
data source and click OK. The Query Wizard will step you through the process
of selecting the table or query you wish to use, the fields you want returned
and the criteria that you want to use.

 

[Guest]

Thanks. I will try that. Just out of curiosity, would I have to put a
MSquery in EACH cell of the spreadsheet that I want to return data.
Otherwise how will MSQuery know what cells to put in what data. Each cell
needs specific data and has row and columnar titles and is formatted with
borders. In other words, I wouldn't be returning data into a raw, blank
workbook.

 

[Guest]

You tell MS query what cell you want the data to start in and it places the
field heading in the first row and occupies as many rows as necessary based
upon the number of records returned. So if you're querying a 10 column table
and your returning all 10 columns and you designate cell A1 as the start cell
the fields will occupy columns A through J and x number of rows based upon
the resulting data set.

As for formatting, if you preformat the cells any row that exceeds the
formatted range will have to be formatted independently. I wouldn't
recommend preformatting all 65,000 rows, you'll use a lot of environment
space for nothing. You'll be better off formatting post import.