Importing data from one workbook to another programmically

[mleone]

I have two Excel files, one has raw data the other one has all the
functions and formulas in it. I have a button on one of the worksheets
that opens an open file dialog and I can select the source workbook. I
only need a specific range and I don't need to format it or modify it
in any way. The range will also never change.

For instance: On WorkBook A, sheet1 from cells B2 to M9 I have data I
want to copy into WorkBook B, sheet3 cells B7 to M14. The code to
import the data will always reside on WorkBook B.

I need this summoned by a button click which I have, and I have so
far:

Code:
--------------------
Private Sub cmdDataOne_Click()
On Error GoTo foo

Dim InFilename As String

CommonDialog1.CancelError = True
CommonDialog1.DialogTitle = "Open File"
CommonDialog1.Filter = "Excel Files|*.xls|All Files |*.*"
CommonDialog1.Action = 1

InFilename = CommonDialog1.Filename

foo: If Err = 32755 Then Exit Sub

End Sub
--------------------


There are ways I can go about it (recordsets, etc), but it seems like
using a canon to kill a mosquito. I just need a simple copy and paste.
Any help would be apreciated.

 

[Guest]

dim wbSource as workbook ' Source workbook
dim wbTarget as workbook ' Target workbook

dim rngSource as range ' Source Range
dim rngTarget as range ' Target range

set wbTarget = Thisworkbook
set wbSource = workbooks.open ("filename")

set rngSource = wbSource.range("RangeName")
set rngTarget = wbTarget.worksheet("xxx").range("M4") ' whatever

rngsource.copy rngtarget
' if that doesn't work
rngsource.coppy rngTarget.cells(1,1)

 

[mleone]

dim wbSource as workbook ' Source workbook
dim wbTarget as workbook ' Target workbook

dim rngSource as range ' Source Range
dim rngTarget as range ' Target range

set wbTarget = Thisworkbook
set wbSource = workbooks.open ("filename")

set rngSource = wbSource.range("RangeName")
set rngTarget = wbTarget.worksheet("xxx").range("M4") ' whatever

rngsource.copy rngtarget
' if that doesn't work
rngsource.coppy rngTarget.cells(1,1)

Okay, I did all that and modified it to fit.


Code:
--------------------

Private Sub cmdDataOne_Click()
On Error GoTo foo

Dim InFilename As String ' Source Filename
Dim wbSource As Workbook ' Source Workbook
Dim wbTarget As Workbook ' Target Workbook
Dim rngSource As Range ' Source Range
Dim rngTarget As Range ' Target range

CommonDialog1.CancelError = True
CommonDialog1.DialogTitle = "Open File"
CommonDialog1.Filter = "Excel Files|*.xls|All Files |*.*"
CommonDialog1.Action = 1

InFilename = CommonDialog1.Filename

Set wbTarget = ThisWorkbook
Set wbSource = Workbooks.Open(InFilename)

'Set rngSource = wbSource.Range("B2:M9")
Set rngSource = wbSource.Worksheet("Magellan2 Sheet 1").Range("B2:M9")
Set rngTarget = wbTarget.Worksheet("Raw Data").Range("B7:M14")


rngSource.Copy rngTarget
'rngSource.Copy rngTarget.Cells(7, 2)


foo: If Err = 32755 Then Exit Sub

End Sub

--------------------


However, all it does is open the selected worksheet and highlight cell
F25. It doesn't copy or paste any data. I checked and all form names
are correct and I tried both copies.

 

[mleone]

BTW this is Excel 2002.
I didn't see any Workbook.Range()
I think that's why its not working

 

[Norie]

The code looks fine to me.

Have you checked you have the worksheet names correct?

By the way what do you mean by Workbook.Range().

As far as I know worksheets have ranges not workbooks.

 

[mleone]

set rngSource = wbSource.range("RangeName")

wbSource is a workbook, therefore it doesn't have a Range() function.

Yes, I copied and pasted the workbook names directly from th
spreadsheet tabs.

Also wbSource doesnt have a function called "Worksheet" but it ha
"WorkSheets". WorkSheets does not contain a range either

 

[Guest]

copying and pasting has to be EXACT, OR just paste to one cell (excel will
fill it out then appropriately)
I'd define the target range to be just one cell and try it..

 

[Norie]

Sorry the code is slightly wrong.

Worksheet should be Worksheets.

By the way why not use GetOpenFilename instead of the common dialog

 

[mleone]

Yep, that did it! Thanks