Copying Data from closed workbooks
I'm attempting to write a macro that goes to a workbook
that's not open, copy data from it, and paste it into the workbook that is open. Is this possible? If so, how do I "get" to the workbook that's closed to copy data from it? Any help would be appreciated. Thanks, Kevin G p.s. - has anyone noticed any of their posts not displaying? I posted one yesterday that never showed up??? |
Copying Data from closed workbooks
Hi Kevin
See this Webpage from John Walkenbach http://j-walk.com/ss/excel/tips/tip82.htm But opening the workbook is in most cases faster with ScreenUpdating to false -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Kevin G" wrote in message ... I'm attempting to write a macro that goes to a workbook that's not open, copy data from it, and paste it into the workbook that is open. Is this possible? If so, how do I "get" to the workbook that's closed to copy data from it? Any help would be appreciated. Thanks, Kevin G p.s. - has anyone noticed any of their posts not displaying? I posted one yesterday that never showed up??? |
Copying Data from closed workbooks
You can do it with a range also but this is faster
This will open the file test.xls and copy the range in the Activeworkbook I use in both "Sheet1" as the Sheet name Sub test() Dim Wb1 As Workbook Dim Wb2 As Workbook Application.ScreenUpdating = False Set Wb1 = ActiveWorkbook Set Wb2 = Workbooks.Open("C:\test.xls") Wb2.Sheets("Sheet1").Range("a2:h600").Copy _ Wb1.Sheets("sheet1").Range("a1") Wb2.Close False Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Kevin G" wrote in message ... If I'm reading this right this will only get the value from one cell not a range. I'm trying to copy a range (a2:h600) and then paste it into an active workbook. If this isn't possible, I'll stop trying and just open the workbook to get it to work. KG -----Original Message----- Hi Kevin See this Webpage from John Walkenbach http://j-walk.com/ss/excel/tips/tip82.htm But opening the workbook is in most cases faster with ScreenUpdating to false -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Kevin G" wrote in message ... I'm attempting to write a macro that goes to a workbook that's not open, copy data from it, and paste it into the workbook that is open. Is this possible? If so, how do I "get" to the workbook that's closed to copy data from it? Any help would be appreciated. Thanks, Kevin G p.s. - has anyone noticed any of their posts not displaying? I posted one yesterday that never showed up??? . |
Copying Data from closed workbooks
Steve
Thanks for the code. It appeared to almost work. My guess is that the 'import table wizard' needs somehow to be invoked, so nothing ends up happening, cause of the 'non- .xls file type I guess? So I was not able to try your LASTROW function(?). Where the earlier code of Rons' opened an excel workbook and pasted in data; would there be a way to marry up your code with his to select the required file from a particular folder, the copy&paste in the data, in the case of excel files. Incidentally, with his code;" Range("a2:h600").Copy _ ", only 99 rows were copied in? Many Thanks Jon "steve" wrote in message ... Jon, #1. Can the name of the file to be opened be typed into a window on initiating the macro above. (I would like to be able to run this macro each week over a new update file). Better yet, you can use this code to initiate the Open file dialog and pick the file you want... Sub OpenMyFile() ' code "borrowed" from the ng Dim GetFiles As Variant Dim iFiles As Long Dim nFiles As Long GetFiles = Application.GetOpenFilename _ (FileFilter:="Text Files (*.txt),*.txt", _ Title:="Select Files To Open", MultiSelect:=True) If TypeName(GetFiles) = "Boolean" Then ''' GetFiles is False if GetOpenFileName is Canceled MsgBox "No Files Selected", vbOKOnly, "Nothing Selected" End Else ''' GetFiles is Array of Strings (File Names) ''' File names include Path nFiles = UBound(GetFiles) For iFiles = 1 To nFiles '' List Files in Immediate Window Debug.Print GetFiles(iFiles) Next End If End Sub You may have to play with (FileFilter:="Text Files (*.txt),*.txt", _ (I am not up on ascii type files) You may not even need that part. #2. Can the macro open an ascii file instead of an .xls file ? If so, the formatting would be consistent each time it is run. This is delimited, comma separated, with the second column being 'date - MDY'. The extension is .uut Once open you may automatically be put into the text wizard. Or you may have to use Text to Columns in the Data menu. You can record this part and make it part of your code. Also, could the whole table, in the ascii be copied? The number of records vary for each file, but the fields are the same. Than you can use Dim LASTROW As Long LASTROW = Cells(Rows.COUNT, "A").End(xlUp).Row to determine the number of records. Change "A" to the column that will always have the greatest number of records (if not all columns have data to the bottom of the file). Than Range(Cells(1, 1),Cells(LASTROW, 12)).Copy to copy. (this copies columns A to L from row 1 to Lastrow) steve "Jon Macmichael" wrote in message om... "Ron de Bruin" wrote in message ... You can do it with a range also but this is faster This will open the file test.xls and copy the range in the Activeworkbook I use in both "Sheet1" as the Sheet name Sub test() Dim Wb1 As Workbook Dim Wb2 As Workbook Application.ScreenUpdating = False Set Wb1 = ActiveWorkbook Set Wb2 = Workbooks.Open("C:\test.xls") Wb2.Sheets("Sheet1").Range("a2:h600").Copy _ Wb1.Sheets("sheet1").Range("a1") Wb2.Close False Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl <<<<< Are two further steps possible here; #1. Can the name of the file to be opened be typed into a window on initiating the macro above. (I would like to be able to run this macro each week over a new update file). #2. Can the macro open an ascii file instead of an .xls file ? If so, the formatting would be consistent each time it is run. This is delimited, comma separated, with the second column being 'date - MDY'. The extension is .uut Also, could the whole table, in the ascii be copied? The number of records vary for each file, but the fields are the same. Thanks Jon Macmichael |
Copying Data from closed workbooks
Jon,
from Ron's code add the following Sub test() Dim Wb1 As Workbook Dim Wb2 As Workbook Dim LASTROW as Long Application.ScreenUpdating = False Set Wb1 = ActiveWorkbook Set Wb2 = Workbooks.Open("C:\test.xls") LASTROW = Cells(Rows.COUNT, "A").End(xlUp).Row Wb2.Sheets("Sheet1").Range(Cells(2,1),Cells(LASTRO W,8)).Copy _ Wb1.Sheets("sheet1").Range("a1") Wb2.Close False Application.ScreenUpdating = True End Sub Should work. Post back if you still have problems. And you're Welcome! steve "Jon Macmichael" wrote in message om... Steve Thanks for the code. It appeared to almost work. My guess is that the 'import table wizard' needs somehow to be invoked, so nothing ends up happening, cause of the 'non- .xls file type I guess? So I was not able to try your LASTROW function(?). Where the earlier code of Rons' opened an excel workbook and pasted in data; would there be a way to marry up your code with his to select the required file from a particular folder, the copy&paste in the data, in the case of excel files. Incidentally, with his code;" Range("a2:h600").Copy _ ", only 99 rows were copied in? Many Thanks Jon "steve" wrote in message ... Jon, #1. Can the name of the file to be opened be typed into a window on initiating the macro above. (I would like to be able to run this macro each week over a new update file). Better yet, you can use this code to initiate the Open file dialog and pick the file you want... Sub OpenMyFile() ' code "borrowed" from the ng Dim GetFiles As Variant Dim iFiles As Long Dim nFiles As Long GetFiles = Application.GetOpenFilename _ (FileFilter:="Text Files (*.txt),*.txt", _ Title:="Select Files To Open", MultiSelect:=True) If TypeName(GetFiles) = "Boolean" Then ''' GetFiles is False if GetOpenFileName is Canceled MsgBox "No Files Selected", vbOKOnly, "Nothing Selected" End Else ''' GetFiles is Array of Strings (File Names) ''' File names include Path nFiles = UBound(GetFiles) For iFiles = 1 To nFiles '' List Files in Immediate Window Debug.Print GetFiles(iFiles) Next End If End Sub You may have to play with (FileFilter:="Text Files (*.txt),*.txt", _ (I am not up on ascii type files) You may not even need that part. #2. Can the macro open an ascii file instead of an .xls file ? If so, the formatting would be consistent each time it is run. This is delimited, comma separated, with the second column being 'date - MDY'. The extension is .uut Once open you may automatically be put into the text wizard. Or you may have to use Text to Columns in the Data menu. You can record this part and make it part of your code. Also, could the whole table, in the ascii be copied? The number of records vary for each file, but the fields are the same. Than you can use Dim LASTROW As Long LASTROW = Cells(Rows.COUNT, "A").End(xlUp).Row to determine the number of records. Change "A" to the column that will always have the greatest number of records (if not all columns have data to the bottom of the file). Than Range(Cells(1, 1),Cells(LASTROW, 12)).Copy to copy. (this copies columns A to L from row 1 to Lastrow) steve "Jon Macmichael" wrote in message om... "Ron de Bruin" wrote in message ... You can do it with a range also but this is faster This will open the file test.xls and copy the range in the Activeworkbook I use in both "Sheet1" as the Sheet name Sub test() Dim Wb1 As Workbook Dim Wb2 As Workbook Application.ScreenUpdating = False Set Wb1 = ActiveWorkbook Set Wb2 = Workbooks.Open("C:\test.xls") Wb2.Sheets("Sheet1").Range("a2:h600").Copy _ Wb1.Sheets("sheet1").Range("a1") Wb2.Close False Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl <<<<< Are two further steps possible here; #1. Can the name of the file to be opened be typed into a window on initiating the macro above. (I would like to be able to run this macro each week over a new update file). #2. Can the macro open an ascii file instead of an .xls file ? If so, the formatting would be consistent each time it is run. This is delimited, comma separated, with the second column being 'date - MDY'. The extension is .uut Also, could the whole table, in the ascii be copied? The number of records vary for each file, but the fields are the same. Thanks Jon Macmichael |
All times are GMT +1. The time now is 03:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com