copy worksheet

[v_gyku]

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

 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.

[RobDog888]

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

[Webtest]

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 ...

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

[Webtest]

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.

[RobDog888]

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.