Links from Access to Excel

[Guest]

Hi,

I have many Excel files that have the same information but in different
formats. I would like to consolidate all that information and I am using
Excel to do this. I just make some links (Example:
='C:\MyDocs\[Excelpivot.xls]Analysis'!$B$2) to all files and arrange them
into columns so I am able to make the pivot. This was working very well.

The problem is that the Excel file is getting too big (already 50Mb). I
would like to know if I can make links in Access to Excel just like I do in
Excel. I have tried the "Link Table" option but, as all files have different
formats, I am not being able to do so.

Thanks in advance.

Raphael

 

[Guest]

You are correct in using the "Link Table" option.

If by "different formats" you mean text, number, currency, etc. If that is
the case, try saving your spreadsheets as .csv or .txt file. It is easier to
control the field format when you link (or import) those file types.

If by "different formats" you mean the layout of the files are different
(information in different columns or rows) then try defining a named range.
In Access you can link to a worksheet in Excel or to a named range on a
spreadsheet.

HTH