Solved

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

Posted on 2014-01-30
13
3,919 Views
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?
0
Comment
Question by:PerMagnusStrom
13 Comments
 
LVL 29

Expert Comment

by:gowflow
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.

gowflow
0
 
LVL 13

Expert Comment

by:MrBullwinkle
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.
0
 
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.
0
 

Author Comment

by:PerMagnusStrom
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.
0
 
LVL 29

Expert Comment

by:gowflow
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
gowflow
0
 

Author Comment

by:PerMagnusStrom
ID: 39832433
ok, if so - i would be very happy to se a sample code snippet.. :)
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 29

Expert Comment

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

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

Author Comment

by:PerMagnusStrom
ID: 39832488
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
 
LVL 29

Expert Comment

by:gowflow
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.

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
 

Author Comment

by:PerMagnusStrom
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....?
0
 
LVL 29

Expert Comment

by:gowflow
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.
gowflow
0
 
LVL 21

Accepted Solution

by:
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")
    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
 

Author Closing Comment

by:PerMagnusStrom
ID: 39869763
Thank, this is a good solution :)
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now