Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    New Lounger
    Join Date
    Apr 2015
    Posts
    12
    Thanks
    15
    Thanked 0 Times in 0 Posts

    VBA to import all sheet

    Hi ,

    I am novice in VBA.. Need help on below below code.


    This code import only one sheet, How to tweak the code to import multiple sheet?

    Code:
    Sub Macro()Dim sImportFile As String, sFile As String
        Dim sThisBk                    As Workbook
        Dim vfilename                  As Variant
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Set sThisBk = ActiveWorkbook
        sImportFile = Application.GetOpenFilename( _
                      fileFilter:="Microsoft Excel Workbooks, *.xls; *.xml; *.xlsx", Title:="Open Workbook")
        If sImportFile = "False" Then
            MsgBox "No File Selected!"
            Exit Sub
    
    
        Else
            vfilename = Split(sImportFile, "\")
            sFile = vfilename(UBound(vfilename))
            With Application.Workbooks.Open(Filename:=sImportFile).Sheets(1)
                     .Copy After:=sThisBk.Sheets("Master File")
                .Parent.Close savechanges:=False
            End With
        End If
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
    
    
    End If
    End Sub

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Shiva,

    Welcome to the Lounge as a new poster!

    This should do the trick:

    Code:
    Option Explicit
    
    Sub ImportSheets()
    
        Dim sImportFile As String
        Dim wkbImport   As Workbook
        Dim wkbThisBk   As Workbook
        Dim vFilename   As Variant
        Dim iFileCntr   As Integer
        
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        
        Set wkbThisBk = ActiveWorkbook
        vFilename = Application.GetOpenFilename( _
                      FileFilter:="Microsoft Excel Workbooks, *.xls; *.xml; *.xlsx", Title:="Open Workbooks", _
                      MultiSelect:=True)
                      
        If vFilename = "False" Then
            MsgBox "No File Selected!"
            Exit Sub
    
        Else
            For iFileCntr = 1 To UBound(vFilename)
               Set wkbImport = Application.Workbooks.Open(Filename:=vFilename(iFileCntr))
               With wkbImport
                   .Sheets(1).Copy After:=wkbThisBk.Sheets("MasterFile")
                   .Close savechanges:=False
               End With
            Next iFileCntr
            
        End If
        
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
    
    End Sub   'ImportSheets()
    Note: You need to use the Ctrl key to Multi Select random files in the dialog box or to select a series click on the first one them hold Shift & click on the last one.

    HTH
    Last edited by RetiredGeek; 2015-04-29 at 12:08.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    shiva_reshs (2015-04-29)

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi RG

    Your code allows import from multiple files.
    Shiva asked
    This code import only one sheet, How to tweak the code to import multiple sheet?
    ..I thinks that means more than one sheet from the same file???
    IF(myMistake) THEN GoTo Sorry

    zeddy

  5. The Following User Says Thank You to zeddy For This Useful Post:

    shiva_reshs (2015-04-29)

  6. #4
    New Lounger
    Join Date
    Apr 2015
    Posts
    12
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Yes.. Sorry for confusion. I need to import multiple sheet from same file.

    Please advise.

    Thanks,
    Shiva

  7. #5
    New Lounger
    Join Date
    Apr 2015
    Posts
    12
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Below code works I guess, but it does not identify xml formate. I need to import tabs and selection can be any type of excel file.

    Code:
    Code:
    Private Sub Command9_Click()
       ' Requires reference to Microsoft Office 11.0 Object Library.
       Dim fDialog As FileDialog
       Dim varFile As Variant
    
       ' Clear listbox contents.
       'Me.FileList.RowSource = ""
    
       ' Set up the File Dialog.
       Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    
       With fDialog
    
          .AllowMultiSelect = False
          .Filters.Add "Excel File", "*.xls"
          .Filters.Add "Excel File", "*.xlsx"
    
          If .Show = True Then
    
             'Loop through each file selected and add it to our list box.
             For Each varFile In .SelectedItems
             ' Label3.Caption = varFile
    
             Const acImport = 0
             Const acSpreadsheetTypeExcel9 = 8
    
             ''This gets the sheets to new tables
             GetSheets varFile
    
             Next
             MsgBox ("Import data successful!")
             End If
    End With
    End Sub
    
    
    Sub GetSheets(strFileName)
       'Requires reference to the Microsoft Excel x.x Object Library
    
       Dim objXL As New Excel.Application
       Dim wkb As Excel.Workbook
       Dim wks As Object
    
       'objXL.Visible = True
    
       Set wkb = objXL.Workbooks.Open(strFileName)
    
       For Each wks In wkb.Worksheets
          DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
                wks.Name, strFileName, True, wks.Name & "$"
       Next
    
       'Tidy up
       wkb.Close
       Set wkb = Nothing
       objXL.Quit
       Set objXL = Nothing
    
    End Sub

  8. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Shiva,

    Gee, and I was so proud of my self...

    Ok, here's the next question...Do you want to import ALL sheets or only SELECTED sheets?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. The Following User Says Thank You to RetiredGeek For This Useful Post:

    shiva_reshs (2015-05-08)

  10. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Shiva
    ..modify the .Filters.Add to just one only as in:
    Code:
    .AllowMultiSelect = False
    .Filters.Add "Excel File", "*.xls*"
    
    If .Show = True Then
    RG: Post#1 title is "VBA to import all sheet"

    do I have to start my eye jokes again?

    zeddy

  11. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    No..No PLEASEEEEEEEE not THAT!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. The Following User Says Thank You to RetiredGeek For This Useful Post:

    shiva_reshs (2015-05-08)

  13. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    OK, a bit of Shakespeare then as punishment:

    =OR(B2, NOT(B2))

    zeddy

  14. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    To get us back on course...

    Hopefully this bit of code will do what the OP actually wanted.
    Code:
    Option Explicit
    
    Sub ImportSheets()
    
        Dim sImportFile As String
        Dim wkbImport   As Workbook
        Dim wkbThisBk   As Workbook
        Dim sht         As Worksheet
        Dim vFilename   As Variant
        Dim zFilter     As String
        
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        
        Set wkbThisBk = ActiveWorkbook
        zFilter = "Microsoft Excel Workbooks, *.xls; *.xml; *.xlsx"
        vFilename = Application.GetOpenFilename( _
                      FileFilter:=zFilter, Title:="Open Workbooks", _
                      MultiSelect:=False)
                      
        If vFilename = "False" Then
        
            MsgBox "No File Selected!"
            Exit Sub
    
        Else
          Set wkbImport = Application.Workbooks.Open(Filename:=vFilename)
               
          For Each sht In wkbImport.Sheets
             sht.Copy After:=wkbThisBk.Sheets("MasterFile")
          Next sht
               
          wkbImport.Close SaveChanges:=False
         
        End If
        
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
    
    End Sub   'ImportSheets()
    Note: I moved the file filter to a variable to make it easier to modify w/o messing with the rest of the file open code.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. The Following User Says Thank You to RetiredGeek For This Useful Post:

    shiva_reshs (2015-05-08)

  16. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi RG

    Back on track now.
    What about *.xlsb files in the file filter then??

    zeddy

  17. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    Now you're the one needing the specs. Did you not see the Note:? After all we need to leave something for the posters to do.

    For those who may be interested I decided to merge what I thought was being asked and what was actually being asked into one routine and found some interesting things along the way. Here's the code to merge all sheets from multiple workbooks.
    Code:
    Sub ImportSheets()
    
        Dim wkbImport   As Workbook
        Dim wkbThisBk   As Workbook
        Dim sht         As Worksheet
        Dim vFilename   As Variant
        Dim iFileCntr   As Integer
        
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        
        Set wkbThisBk = ActiveWorkbook
        vFilename = Application.GetOpenFilename( _
                      FileFilter:="Microsoft Excel Workbooks, *.xls; *.xml; *.xlsx", Title:="Open Workbooks", _
                      MultiSelect:=True)
                      
    Debug.Print VarType(vFilename)
                      
        If VarType(vFilename) = 8204 Then  '*** Array (8192) + Variant Array (12)! ***
    
            For iFileCntr = 1 To UBound(vFilename)
               Set wkbImport = Application.Workbooks.Open(Filename:=vFilename(iFileCntr))
               For Each sht In wkbImport.Sheets
                  sht.Copy After:=wkbThisBk.Sheets("MasterFile")
               Next sht
               
               wkbImport.Close savechanges:=False
               
            Next iFileCntr
        
        Else
        
          MsgBox "No File Selected!"
          Exit Sub
            
        End If
        
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
    
    End Sub   'ImportSheets()
    When using MultiSelect=True the GetOpenFilename function will return either a String, if Cancel is pressed, or an Variant Array, if 1 or more filenames are selected. Thus, to deterine what selection the user selected you can't do it with a straight test! You must test the TypeOf variant. In this case the test VarType(variant name) = 8204
    You use 8204 because, Array (8192) + Variant Array (12)!

    From MS documentation on the TypeOf() function:
    Value Variant type
    0 Empty (unitialized)
    1 Null (no valid data)
    2 Integer
    3 Long Integer
    4 Single
    5 Double
    6 Currency
    7 Date
    8 String
    9 Object
    10 Error value
    11 Boolean
    12 Variant (only used with arrays of variants)
    13 Data access object
    14 Decimal value
    17 Byte
    36 User Defined Type
    8192 Array

    Of course, this still leaves the question of...what if I want to select only some sheets? But, that's for another day!

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  18. The Following 2 Users Say Thank You to RetiredGeek For This Useful Post:

    shiva_reshs (2015-05-08),zeddy (2015-04-29)

  19. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi RG

    Now you're the one needing the specs
    ..we had a solar eclipse over here last month, and I stupidly looked at it through a colander.
    I think I strained my eyes.

    I liked your explanation and use of Arrays.
    It was Array("of", "sunshine")

    zeddy

  20. #14
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Based on the original post, why not simply

    Sub SelectFileGetAllSheetsSAS()
    Dim ds As String
    Dim fNameAndPath As Variant
    ds = ThisWorkbook.Name
    fNameAndPath = Application.GetOpenFilename(FileFilter:= _
    "Excel Files (*.XLS), *.XLS", Title:="Select File To Open")
    If fNameAndPath = False Then Exit Sub
    Workbooks.Open Filename:=fNameAndPath
    Sheets.Copy after:=Workbooks(ds).Sheets("Master File")
    End Sub
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  21. The Following User Says Thank You to Supershoe For This Useful Post:

    RetiredGeek (2015-04-29)

  22. #15
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Don,

    Thanks, I didn't realize you could use the Sheets as an aggregate. I'll tuck that one away for future use.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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
  •