Copying data from a series of Excel files.

[ukdane]

I have an excel workbook, which I want to create some vba code that will copy some data into a worksheet in the workbook, in the "next available" row.

The data will be in the form of a number of excel workbooks, which will all be located in the same folder. They will all be called "FILExxx" where xxx is a number from 001 to 999. (There won't necessarily be 999 files located, but the files that are there will be named numerically, without gaps).

From what I can work out, I will need to create some code that will:

1) Count the number of files in the folder (thus knowing which files it should open; from 001 to 999).
2) Open each folder, remove the protection (all the worksheets will have the same password).
3) Locate the first row of data (again this will be the same on all the worksheets).
4) Locate the last row of data on the worksheet (this is variable).
5) Copy the data into the current worksheet- either in the first row if it is the first file (001) or the next available row.
6) Create a message when the last file has been imported.

I'm pretty sure this is all possible, as there are bits of it I've done before in an active workbook, but I've not worked with importing data by this means before.
Can anyone give me any pointers?

Thanks.

[lucas]

Why count them?

Data from every worksheet in every workbook in the folder?

[lucas]

Try this:

Option Explicit
Sub CombineSheetsFromAllFilesInADirectory()
'Uses methods found in http://vbaexpress.com/kb/getarticle.php?kb_id=151 and
' http://vbaexpress.com/kb/getarticle.php?kb_id=221
Dim Path As String 'string variable to hold the path to look through
Dim FileName As String 'temporary filename string variable
Dim tWB As Workbook 'temporary workbook (each in directory)
Dim tWS As Worksheet 'temporary worksheet variable
Dim mWB As Workbook 'master workbook
Dim aWS As Worksheet 'active sheet in master workbook
Dim RowCount As Long 'Rows used on master sheet
Dim uRange As Range 'usedrange for each temporary sheet
'***** Set folder to cycle through *****
Path = ThisWorkbook.Path & "\subdirectory\" 'Change as needed, ie "C:\"
Application.EnableEvents = False 'turn off events
Application.ScreenUpdating = False 'turn off screen updating
Set mWB = Workbooks.Add(1) 'create a new one-worksheet workbook
Set aWS = mWB.ActiveSheet 'set active sheet variable to only sheet in mWB
If Right(Path, 1) <> Application.PathSeparator Then 'if path doesnt end in "\"
Path = Path & Application.PathSeparator 'add "\"
End If
FileName = Dir(Path & "*.xls", vbNormal) 'set first file's name to filename variable
Do Until FileName = "" 'loop until all files have been parsed
If Path <> ThisWorkbook.Path Or FileName <> ThisWorkbook.Name Then
Set tWB = Workbooks.Open(FileName:=Path & FileName) 'open file, set to tWB variable
For Each tWS In tWB.Worksheets 'loop through each sheet
Set uRange = tWS.Range("A2", tWS.Cells(tWS.UsedRange.Row + tWS.UsedRange.Rows _
.Count - 1, tWS.UsedRange.Column + tWS.UsedRange.Columns.Count - 1)) 'set used range
If RowCount + uRange.Rows.Count > 65536 Then 'if the used range wont fit on the sheet
aWS.Columns.AutoFit 'autofit mostly-used worksheet's columns
Set aWS = mWB.Sheets.Add(After:=aWS) 'add a new sheet that will accommodate data
RowCount = 0 'reset RowCount variable
End If
If RowCount = 0 Then 'if working with a new sheet
aWS.Range("A1", aWS.Cells(1, uRange.Columns.Count)).Value = _
tWS.Range("A1", tWS.Cells(1, uRange.Columns.Count)).Value 'copy headers from tWS
RowCount = 1 'add one to rowcount
End If
aWS.Range("A" & RowCount + 1).Resize(uRange.Rows.Count, uRange.Columns.Count).Value _
= uRange.Value 'move data from temp sheet to data sheet
RowCount = RowCount + uRange.Rows.Count 'increase rowcount accordingly
Next 'tWS
tWB.Close False 'close temporary workbook without saving
End If
FileName = Dir() 'set next file's name to FileName variable
Loop
aWS.Columns.AutoFit 'autofit columns on last data sheet
mWB.Sheets(1).Select 'select first data sheet on master workbook
Columns("G:G").Select
Selection.NumberFormat = "d/m/yyyy"
Range("F16").Select
Application.EnableEvents = True 're-enable events
Application.ScreenUpdating = True 'turn screen updating back on
'Clear memory of the object variables
Set tWB = Nothing
Set tWS = Nothing
Set mWB = Nothing
Set aWS = Nothing
Set uRange = Nothing
End Sub

[ukdane]

Thanks Lucas,
I tried the code you provided above.

Here are a few comments.

1) If I inititate the code in a file "SOP Master" (using a button that runs the Macro) the data is not added to the master sheet, but instead to a new sheet.

2) I need the code to look for a specific worksheet name, and only import the data from that worksheet.

3) I need to generate a report of the workbook name if the workbook doesn't contain any data.

4) I need the data to be placed at a specific point on the "SOP Master" folder, of which it needs to look up three rows. If a match is found it needs to overwrite the existing data, and add the days date.
If a match is not found, then it just needs to add the data to the last row.

5) Finally I need to add autofilters for certain columns on the Master file.

Again, any pointers are more than welcome.

Edit: At this point, I think I only need help with points 2 and 4.

Thanks

[ukdane]

Update:

Problem 1.
I need the code to look for a specific worksheet name, and only import the data from that worksheet.

Can anyone help with this?

Problem 2.
As posted earlier, I also need the data to be imported one line at a time, to check if the data already exists, and overwrite the existing data if it does, or add it to a new row if it doesn't.
I need the data to match from three different columns.

I think I can see a way to do this, if someone can shed light on problem 1.

Thanks

[ukdane]

I've solved problem 1, by adding an IF statement, after the For that will look for the relevant file name.

Problem 1 solved :-)

[ukdane]

I'm now working on problem 4)
I need the data to be placed at a specific point on the "SOP Master" folder, of which it needs to look up three rows. If a match is found it needs to overwrite the existing data, and add the days date.
If a match is not found, then it just needs to add the data to the last row.

(I think I've solved all the others)

So for problem 4 I now have two tables. A data table, and a results table.
The data table is located on one worksheet, and fills a range from B2:ANxxx where xxx is variable.

The resultstable is located in another worksheet. The results table fills a range from B5:ANyyy where yyy is variable.

I need to take each item from the table data, and look to see if the contents of field B, E, and F match those of any of the data found on the results table.

If it is a complete match with all three items, then it needs to replace the data in the results table.

If the data does not match any of the results, then it needs to be added as the last item on the results table.

This needs to be repeated for each row in the data table. And it needs to be checked with each item in the results table.



I'm not sure how to approach this, but maybe the three columns that need to be checked with each other (B, E, and F in each sheet) could be added together, and placed in an array that is the size of the relevant table (-1 as arrays start at 0).

Then I could run a loop that checks each row in the array in the data table with each row in the array in the results table.


Any ideas how best to accomplish this?

[Gil]

What does the following line taken from above 'set first file's name to filename variable.

FileName = Dir(Path & "*.xls", vbNormal) 'set first file's name to filename variable

Does it mean I have to change the filename literally & if so should the code look like this

FileName = Dir(Path & "d:\documents and settings\Desktop\MDF Log\filename.xls", vbNormal)

I guess not because I can't step past that line
Sorry if I am way out as I am a novice

[mdmackillop]

FileName = Dir(Path & "*.xls", vbNormal)

* is a WildCard character. Filename will be the first file in the Folder defined by the path which is an xls file. If FileName = "" then no files were found.

Check out Dir in VBA Help, as it occurs frequently in VBA procedures.