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

Posted on 2014-01-30
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 31

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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


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 31

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 31

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 31

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 31

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 23

Accepted Solution

Ejgil Hedegaard earned 2000 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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
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…

752 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