Open an existing workbook and reference it
 

Hey all,
I have a template that needs to prompt the user to open an existing workbook
and then be able to reference that workbook's worksheets in order to copy
various columns from worksheets in the template over to the existing workbook
that was just opened.
Can anyone offer the best way to do this?

Open an existing workbook and reference it
 

My preference is to use workbook, worksheet and range objects to access data
in external workbooks. Something like this...

Sub GetStuff()
Dim wbkOpened As Workbook
Dim wksExternalSheet As Worksheet
Dim rngExternalRange As Range

On Error GoTo OpenFileError
Set wbkOpened = Workbooks("This.xls")
On Error GoTo 0
Set wksExternalSheet = wbkOpened.Sheets(1)
Set rngExternalRange = wksExternalSheet.Range("A1:B10")

rngExternalRange.Copy ThisWorkbook.Sheets("Sheet1").Range("A1")

Exit Sub
OpenFileError:
Workbooks.Open "C:\This.xls"
Set wbkOpened = Workbooks("This.xls")
Resume Next
End Sub

This copies a range from a workbook called this which may or may not be open
when the code executes. it copes some stuff from the first sheet back into
Thisworkbook...
--
HTH...

Jim Thomlinson


"TimT" wrote:

Hey all,
I have a template that needs to prompt the user to open an existing workbook
and then be able to reference that workbook's worksheets in order to copy
various columns from worksheets in the template over to the existing workbook
that was just opened.
Can anyone offer the best way to do this?