copy worksheet without opening the workbook

[Ben]

Hi all,

I would like ask if there's a way to copy a worksheet from a workbook A into
workbook B, without actually opening workbook A?

Thanks,

Ben

--

 

[john]

you can do it using formula.
try following:

Sub GetData()
Dim mydata As String
'data location & range to copy
mydata = "='C:\[NewBook.xls]Sheet1'!$B$2:F12" '<< change as required

'link to worksheet
With Worksheets(1).Range("B2:F12") '<< change as required
.Formula = mydata

'convert formula to text
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
End Sub

 

[JLGWhiz]

See this site:

http://www.rondebruin.nl/ado.htm