Exporting to a closed workbook

[Mikeice]

Hi Excel Gurus.

I have bought so many VBA books but can't find the solution.

What I need to do is export or import (whatever is best.)
a range from an open work book to a closed workbook.

Range a1 to I55

how can I do this?

Please give us some ideas and I will see if I can get my money out of
these books.

thx

 

[Guest]

You can Import from a closed wbk by using
Data>GetExternalData

I don't think you can Export from an open wbk to a "closed" wbk.
but, you can do it with vba in the blink of an eye:

Sub Export()
Dim sFile as String
Dim rSource as Range
Dim rDest as Range

Set rSource =ThisWorkbook.ActiveSheet.Range("A1:I55")

sFile = ThisWorkbook.Path & "\MyFile.xls" 'Adjust as needed
Workbooks.Open sFile
Set rDest = ActiveWorkbook.Sheets(1).Range("A1")
rSource.Copy rDest
ActiveWorkbook.Close True

End Sub


You should also add error handling in case the file can't be found
in the specified location.

 

[NickHK]

Mikeice,
ADO can work with closed workbooks, if it is straight data in a
database-like structure.

NickHK