Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing data from one workbook to another programmically
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing data from one workbook to another programmically
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing data from one workbook to another programmically
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing data from one workbook to another programmically
BTW this is Excel 2002. I didn't see any Workbook.Range() I think that's why its not working -- mleon ----------------------------------------------------------------------- mleone's Profile: http://www.excelforum.com/member.php...fo&userid=2429 View this thread: http://www.excelforum.com/showthread.php?threadid=37900 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing data from one workbook to another programmically
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 Wrote: BTW this is Excel 2002. I didn't see any Workbook.Range() I think that's why its not working. -- Norie ------------------------------------------------------------------------ Norie's Profile: http://www.excelforum.com/member.php...o&userid=19362 View this thread: http://www.excelforum.com/showthread...hreadid=379003 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing data from one workbook to another programmically
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 -- mleon ----------------------------------------------------------------------- mleone's Profile: http://www.excelforum.com/member.php...fo&userid=2429 View this thread: http://www.excelforum.com/showthread.php?threadid=37900 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing data from one workbook to another programmically
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing data from one workbook to another programmically
Sorry the code is slightly wrong. Worksheet should be Worksheets. By the way why not use GetOpenFilename instead of the common dialog -- Nori ----------------------------------------------------------------------- Norie's Profile: http://www.excelforum.com/member.php...fo&userid=1936 View this thread: http://www.excelforum.com/showthread.php?threadid=37900 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing data from one workbook to another programmically
Yep, that did it! Thanks -- mleon ----------------------------------------------------------------------- mleone's Profile: http://www.excelforum.com/member.php...fo&userid=2429 View this thread: http://www.excelforum.com/showthread.php?threadid=37900 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
importing data into a workbook | Excel Worksheet Functions | |||
Importing data from a different workbook | Excel Worksheet Functions | |||
Importing Data from unopened Workbook into an open Workbook | Excel Discussion (Misc queries) | |||
Importing data from another workbook | Excel Programming | |||
Edit Data Query Programmically? | Excel Programming |