+ Reply to Thread
Results 1 to 5 of 5

VBA code to browse for external excel file and extract its all sheet to current workbook

Hybrid View

mchilapur VBA code to browse for... 02-11-2016, 03:33 AM
MarvinP Re: VBA code to browse for... 02-11-2016, 03:55 AM
mchilapur Re: VBA code to browse for... 02-11-2016, 03:58 AM
Catman50 Re: VBA code to browse for... 02-11-2016, 04:03 AM
mchilapur Re: VBA code to browse for... 02-11-2016, 04:05 AM
  1. #1
    Forum Contributor
    Join Date
    09-20-2014
    Location
    India
    MS-Off Ver
    2013
    Posts
    275

    Lightbulb VBA code to browse for external excel file and extract its all sheet to current workbook

    Hello all,
    I need VBA code to search for a workbook and move its all sheets into current workbook by inserting them last.
    I want format to be same as that of original file.

    Below is the code i have developed but fails to meet my intent and returns error.

    Please suggest me to with correct code.
    Sub ImportData()
    
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim Sh As Worksheet
    Dim i As Integer
    
    
    'Dim PasteStart As Range
    
    Set wb1 = ActiveWorkbook
    i = wb1.Worksheets.Count
    'Set PasteStart = [Report!A1]
    
    FileToOpen = Application.GetOpenFilename _
    (Title:="Please choose a Report to Parse")
    
    If FileToOpen = False Then
        MsgBox "No File Specified.", vbExclamation, "ERROR"
        Exit Sub
    Else
        Set wb2 = Workbooks.Open(Filename:=FileToOpen)
    
    
    For Each Sh In wb2.Sheets
          'ActiveSheet.Select
       Sh.Copy After:=Workbooks(wb1).Sheets(i)
     Next Sh
    
    End If
        wb2.Close
    
    End Sub

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,451

    Re: VBA code to browse for external excel file and extract its all sheet to current workbo

    Hi,

    I like to use Application.Findfile Method as it gives a dialog and I can then select the one I want.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Contributor
    Join Date
    09-20-2014
    Location
    India
    MS-Off Ver
    2013
    Posts
    275

    Re: VBA code to browse for external excel file and extract its all sheet to current workbo

    FileToOpen = Application.GetOpenFilename _
    (Title:="Please choose a Report to Parse")
    Does the same thing...I dont have any issue with browsing a file..I have facing error while extracting sheet.

  4. #4
    Registered User
    Join Date
    04-22-2013
    Location
    Philippines & Australia
    MS-Off Ver
    Excel 2016, Office 365
    Posts
    73

    Re: VBA code to browse for external excel file and extract its all sheet to current workbo

    This amendment works for me

    Sub ImportData()
        Dim wb1
        Dim wb2
        Dim Sh As Worksheet
        Dim i As Integer
        '
        wb1 = ActiveWorkbook.Name
        i = ActiveWorkbook.Sheets.Count
        FileToOpen = Application.GetOpenFilename _
        (Title:="Please choose a Report to Parse")
        '
            If FileToOpen = False Then
                MsgBox "No File Specified.", vbExclamation, "ERROR"
                Exit Sub
            Else
                Workbooks.Open (FileToOpen)
                wb2 = ActiveWorkbook.Name
            For Each Sh In Workbooks(wb2).Sheets
               Sh.Copy After:=Workbooks(wb1).Sheets(i)
             Next Sh
        End If
            Workbooks(wb2).Close
    End Sub

  5. #5
    Forum Contributor
    Join Date
    09-20-2014
    Location
    India
    MS-Off Ver
    2013
    Posts
    275

    Re: VBA code to browse for external excel file and extract its all sheet to current workbo

    Thanks..Works fine..

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Extract text from an external Excel using the file path only
    By gaker10 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-17-2015, 12:30 PM
  2. Replies: 3
    Last Post: 12-18-2014, 10:27 AM
  3. [SOLVED] Marco to import csv from browse location and rename current sheet without spaces or dashes
    By gutterball in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-23-2014, 09:42 AM
  4. Replies: 3
    Last Post: 12-04-2013, 09:59 PM
  5. [SOLVED] Copying data from external workbook into current workbook based on cell value
    By Brontosaurus in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-07-2012, 11:17 AM
  6. [SOLVED] copy from external file, paste in current workbook
    By rodich in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-16-2012, 04:29 AM
  7. [SOLVED] Browse for file name code? (Excel 2002 VBA)
    By kp2900@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-27-2006, 08:45 AM

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