+ Reply to Thread
Results 1 to 6 of 6

import sheets of name selected

  1. #1
    Forum Contributor
    Join Date
    06-02-2005
    Location
    India
    MS-Off Ver
    2007
    Posts
    138

    Question import sheets of name selected

    import sheets


    i need to import sheets of following names from "MYProject.xls" in my new workbook.

    RULES, CONDITIONS, ROUTING, ZONES.


    Only check above sheet names in MyProject.xls and import in new wrkbk. If not found any then display its name in msgbox.

  2. #2
    Forum Contributor
    Join Date
    06-02-2005
    Location
    India
    MS-Off Ver
    2007
    Posts
    138

    Question

    Is my question is cleared??

  3. #3
    Tom Ogilvy
    Guest

    Re: import sheets of name selected

    Assume MyProject.xls is open

    Dim v as Variant
    Dim s as String
    Dim s1 as String
    Dim iloc as Long
    s = "#RULES#CONDITIONS#ROUTING#ZONES#"
    s1 = s
    Redim v(0 to 0)
    for each sh in Workbooks("Myproject.xls").Worksheets
    iloc = instr(1,"#" & sh.Name & "#",s,vbTextCompare)
    if iloc <> 0 then
    v(ubound(v)) = sh.Name
    s = Replace(s,sh.Name & "#","")
    redim Preserve v(0 to ubound(v) + 1)
    end if
    Next
    if len(s) < s1 then
    Redim preserve v(0 to ubound(v)-1)
    workbooks("Myproject.xls").Worksheets(v).copy
    End if
    if len(s) > 1 then
    s = Right(s,len(s)-1)
    s = Replace(s,"#",vbCr)
    msgbox "Sheets not found: " & vbCr & s
    End If

    --
    Regards,
    Tom Ogilvy

    "ilyaskazi" <[email protected]> wrote
    in message news:[email protected]...
    >
    > import sheets
    >
    >
    > i need to import sheets of following names from "MYProject.xls" in my
    > new workbook.
    >
    > RULES, CONDITIONS, ROUTING, ZONES.
    >
    >
    > Only check above sheet names in MyProject.xls and import in new wrkbk.
    > If not found any then display its name in msgbox.
    >
    >
    > --
    > ilyaskazi
    > ------------------------------------------------------------------------
    > ilyaskazi's Profile:

    http://www.excelforum.com/member.php...o&userid=23969
    > View this thread: http://www.excelforum.com/showthread...hreadid=376676
    >




  4. #4
    Patrick Molloy
    Guest

    RE: import sheets of name selected

    Option Explicit

    Public Sub GetSheets()

    Dim wbP As Workbook
    Dim ws As Worksheet
    Dim MySheets() As Variant
    Dim index As Long
    MySheets = Array("RULES", "CONDITIONS", "ROUTING", "ZONES")
    Set wbP = Workbooks.Open("C:\MyProject.xls")

    For index = LBound(MySheets, 1) To UBound(MySheets, 1)

    Set ws = wbP.Worksheets(MySheets(index))

    ws.Copy ThisWorkbook.Sheets(1)


    Next


    wbP.Close False



    "ilyaskazi" wrote:

    >
    > import sheets
    >
    >
    > i need to import sheets of following names from "MYProject.xls" in my
    > new workbook.
    >
    > RULES, CONDITIONS, ROUTING, ZONES.
    >
    >
    > Only check above sheet names in MyProject.xls and import in new wrkbk.
    > If not found any then display its name in msgbox.
    >
    >
    > --
    > ilyaskazi
    > ------------------------------------------------------------------------
    > ilyaskazi's Profile: http://www.excelforum.com/member.php...o&userid=23969
    > View this thread: http://www.excelforum.com/showthread...hreadid=376676
    >
    >


  5. #5
    Forum Contributor
    Join Date
    06-02-2005
    Location
    India
    MS-Off Ver
    2007
    Posts
    138

    Exclamation

    Tom,

    I was not able to get any output from your code, bcoz it gives me debug messages. Whereas..


    Patrick,
    Your code was successful to import sheets only if array containing names matches with workbook sheet names.

    But what about if array containing names does not found any in workbook???
    Suppose workbook does not contain sheets which are required to import, then display msgbox with its sheet-name not found.

    Plz check the same and let me know.
    Last edited by ilyaskazi; 06-07-2005 at 08:00 AM.

  6. #6
    Tom Ogilvy
    Guest

    Re: import sheets of name selected

    Here is a slight revision that worked perfectly for me, but if you want to
    wait for Patrick, knock yourself out.

    Sub abc()
    Dim v As Variant
    Dim s As String
    Dim s1 As String
    Dim iloc As Long
    s = "#RULES#CONDITIONS#ROUTING#ZONES#"
    s1 = s
    ReDim v(0 To 0)
    For Each sh In Workbooks("Myproject.xls").Worksheets
    iloc = InStr(1, s, "#" & sh.Name & "#", vbTextCompare)
    If iloc <> 0 Then
    v(UBound(v)) = sh.Name
    s = Replace(s, UCase(sh.Name) & "#", "")
    ReDim Preserve v(0 To UBound(v) + 1)
    End If
    Next
    If Len(s) < Len(s1) Then
    ReDim Preserve v(0 To UBound(v) - 1)
    Workbooks("Myproject.xls").Worksheets(v).Copy
    End If
    If Len(s) > 1 Then
    s = Right(s, Len(s) - 1)
    s = Replace(s, "#", vbCr)
    MsgBox "Sheets not found: " & vbCr & s
    End If

    End Sub

    --
    Regards,
    Tom Ogilvy

    "ilyaskazi" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Tom,
    >
    > I was not able to get any output from your code, bcoz it gives me debug
    > messages. Whereas..
    >
    >
    > Patrick,
    > Your code was successful to import sheets only if array containing
    > names matches with workbook sheet names.
    >
    > But what about if array containing names does not found any in
    > workbook???
    > Suppose workbook does not contain sheets which are required to import,
    > then display msgbox with its sheet-name not found.
    >
    > Plz check the same and let me know.
    >
    >
    > --
    > ilyaskazi
    > ------------------------------------------------------------------------
    > ilyaskazi's Profile:

    http://www.excelforum.com/member.php...o&userid=23969
    > View this thread: http://www.excelforum.com/showthread...hreadid=376676
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1