Retreiving data from Excel spreadsheet through ADODB

[Roel]

Hi,

I am using the ADO to retreive data from one worksheet from one book and
into another book. I used sqSQL for the sheet search:

rsdata.Open szSQL, ...

where szSQL = "SELECT * FROM [Combined TG and TN$]"

The sheet name from the book is "Combined TG and TN". Is there any way
to format this so that this can be recognized? I know I should not be using
long names with spaces, but I am not allowed to change the name.


Thanks,

Roel

 

[onedaywhen]

What do you mean by 'reorganize'? You can alias the table name to make
you queries more concise e.g. if you wanted to select columns named
Col1 and Col2 from your 'Combined TG and TN' sheet:

SELECT T1.Col1, T1.Col2
FROM [Combined TG and TN$] AS T1
WHERE T1.Col1 > 100

BTW in the other workbook, are you creating a new sheet or appending
to an existing sheet?

To create a new sheet:

SELECT T1.Col1, T1.Col2
INTO [Excel 8.0;database=C:\MyOtherWorkbook.xls;].MyNewTable
FROM [Combined TG and TN$] AS T1

To append to existing sheet 'MyTable':

INSERT INTO [Excel 8.0;database=C:\MyOtherWorkbook.xls;].[MyTable$]
SELECT T1.Col1, T1.Col2
FROM [Combined TG and TN$] AS T1

 

[onedaywhen]

Did I totally misread the OP or what?! (brain is dehydrated today)

The syntax you require is (note the single quotes):

SELECT * FROM ['Combined TG and TN$']

--

What do you mean by 'reorganize'? You can alias the table name to make
you queries more concise e.g. if you wanted to select columns named
Col1 and Col2 from your 'Combined TG and TN' sheet:

SELECT T1.Col1, T1.Col2
FROM [Combined TG and TN$] AS T1
WHERE T1.Col1 > 100

BTW in the other workbook, are you creating a new sheet or appending
to an existing sheet?

To create a new sheet:

SELECT T1.Col1, T1.Col2
INTO [Excel 8.0;database=C:\MyOtherWorkbook.xls;].MyNewTable
FROM [Combined TG and TN$] AS T1

To append to existing sheet 'MyTable':

INSERT INTO [Excel 8.0;database=C:\MyOtherWorkbook.xls;].[MyTable$]
SELECT T1.Col1, T1.Col2
FROM [Combined TG and TN$] AS T1

--

Hi,

I am using the ADO to retreive data from one worksheet from one book and
into another book. I used sqSQL for the sheet search:

rsdata.Open szSQL, ...

where szSQL = "SELECT * FROM [Combined TG and TN$]"

The sheet name from the book is "Combined TG and TN". Is there any way
to format this so that this can be recognized? I know I should not be using
long names with spaces, but I am not allowed to change the name.


Thanks,

Roel