How to get the first sheet at EXCEL??

[pigpig504]

Dear all,

I'm using ADODB.recordset to get the data of EXCEL as follows:

RS1.Open "SELECT * FROM [Sheet1$A1:J4]"

But if the sheetname is renamed, I failed to get it.
Thus,I would like if I can get the 'absolute first sheet' name or how can I get the data at the first sheet without knowing the sheet name??

thx a lot!

 

[BrainB]

I'm not too clever with Access, but this might help :-

MySheet = Workbooks("MyBook.xls&quot.Worksheets(1).Name
RS1.Open "SELECT * FROM [" & MySheet & "$A1:J4]"


Regards
BrianB
Use CupOfCoffee to speed up all windows applications
================================

 

[xlbo]

hmmm - you will need to open excel to get that info.....

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[steveouthwaite]

Hi.

Xlbo is correct, you will need to open the workbook in excel, but you can do this from within access...

in the VBA screen, add a reference to excel (click on tools-->references,and put a tick against "microsoft excel 9 object libary"

you can then open the sheet and get the info as follows:




Dim xlapp As Excel.Application
dim firstsheetname as string

Set xlapp = New Excel.Application
xlapp.Workbooks.Open "C:\filename.xls"
firstsheetname = xlapp.activeworkbook.sheets(1).name
xlapp.activeworkbook.close
xlapp.quit
set xlapp = nothing




the firstsheetname variable can then be used in your select statement

Regards
SteveO

 

[PHV]

Just a though: why not grab directly the cells values while the workbook is open ?

 

[pigpig504]

Thx all...

But for some reasons.. I am not able to open the Excel file (workbook)....that's why I changed to use recordset method to open it.

e.g. There're some broken link (failed to update) in the workbook.. program would be idled and prompt me whether to edit the link or continue....(I've already tick the option of auto update link)

so.... that mean it is impossible for me to get the 'FIRST SHEET' without opening it, right?? =(

 

[PHV]

Have you tried this ?

xlapp.Workbooks.Open "C:\filename.xls", 0

i.e. UpdateLinks = No


Hope This Help
PH.