CODE help - Copy data from closed workbook

[staticbob]

Hi Guys,

I have 7 workbooks I would like to copy data from. The data in question is a sheet called "Accident Book" in each of the 7 workbooks.

I know how to copy the data, but how do I reference these 7 closed workbooks ? They are all in the same directory, but these are other files in there that I dont need, so I dont just want to point to the directory.

I have this code from Nimrod but can't get it working.


Sub GetValuesFromAClosedWorkbook(fPath As String, fName As String, sName, cellRange As String)
With ActiveSheet.Range("A65536").End(xlUp)
.FormulaArray = "='" & "\\Bls36dc10\Work\H&S" & "\[" & Central 2004.xls & "]" _
& "Accident Book" & "'!" & range("A6")
.Value = .Value
End With
End Sub


I actually want to copy data from A6:N6 down to the last row entry, then paste this into the next available row in the activesheet. I need to do this from 7 different workbooks.

Cheers.

Bob


Bob

 

[staticbob]

bump !

 

[Ekim]

Bob,

Does it matter if your closed files are temporarily opened, data copied to a master file, then closed? All of this happens in the background i.e. you are not aware that the files are being opened then closed.

If this is OK, the following macro may work for you.

Macro assumptions:
All the closed files are in the same directory.
The directory holds many files, not all of which are to be consolidated.
Data to be copied is in a worksheet named “Accident Book”.

Macro setup:
In your destination file, name a worksheet as “MasterSheet” – data from the closed files will be copied to here.
Change the path of the source files (see the macro). Note: include the last backslash.
List the names of your source files – see the macro. For demo purposes, I have consolidated 5 files.
There are two spots in the macro where you have to specify the number of files – change to suit:
Dim Files(1 To 5) As String
For filenum = 1 To 5

The range to copy has been set as A6:N6.

Put the macros in a normal module.

Option Explicit
Sub ConsolFiles()

Dim wsMaster As Worksheet
Set wsMaster = Worksheets("Mastersheet")
Dim nextrow As Long

Dim Files(1 To 5) As String
   Files(1) = "Consol_Div1.xls"
   Files(2) = "Consol_Div2.xls"
   Files(3) = "Consol_Div3.xls"
   Files(4) = "Consol_Div4.xls"
   Files(5) = "Consol_Div5.xls"
   
Dim filepath As String
   filepath = "C:\Documents and Settings\"
Dim filenum As Integer

Application.ScreenUpdating = False

With wsMaster
   .Cells.Clear
End With
   
For filenum = 1 To 5
nextrow = wsMaster.UsedRange.Rows.Count + wsMaster.UsedRange.Row

Workbooks.Open (filepath & Files(filenum))
Worksheets(“Accident Book”).Range("A6:N6").Copy _
wsMaster.Cells(nextrow, 1)
   
Next

ThisWorkbook.Activate
Call CloseAll

Application.ScreenUpdating = True

End Sub


Sub CloseAll()
' Close all but the active workbook

 Dim wkbk As Workbook
  
 Application.ScreenUpdating = False

 For Each wkbk In Application.Workbooks
     If wkbk.Name <> ActiveWorkbook.Name Then
         wkbk.Close SaveChanges:=False  'or make it true
     End If
 Next
  
 Application.ScreenUpdating = True

End Sub

If you are interested, I can show you how to get Nimrod’s macro to work. Regrettably, I can only do this for one file – i.e. it doesn’t loop through multiple files.

HTH

Mike

 

[staticbob]

Cheers Mike, that'll do nicely.

Out of curiosity, what was wrong with the way I populated Nimrods code ? I'm sure the original code was good, I think I got a bit mixed up with all the "'s

Cheers
Bob

 

[DNS SHANGHAI]

Hi Ekim,
I tried your code and it seems to be working.
But how would I chaneg the code to to copy different ranges from different workbooks to the MAster?
I will be very pleased to hear from you on this.
Thanks

 

[Ekim]

Bob,

I have setup the following test data:
File name: DemoTest.xls
Location: C:\Temp
Data is in a worksheet named “Data”

The objective is to copy data from C5:C14 to a destination workbook.
The file is closed.

DemoTest.xls
	A	B	C	D	E	F
1	DIVISION1
2
3	AccountCode	Description	Jan-04	Feb-04	Mar-04	Apr-04
4
5	101	A	127	145	190	235
6	102	B	113	158	203	248
7	103	C	126	171	216	261
8	104	D	139	184	229	274
9	105	E	152	197	242	287
10	106	F	165	210	255	300
11	107	G	178	223	268	313
12	108	H	191	236	281	326
13	109	I	204	249	294	339
14	110	J	217	262	307	352
15	111	K	230	275	320	365
16	112	L	243	288	333	378
17	113	M	256	301	346	391
18	114	N	269	314	359	404
19	115	O	282	327	372	417
20	116	P	295	340	385	430
21
22			3,187	3,880	4,600	5,320
23
Data

In reference to Nimrod’s macro, I find it much easier to understand/edit if you use two macros – the first contains all the variables and calls the second macro. The second macro does all the work. Usually, you will not have to touch the second macro other than to change the sheet reference in the destination workbook and the location of where to copy the data (see notes at end)

Sub test()
GetValuesFromAClosedWorkbook "C:\Temp", "DemoTest.xls", _
       "Data", "C5:C14"
End Sub


Sub GetValuesFromAClosedWorkbook2(fPath As String, _
           fName As String, sName, cellRange As String)
 With Worksheets("Sheet1").Range(cellRange)
   .FormulaArray = "='" & fPath & "\[" & fName & "]" _
       & sName & "'!" & cellRange
   .Value = .Value
 End With
End Sub

Alternatively, the first macro could be re-written as:

Sub test()
Dim fPath As String
Dim fName As String
Dim sName As String
Dim cellRange As String

'Details of the closed workbook
fPath = "C:\Temp"
fName = "DemoTest.xls"
sName = "Data"       ‘ worksheet named “Data”
cellRange = "C5:C14"

GetValuesFromAClosedWorkbook fPath, fName, _
    sName, cellRange
End Sub

Notes:
See this line in the second macro:
Sub GetValuesFromAClosedWorkbook(fPath As String, _
fName As String, sName, cellRange As String)

“CellRange” refers to C5:C14 (first macro)

Now see this line in the second macro:
With Worksheets("Sheet1").Range(cellRange)

The second line copies the range, C5:C14, to the same rangein the destination workbook i.e. C5:C14. If you want to copy the range to say A1:A10 then you have to use Offset to maneuver the range:
With Worksheets("Sheet1").Range(cellRange).Offset(-4, -2)

i.e. move up 4 rows (from C5 to C1), then 2 columns to the left i.e. A1.

Time permitting, I will post a different way of getting data from a closed file.

HTH

Mike

 

[staticbob]

Mike,

Thanks for both solutions, and very clear descriptions and instructions ! I am trying to use your first solution, I am getting compile error on the bold row below, expected =

Also, is my method for selecting all the used rows on the sheets to copy from correct ?

And . . . I don't want to clear all the cells in the master sheet (Accident Book) as I have some field titles in there I need to remain.

Thanks again.
Bob

Option Explicit
Sub ConsolFiles()

Dim wsMaster As Worksheet
Set wsMaster = Worksheets("Accident Book")
Dim nextrow As Long

Dim Files(1 To 7) As String
Files(1) = "Central 2004.xls"
Files(2) = "Eastern 2004.xls"
Files(3) = "London 2004.xls"
Files(4) = "Midlands 2004.xls"
Files(5) = "North 2004.xls"
Files(6) = "South West 2004.xls"
Files(7) = "Southern 2004.xls"

Dim filepath As String
filepath = "\\bls36dc10\work\H&S\"
Dim filenum As Integer

Application.ScreenUpdating = False

With wsMaster
.Range("A6:N65536").Clear
End With

For filenum = 1 To 7
nextrow = wsMaster.UsedRange.Rows.Count + wsMaster.UsedRange.Row

Workbooks.Open (filepath & Files(filenum))
Worksheets("Accident Book").Range ("A6:N") & .Range("N65536").End(xlUp).Copy
wsMaster.Cells(nextrow, 1)
Next

ThisWorkbook.Activate
Call CloseAll

Application.ScreenUpdating = True

End Sub

 

[Ekim]

Bob,

The line “wsMaster.Cells(nextrow, 1)” is a continuation of the previous line, hence it needs to be connected with an underscore “_” after the Copy, so:

Worksheets("Accident Book").Range ("A6:N") & .Range("N65536").End(xlUp).Copy _
wsMaster.Cells(nextrow, 1)

See my previously posted macro. Splitting a long line of code into 2 lines, or more, is done purely for presentation purposes, but you need to use the underscore to “tell” VBA that the split lines are actually all one line.

Your method for selecting all the used rows on the sheets to copy gives me a compile error. This works for me on my dummy test files:

Worksheets("Accident Book").Range("A6:N" & Range("A" & Rows.Count).End(xlUp).Row).Copy _
wsMaster.Cells(nextrow, 1)

See my HTML exhibit posted above. For the first file, the above code copies the range A6:N20 - it stops at row 20 because there is no data in A21. The code then proceeds to the next file and copies A6:N (down to the first row where there is no data in column A) etc.

Post back if you have any further problems.

Regards,

Mike

 

[vheraldo]

Hi!
The very first code suggested works great!!!

BUT I have a hundred or so worksheets. I would not like to name all 100 worksheets in the code under FileName.

If all the files are within one directory, how can I change the code above to just run this against all excel files within the directory?

Please let me know!

This has been great help so far.

Thanks,
VHeraldo

 

[vheraldo]

Oh.. and I just realized that the first code copies the data from the several spreadsheets and pastes it into the same column but the next row down (Since the data to be copied is from A6:N6)

How would this code be changed so that it will copy the range E1:E75 from the several spreadsheets instead, and then paste it column by column into the MasterSheet?

I.e. in the MasterSheet, it will paste to A1:A75 (for file1), B1:B75 (for file2), C1:C75 (for file3) and so forth?

Thanks in advance for your help!!

VHeraldo