Categorised Menu List

Importing Excel Sheets

Importing Sheets to Current Workbook (ThisWorkBook)

Explanation:

  • On error: clause refers to - if there exists any error in execution, Interpreter will transfer the control to error handler labelled errHandler: (followed by Colon) as per notation.
  • Two workbooks are declared: one for the existing or target work book and the other for source work book (from where the data is getting copied)
  • Check if the target work book has atleast two sheets, because, the code is written to copy data from the source file after the second work sheet.
  • Application.GetOpenFileName is to get the name(s) of the file(s) that need to be imported to the current work book (target workbook)

This opens a FILE OPEN dialog box,

  1. to select one or more files,
  2. Cancel button can be operated upon, in which case, interpreter passes control to error handler, to display message.

Rest of the code is self explanatory - a process of copying sheets from source work book to the current work book after second work sheet in target sheet. 

I have written as a seprate procedure, please copy code and call the procedure (Excel Usage).

Sample Code:

gallery/importingsheetstocurrentwb