Access/VBA/Excel accessing first sheet in a workbook

[martinm]

Hi all, I need to convert someone else's Access VBA so that it opens the first sheet in an Excel spreadsheet, and not the named one.
The existing code:

Set ltb = db.CreateTableDef(strLink_name)

ltb.Connect = xConnection_string
ltb.SourceTableName = xTable   ' e.g. sheet1

I've tried replace xTable (string) with 1 (integer) with no success.

Next I tried (and various simialr approaches!) to get the sheet name to use in the original, with no joy. :

 Set objXL = New Excel.Application

 With objXL
  
   Set objWkb = .Workbooks.Open(aFileName)
   On Error Resume Next
     
   objWkb.Worksheets(1).Activate
   
   Set objSht = objWkb.ActiveSheet
   
   If Not Err.Number = 0 Then
     WSName = objSht.Name
   End If
   Err.Clear

I'm sure this is simple, but I can't find the solution anywhere!

Ta.

 

[SkipVought]

Hi,

 Set objXL = New Excel.Application

 With objXL
  
   On Error Resume Next

   Set objWkb = .Workbooks.Open(aFileName)

   If Err.Number = 0 Then
     WSName = objWkb.worksheet(1).Name
   Else
     Err.Clear   
   End If

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWise]

How About?

objWkb.Worksheets(1).Select

 

[martinm]

Thanks, I used SkipVought's, minor tweak in case anyone else uses this :

WSName = objWkb.worksheet(1).Name

Needs an 's' added!

WSName = objWkb.worksheetS(1).Name

 

[SkipVought]

SSorry!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}