[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5364
  • Last Modified:

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

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?
0
PerMagnusStrom
Asked:
PerMagnusStrom
1 Solution
 
gowflowCommented:
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.

gowflow
0
 
RyanProject Engineer, ElectricalCommented:
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.
0
 
Jacques Bourgeois (James Burger)Commented:
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.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
PerMagnusStromAuthor Commented:
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.
0
 
gowflowCommented:
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
0
 
PerMagnusStromAuthor Commented:
ok, if so - i would be very happy to se a sample code snippet.. :)
0
 
gowflowCommented:
well it is like saying .. tech me chineese or ..

pls be specific what you want I can give you a script
gowflow
0
 
PerMagnusStromAuthor Commented:
Ok:

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.
0
 
gowflowCommented:
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.

IF

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.
gowflpwo
0
 
PerMagnusStromAuthor Commented:
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....?
0
 
gowflowCommented:
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.
gowflow
0
 
Ejgil HedegaardCommented:
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")
    wsDataSheet.Select
    '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
    wsDataSheet.Cells.ClearContents
End Sub

Open in new window

0
 
PerMagnusStromAuthor Commented:
Thank, this is a good solution :)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now