VBA: Insert worksheet from another file without opening it..

Posted on 2014-01-30
Last Modified: 2014-02-19
I am (with VBA code) generating an excel file based on copying a number of other sheets from other files. These files are large and takes time to open, and I am therefore looking for a way to have a specific sheet (for which I know the name) inserted into my file without having to open, select, copy and close the file.

Is there a "insert-from-file" command I can use?
Question by:PerMagnusStrom
LVL 29

Expert Comment

ID: 39820561
It is not clear what you want to do as see some redundancy in what you state:

First you mention VBA then

you say:
I am therefore looking for a way to have a specific sheet (for which I know the name) inserted into my file without having to open, select, copy and close the file.

Is this thru VBA ?? or manually ?

To answer straight out the question:
NO there is not a ["insert-from-file" command ]

but we can do this via VBA without you seeing the file that is being opened and smoothly.

If you are interested then start by posting 2 workbooks the one that in which will have the code and the one from which will read the data specifying which sheet and where you want it.

LVL 13

Expert Comment

ID: 39820804
It might be possible you're running out of RAM and slowing the process down.

Also, if the workbooks you're reading are networked or on an external drive, then copy them to your harddrive first, then open them, copy, and delete when done.
LVL 40
ID: 39821675
Unless you are ready to write very low level code that reads directly the sectors on the hard disk, you cannot have access to a file, not matter what type of file, if you do not open it in some way.

It does not have to be shown to the user (keep it invisible), but it needs to be opened.
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.


Author Comment

ID: 39832415
Hi again,.

thank for your input, but it is not what I am looking for.

"Importing" may be a more accurate term that "insert", as the whole idea is NOT having to open the file you want to get the data from. MS Access has this feature, where you can import data from a table in another database (without opening it..)

In Excel, you can import from Access, web, text files, and a number of other sources - but not from Excel itself.

Tips and hints are very much appreciated.
LVL 29

Expert Comment

ID: 39832429
In Excel, you can import from Access, web, text files, and a number of other sources - but not from Excel itself.

Who told you so ?
in VBA you can

Author Comment

ID: 39832433
ok, if so - i would be very happy to se a sample code snippet.. :)
LVL 29

Expert Comment

ID: 39832440
well it is like saying .. tech me chineese or ..

pls be specific what you want I can give you a script

Author Comment

ID: 39832488

I have 3 files (xlsx), File_A which contains financial statements, File_B which contains KPI's and File_C which contains a third type of report (not important). Each of these files contain 30 very similar sheets (one sheet for each department in an organization). The sheet name is always the department number (e.g. 7240).

I want to generate a new file, that consists of 3 sheets; one sheet from File_A (the sheet named 7240); one sheet from File_B (the one named 7240 - which may have to be renamed), and one sheet from File_C (again - the one named 7240)..

The whole point is not having to open File_A or File_B or File_C to get the specific sheet, as the files are very large and takes a lot of time to open.
LVL 29

Expert Comment

ID: 39832943
What do you mean by very large ? how many GB are we talking ?
Basically you cannot pull a sheet from a workbook without opening it.

But sorry let me ask you a simple question or else will be debating the sex of angels !!!
Are you familiar with VBA programing ? like you know how to write code that would manipulate data in Excel ??

If the answer is YES then I rest my case as presume you then know what your talking about and when you say: are very largo and take time and cannot open ... you know what your talking about.


You are not familiar with VBA then I will ask you to pls post a sample WB of these (unless very large data) and will take it from there.

Author Comment

ID: 39843966
hi again,

I am familiar with VBA, no problem. My VBA-routine takes about 5 hours to complete - much of the time is spent opening the large files...

In one of your first answers above, you say:
  (In Excel, you can import from Access, web, text files, and a number of other sources - but not from Excel itself.)

"Who told you so ?
in VBA you can
gowflow "

I little further down, you continue with:

"Basically you cannot pull a sheet from a workbook without opening it."

So I presume the latter is correct - a sheet cannot be imported to another file without opening it....?
LVL 29

Expert Comment

ID: 39844012
Well I hardly saw a routine that takes 5 hours !!!
If you need help we can troubleshoot your problem and you posting it beside this don't know what to tell you. You speak in general and have assumption and do not post code, sorry but our help is very limited.
LVL 21

Accepted Solution

Ejgil Hedegaard earned 500 total points
ID: 39844057
Even that Excel does not have an import function from other Excel files, it is possible to get data from closed files, using the standard link method to cells.
Not as flexible as from other sources, but possible.

The method is to recursively create formulas (VBA) with links to the sheets/cells in the closed files.
Then loop the ‘imported” result and insert the values needed into the new sheet.
When linking to individual cells, the range to get the data from must be known, or the range to retrieve from specified large enough.

I use this method to get data from app. 500 closed files.
Not a huge range in each file, app. 250 cells.
It takes less than a minute.

Sub GetDataFromFile(strPath As String, strFileName As String, strSheetName As String, lngRowMax As Long, intColMax As Integer)
    'strPath = Full path to the file, ends with \
    'strFileName = Filename incl. extension .xls, .xlsx or .xlsm
    'strSheetName = Name of the sheet with the data
    'lngRowMax = Max row to retrieve
    'intColMax = Max column to retrieve
    Dim wsDataSheet As Worksheet    'Sheet where the linking formulas are inserted
    Dim rgDataRange As Range        'Range with the linking formulas
    Dim strFormula As String           'Formula text
    Set wsDataSheet = Worksheets("Name of the sheet to hold the retrieved data")
    'Define the range for the formulas
    Set rgDataRange = wsDataSheet.Range(Cells(1, 1), Cells(lngRowMax, intColMax))
    'Create the link formula to get the values from the closed file, set an empty string if source cell is empty
    strFormula = "=If(Len('" + strPath + "[" + strFileName + "]" + strSheetName + "'!RC)>0,"
    strFormula = strFormula + "'" + strPath + "[" + strFileName + "]" + strSheetName + "'!RC,"""")"
    'Insert link formulas
    wsDataSheet.Range(rgDataRange.Address).FormulaR1C1 = strFormula
    'Then loop all cells in the retrieved range, and insert values that is not an empty string to the new sheet
    'Clean up link, removing the formulas
End Sub

Open in new window


Author Closing Comment

ID: 39869763
Thank, this is a good solution :)

Featured Post

ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question