copy worksheet

I want to add all the worsheets of selected workbook to a listbox.but without opening a workbook.

i have done it in the following way

Code:

---


 Set Wkb = Workbooks.Open(fileName:=mypath)
        For Each ws In Wkb.Worksheets

        SheetList.Add ws.name & ".xls"
 Next
 n = SheetList.Count
    For i = 1 To n
  UserControl.List1.AddItem SheetList(i)
    Next i

---

but here excel opens that workbook.still thios is ok.
but when a wbook having password is opened it asks mw for password.I dont want that.
I want to send a sheet directly without opening it.

Also i am saying activesheet.copy -- if i am not opening a workbook how will i copy a worksheet which i have selected from the listbox.

You can not copy a sheet out of a workbook without opening it. No way around it.

I'm no pro, but here are some comments ...

I think you have to open the workbook, but you can make it invisible! The following may help you ...

Code:

---

Option Explicit
Sub Macro1()
    Dim abook As Workbook
    'Open an existing workbook - you'll need error handling if it is already open!
    Workbooks.Open Filename:="C:\Documents and Settings\guawd1\Desktop\JUNK-TABS-1.xls"
    'The book you just opened is now the Active Workbook - set a handle for it
    Set abook = ActiveWorkbook
    'MsgBox abook.Windows.Count
    'Make the book disappear!
    abook.Windows(1).Visible = False
    'Access something out of the hidden book
    MsgBox abook.Sheets(1).Name & " | " & abook.Sheets(1).Range("A1").Value
    'Close the hidden book
    Application.DisplayAlerts = False
    abook.Close
    Application.DisplayAlerts = True
End Sub

---

I think you can extract information out of a closed book ... I extracted information into Excel from an Access database without opening the database, but that was a long time ago.

Hey! I just found some old notes. I used DAO. I also found some notes on "Import data from a closed workbook (ADO)". Unfortunately the link to the document seems to be dead now (edc.bizhosting.com/english/adodao.htm). I have a printed copy of it. It's about 50 lines ... too much to type right now ... Try this link:
http://www.mrexcel.com/archive2/30100/34328.htm

P.S. I read the article and it seems like you can only access the data from the first sheet or from a named range in the workbook. You can not access sheets or sheet names with the macro.

Yes, you can only copy a workbook without opening by using ADO that I have done before but its still going to give you an issue if its got a password protected workbook. I feel its easier to keep it all in Excel and use the Sheets(1).Copy method as its a complete copy where the ADO and/or DAO methods will not copy formatting, formulas, etc.