How can I link a cerrtain workseet of an Excel file? 1

[nagyf]

I have an Excel table "C:\A\X Y.xls", a workseet on it "W Z".
How can I link it to my databse as table "T"?
I know that docmd.transferspresheet does it, if I have only one workseet in the table. How can I command that I need a a given worksheet (with spaces in its name)?
[tt]
Ferenc Nagy
|\ /~ ~~|~~~ nagyf@alpha0.iki.kfki.hu Fax: (36-1)-392-2529 New!
| \ | | Institute of Isotope and Surface Chemistry
| \ | -+- 1525 Bp. POB 77. Tel. 36-1)-392-2550
| \| |

http://alpha0.iki.kfki.hu/~nagyf

`-' ' `-' "The goal of the life is the struggle itself"
[/tt]

 

[scking]

You have an aversion to using the import wizard, have a need to do it in code, or don't have the wizard loaded? I simply trigger the wizard from code. It is a half-way means of accomplishing the task.


----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------

 

[VBslammer]

You just have to set the range argument to the sheet name (use $ after name):

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "T", "C:\A\X Y.xls", True, "W Z$"

*True arg is for field names
VBSlammer

 

[nagyf]

I tried
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "T", "C:\A\X Y.xls", True, "$[W Z]"


*Dollar ,opening brackett, worksheet name and closing brackett.*
Anyway where is it written in a help file for everybody?[/i] [tt]
Ferenc Nagy
|\ /~ ~~|~~~ nagyf@alpha0.iki.kfki.hu Fax: (36-1)-392-2529 New!
| \ | | Institute of Isotope and Surface Chemistry
| \ | -+- 1525 Bp. POB 77. Tel. 36-1)-392-2550
| \| |

http://alpha0.iki.kfki.hu/~nagyf

`-' ' `-' "The goal of the life is the struggle itself"
[/tt]

 

[VBslammer]

It works as shown in my previous post. There are several help references on how to address a worksheet range as an argument.

To address a sheet:

"sheetname

$

"

To address a sheet and cell range:

"sheetname!A1:B3"

The range argument will fail if the sheetname you use does not exist in the workbook.

If the sheet is named "X Y" then using "[X Y]" is invalid because that's not the name of the sheet.
VBSlammer

 

[nagyf]

Please help me to find the help topic where it is written. [tt]
Ferenc Nagy
|\ /~ ~~|~~~ nagyf@alpha0.iki.kfki.hu Fax: (36-1)-392-2529 New!
| \ | | Institute of Isotope and Surface Chemistry
| \ | -+- 1525 Bp. POB 77. Tel. 36-1)-392-2550
| \| |

http://alpha0.iki.kfki.hu/~nagyf

`-' ' `-' "The goal of the life is the struggle itself"
[/tt]

 

[VBslammer]

As you might be aware, not everything is well documented, or documented at all. I know from extensive work with Excel that the dollar sign trick works.

The online documentation says something like this:

If you have any special characters in the sheet name, enclose the name in single quotes, (not brackets):

"'my sheet'!"

However, using the dolloar sign to denote a sheet name is the only way this has worked for me.

"my sheet$"

The dollar sign trick appears in the help somewhere but I don't remember if it was in the knowledge base or MSDN. I know it works.
VBSlammer

 

[nagyf]

VBSlammer,

I have tried the cases

1. If there are special characters in the sheet name
then it must be enclosed in apstropes and followed by a dollar sign.

2. It it contains apostrophes, then they have to be doubled and then the result must be enclosed in apostrophes and followed by a dollar sign.

3. Otherwise it must be followed by a single dollar sign.

4. In case of ranges within a sheet excalamtion mark is required instead of dollar sign.
Examples
[tt]
Case Sheet name Range Parameter of the TransferSpreadsheet
1. A B whole 'A B'$
2. Hi ' $ whole 'Hi '' $'$
3. A1 whole A1$
4. Hi ' $ A1:C5 'Hi '' $'!A1:C5

[tt]

I have written functions
A. checking whether the worksheet name conatins only letters (including national accented characters) and number
B. Adding aphostrophes to the name as above.

Regards [tt]
Ferenc Nagy
|\ /~ ~~|~~~ nagyf@alpha0.iki.kfki.hu Fax: (36-1)-392-2529 New!
| \ | | Institute of Isotope and Surface Chemistry
| \ | -+- 1525 Bp. POB 77. Tel. 36-1)-392-2550
| \| |

http://alpha0.iki.kfki.hu/~nagyf

`-' ' `-' "The goal of the life is the struggle itself"
[/tt]