- Code: Select all
Private Sub GetForeignData(ByRef Target As String, _
ByVal SourceCell As String, _
Optional ByVal SourceSheet As Variant)
Const SourceBookName As String = "D:\Alpha\Beta\The Other Workbook.xls"
Dim SourceBook As Workbook
Dim TargetSheet As Worksheet
Set TargetSheet = ActiveWorkbook.Sheets("Sheet1")
' You will never see that the workbook was opened:
Application.ScreenUpdating = False
Set SourceBook = Workbooks.Open(SourceBookName, ReadOnly:=True)
' Do your importing of data here, for example:
' Sourcesheet can be a number or a name, but if it is nothing ...
If VarType(SourceSheet) = vbError Then SourceSheet = 1
TargetSheet.Range(Target).Value = SourceBook _
.Worksheets(SourceSheet).Range(SourceCell).Value
' Close the SourceBook without asking to save changes:
SourceBook.Close Savechanges:=xlDoNotSaveChanges
' Make the changes in your worksheet visible:
Application.ScreenUpdating = True
End Sub
The other workbook must be closed at the time of calling this code. If it doesn't exist a fatal error will occur.
The workbook to be accessed is specified in the first line of code. Assign any suitable value to the Const SourceBookName. It must be a text string and it must be a full workbook name, including drive and path. The extension can be xlsx or xlsm if you are using Excel 2007 or newer.
The following two procedures show various ways of calling the above procedure. From an environment where the surrounding code was largely created by the macro recorder the methods demonstrated in this example will most likely be useful:
- Code: Select all
Private Sub Test_GetForeignData1()
' Fully referenced method:
' (Sequence of parameters is immaterial)
GetForeignData SourceSheet:="Sheet2", SourceCell:="B1", Target:="A1"
' Implied reference method:
' (Sequence of parameters must match sequence of arguments!)
GetForeignData "A1", "B1", "Sheet2"
' SourceSheet can be a name or a number
' IF the argument is omitted, Sheets(1) will be used
' IF the specified sheet doesn't exist a fatal error occurs
' Using a variable as parameters:
Range("A1").Select
GetForeignData Selection.Address, "B1"
GetForeignData ActiveCell.Address, "B1"
Range("A1:A10").Select
GetForeignData Selection.Cells(1).Address, "B1"
End Sub
The next example imports the data from 12 adjecent cells in the source workbook into 12 different cells in the active worksheet:
- Code: Select all
Private Sub Test_GetForeignData2()
Dim Target As String
Dim SourceCell As String
Dim SourceSheet As Variant
Dim SourceColumn As String
Dim R As Long
SourceSheet = "Monthly Report"
SourceColumn = "AC"
Range("A1:A12").Select
For R = 1 To 12
' Import from Range("AC1:AC12") :
SourceCell = Cells(R, SourceColumn).Address
GetForeignData Selection.Cells(R).Address, SourceCell, SourceSheet
Next R
End Sub