Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: show list of sheet into dir (2000 sr 1)

    Use this code in the Initialize event of the userform:

    Private Sub UserForm_Initialize()
    Const strPath = "C:MyDir"
    Dim strFile As String
    strFile = Dir(strPath & "*.xls")
    Do While Not strFile = ""
    Me.ListBox1.AddItem Left(strFile, Len(strFile) - 4)
    strFile = Dir
    Loop
    End Sub

    What exactly do you want to happen if the user selects a file? I'm not sure what you mean by "copy the selected sheet into wbook".

  2. #2
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: show list of sheet into dir (2000 sr 1)

    First tks for code.
    After i have clicked on a sheet present into list of userfrom, copy this into wbook and add into already sheet are present...

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: show list of sheet into dir (2000 sr 1)

    The list box will now display workbooks, not worksheets, so I don't understand what you mean by "and add into already sheet are present".

  4. #4
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: show list of sheet into dir (2000 sr 1)

    Hi Hans.... i am a stupid!
    The usefoirm show the name of wbook not the name of sheet!:-(
    Ok...
    After i have clicked on one wbook listed into userform copy the sheet of wbook clicked into wbook LISTA_SHEETY_INTO_USERFORM.xls.

    All wbook into dir MyDir contain only one sheet named: DARE_GLOB (EPUR)...

    Hope i am clear...

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    show list of wbook into dir (2000 sr 1)

    Messaggio cancellato da sal21

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: show list of sheet into dir (2000 sr 1)

    See attached version.

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: show list of sheet into dir (2000 sr 1)

    PERFECT!... good code.
    ... Is possible to delete the sheet imported into the event similar this:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    delete sheet without message of confirm...
    End Sub

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: show list of sheet into dir (2000 sr 1)

    Which sheet do you want to delete? The worksheet from the original file, or the imported worksheet in LISTA_SHEETS_INTO_USERFORM.xls?

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: show list of sheet into dir (2000 sr 1)

    the worksheet imported in LISTA_SHEETS_INTO_USERFORM.xls...

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: show list of sheet into dir (2000 sr 1)

    Why would you want to delete it if you have just imported it? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  11. #11
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: show list of sheet into dir (2000 sr 1)

    ... to be sure when i close the wbook, is cleared to the sheet imported, and when i reopen the wbook is cleared from old import of sheet.

    Other modify... is possible to import the new sheet from userform and delete the old imported sheet if existis?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: show list of sheet into dir (2000 sr 1)

    But the user can import several sheets, you'd have to keep track of which sheets have been imported. And what if the user renames the imported sheets? It could get very complicated.

  13. #13
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: show list of sheet into dir (2000 sr 1)

    The user not canot to rename the sheet! (i cut the hand!)
    The import is only for one sheet for process...

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: show list of sheet into dir (2000 sr 1)

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("DARE_GLOB (EPUR)").Delete
    Application.DisplayAlerts = True
    End Sub

  15. #15
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: show list of sheet into dir (2000 sr 1)

    PERFECT!....
    But when the macro go on this line:

    Set wbk = Workbooks.Open(strPath & Me.ListBox1)

    the wbook called from userform flashing for a moment is possible to hide this operation?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •