PDA

View Full Version : SOLVED: Open a workbook and copy data into another



primaryteach
12-12-2008, 09:49 AM
Can any one help with the following?

I need a macro that can open another workbook, select a given sheet and copy range of data (e.g. A2:AK20000 - yep it's big!!), then paste only the values (i.e. not the formulas) back into my original main file. The data would be pasted into cell A20001 and then sorted according to column A. This way I can add another file's data, as long as I never exceed the 20000 row.

I have seen a macro that can do this if you have typed the exact pathname/filename into a cell, but this a few issues I want to avoid:

1) Having to know the exact pathname, as errors might occur when typed.
2) Because the main file will import the data from each file in a different location / different computers, pathnames will have to be typed each time - hence not very user friendly.

I know a small bit of vba, but you might as well treat me as a newbie!

If you can help I would be really grateful.

Cheers

Simon
UK Primary School Teacher

mdmackillop
12-12-2008, 10:23 AM
Hi Simon,
Record a macro doing exactly what you have described. That is a good starting point. Post the result and we can look at tidying it up.
Regards
MD

primaryteach
12-12-2008, 10:48 AM
Sorry, my basic macro is as follows. I haven't included the sorting the data.

Sub Opensesame()
'
' Opensesame Macro
' Macro recorded 12/12/2008 by Simon R
'

'
Workbooks.Open Filename:= _
"C:\Documents and Settings\Simon\My Documents\smaller file.xls"
Range("A11:AK20000").Select
Selection.Copy
Windows("Main file.xls").Activate
Range("A21000").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.ScrollRow = 1
Range("A10").Select
Application.CutCopyMode = False
Windows("smaller file.xls").Activate
ActiveWindow.Close
End Sub

mdmackillop
12-12-2008, 12:02 PM
Give this a try
Option Explicit

Sub Opensesame()
Dim wsTgt As Worksheet
Dim wbSource As Workbook

Application.ScreenUpdating = False
Set wsTgt = ActiveSheet
Set wbSource = Workbooks.Open(Filename:= _
"C:\Documents and Settings\Simon\My Documents\smaller file.xls")
wbSource.Sheets(1).Range("A11:AK20000").Copy '< is sheet correct?
wsTgt.Range("A21000").PasteSpecial Paste:=xlValues
wbSource.Close False
wsTgt.Range("A10").Select
Application.ScreenUpdating = True
End Sub

primaryteach
12-12-2008, 12:16 PM
This does tidy up my original code, thank you, but I still have the problem from my original post, i.e. the macro code identifies a specific file, rather than letting me choose which file I want to source my data from (the location and filename will vary each time I want to use it). Is that possible?

Also, is there a way of getting rid of the message about the 'clipboard having a large amount of data'?

Thanks again,

Simon

mdmackillop
12-12-2008, 12:42 PM
Option Explicit
Sub Opensesame()
Dim wsTgt As Worksheet
Dim wbSource

Application.ScreenUpdating = False
Set wsTgt = ActiveSheet

wbSource = Application.Dialogs(xlDialogOpen).Show
ActiveSheet.Range("A11:AK20000").Copy
wsTgt.Range("A21000").PasteSpecial Paste:=xlValues
Application.DisplayAlerts = False
ActiveWorkbook.Close False
Application.DisplayAlerts = True
wsTgt.Range("A10").Select
Application.ScreenUpdating = True

End Sub

lucas
12-12-2008, 12:50 PM
Sub Opensesame


That's funny Simon.......I'm borrowing it if you don't mind.

primaryteach
12-12-2008, 02:35 PM
mdmackillop (http://www.vbaexpress.com/forum/member.php?u=87) a real big thank you. I have adjusted the code to include the sort and so far so good.

I have one last issue, if I cancel on the open workbook window it closes down the master file without saving. How does the code need to change to make sure it returns the user back to the master file?

Simon

P.S. Lucas you are more than welcome!

mdmackillop
12-12-2008, 02:44 PM
Add the second line here
wbSource = Application.Dialogs(xlDialogOpen).Show
If wbSource = False Then Exit Sub

primaryteach
12-12-2008, 03:54 PM
Brilliant. Thank you for your help.