These pages are no longer updated and are only available for archive purposes.
Click here to visit the pages with updated information.
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
' 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
' perform import
Set TargetRange = Range(TargetAddress).Cells(1, 1)
Set SourceRange = SourceWB.Worksheets(SourceSheet).Range(SourceAddress)
For A = 1 To SourceRange.Areas.Count
If PasteValuesOnly Then
Application.CutCopyMode = False
If SourceRange.Areas.Count > 1 Then
Set TargetRange = _
' clean up
Set SourceRange = Nothing
Set TargetRange = Nothing
Set SourceWB = Nothing
Application.StatusBar = False
Document last updated 1999-10-13 12:50:53
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
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: email@example.com