Import from a workbook/worksheet

This macro imports data from a workbook/worksheet to a worksheet range:

Sub ImportRangeFromWB(SourceFile As String, SourceSheet As String, _
    SourceAddress As String, PasteValuesOnly As Boolean, _
    TargetWB As String, TargetWS As String, TargetAddress As String)
' Imports the data in Workbooks(SourceFile).Worksheets(SourceSheet).Range(SourceAddress)
' to Workbooks(TargetWB).Worksheets(TargetWS).Range(TargetAddress)
' Replaces existing data in Workbooks(TargetWB).Worksheets(TargetWS) 
' without prompting for confirmation
' Example     
' ImportRangeFromWB "C:\FolderName\TargetWB.xls", _
    "Sheet1", "A1:E21", True, _
    ThisWorkbook.Name, "ImportSheet", "A3"


Dim SourceWB As Workbook, SourceWS As String, SourceRange As Range
Dim TargetRange As Range, A As Integer, tString As String
Dim r As Long, c As Integer
    ' validate the input data if necessary
    If Dir(SourceFile) = "" Then Exit Sub ' SourceFile doesn't exist
    Set SourceWB = Workbooks.Open(SourceFile, True, True)
    Application.StatusBar = "Reading data from " & SourceFile
    Workbooks(TargetWB).Activate
    Worksheets(TargetWS).Activate
    
    ' perform import
    Set TargetRange = Range(TargetAddress).Cells(1, 1)
    Set SourceRange = SourceWB.Worksheets(SourceSheet).Range(SourceAddress)
    For A = 1 To SourceRange.Areas.Count
        SourceRange.Areas(A).Copy
        If PasteValuesOnly Then
            TargetRange.PasteSpecial xlPasteValues
            TargetRange.PasteSpecial xlPasteFormats
        Else
            TargetRange.PasteSpecial xlPasteAll
        End If
        Application.CutCopyMode = False
        If SourceRange.Areas.Count > 1 Then
            Set TargetRange = _
                TargetRange.Offset(SourceRange.Areas(A).Rows.Count, 0)
        End If
    Next A
    
    ' clean up
    Set SourceRange = Nothing
    Set TargetRange = Nothing
    Range(TargetAddress).Cells(1, 1).Select
    SourceWB.Close False
    Set SourceWB = Nothing
    Application.StatusBar = False
End Sub

Document last updated 1999-10-13 12:50:53 Printerfriendly version

User comments:

Ole P. from Norway wrote (2006-11-30 09:50:31 CET):

Re: Great !!! This is the exact code which i was looking for it works
You will have to open a workbook to read and/or update it.
Even if you don't see the workbook on the screen, the workbook file is opened every time you read or write to it.
See the last part of this page for a description on how to open a workbook so it will not be visible to the user.

Raj from chennai wrote (2006-11-22 14:51:51 CET):

Great !!! This is the exact code which i was looking for it works
Hi ,

Its great.Its working, This code i was searching for a long time.

Can any one help me in entering text into the closed workbook

Ole P. from Norway wrote (2006-09-07 17:22:51 CET):

Re: Importing Network Files
I haven't tried, but it should work, at least if you have mapped your network drives and assigned them a drive letter.

Valerie from NY wrote (2006-09-07 15:33:59 CET):

Importing Network Files
Would this work for files that are located on a network drive as well?

Luis A Castillo Chicas from Australia wrote (2005-07-01 01:29:16 CET):

Data extraction from timesheet system
This is great. I have set up a timesheet sytemem for a large department where is vital to track time allocation on project and programs. I am using excel application and need to extract data from timesheet in order to interface to an accounting package. I will try to use this macro to find how this work to put it in a separate workbook.

My e-mail address as follow: castillol@wva.org.au

Thanks.