Copy of an Excel link

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ivan - 26 Feb 2007 09:24 GMT

Hi,

there is an excel file with a lot of named sheets on a server on our local
net. In my access dbm I linked the first

Since I have to create links to all the sheets and since creating a lot of
links is not a pleasant job I tried first to copy link (i.e. linked table)
and then renamed the second (linked) table with the name of second sheet. Of
course, only to rename is not enouhg. Since the renamed table still remained
to point to the first sheet, I tried to change the pointer with Linked Table
Manager (LTM) but with no success. Is LTM so clumsy or am I of that sort?

Beside that I looked into the system table MSySObject and I found out that
for second table only ForeignName should be changed (Connect, Database,
Name, ... are OK).

Is there any way, direct or throuhg code, to update the field ForeignName in
the system table MSySObject?

Thanks in advance

Reply to this Message

John Nurick - 27 Feb 2007 20:29 GMT

Hi Ivan,

Rather than hack the system tables, I'd replace the linked tables with
queries using this syntax:

SELECT * FROM
[Excel 8.0;HDR=Yes;database=C:\TEMP\File.xls;].[SheetName$]
;

Use HDR=No if the first row doesn't contain column headers. If you
create one query like that, you can then make multiple copies with
different names and open them in SQL view to change the worksheet name.
Or you can write VBA code to do it automatically.

>Hi,
>
[quoted text clipped - 16 lines]
>
>Thanks in advance

--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.

Reply to this Message

Ivan - 28 Feb 2007 12:00 GMT

Thank you, John, to remind me of this way

Ivan

> Hi Ivan,
>
[quoted text clipped - 39 lines]
>
> Please respond in the newsgroup and not by email.

Reply to this Message