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:

Please Login or Register  to view this content.

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.

[TomHinkle]

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" wrote:

>
> 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.
>
>
> --
> mleone
> ------------------------------------------------------------------------
> mleone's Profile: http://www.excelforum.com/member.php...o&userid=24295
> View this thread: http://www.excelforum.com/showthread...hreadid=379003
>
>

[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.

Please Login or Register  to view this content.

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.

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

[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 the spreadsheet tabs.

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

[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.

[TomHinkle]

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..



"mleone" wrote:

>
> TomHinkle Wrote:
> > 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
> ------------------------------------------------------------------------
> mleone's Profile: http://www.excelforum.com/member.php...o&userid=24295
> View this thread: http://www.excelforum.com/showthread...hreadid=379003
>
>