Copy of an Excel link

[Ivan]

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

 

[John Nurick]

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.

 

[Ivan]

Thank you, John, to remind me of this way

Ivan

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,

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