Extracting multiple data from multiple files

[jose84]

Hi, im very new into macros, and I need help with this,

I have a lot of files which I need to take out data from, and need to put it into one single sheet (summary). Also, with the attached code puts the data in rows, I need to have them in columns.

Many thanks in advance,

I have this code:

Sub Extracting_Data()
Dim basebook As Workbook
Dim mybook As Workbook
Dim rnum As Long
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim Cnum As Integer
Dim cell As Range
SaveDriveDir = CurDir
MyPath = "D:\Documents and Settings\documents"
'file path
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
'clear all cells on the first sheet
basebook.Worksheets(1).Cells.Clear
rnum = 1
Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)
' This will add the workbook name in column A if you want
basebook.Worksheets(1).Cells(rnum, "A").Value = mybook.Name
' Copy the cell values from each cell in one row starting in column B
Cnum = 2
For Each cell In mybook.Worksheets(1).Range("C7:K30")
basebook.Worksheets(1).Cells(rnum, Cnum).Value = cell.Value
Cnum = Cnum + 1
Next cell
mybook.Close False
rnum = rnum + 1
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

 

[RichardMGreen]

What's the rest of the message?

 

[jose84]

I also get the message "File not found"

thanks,

 

[RichardMGreen]

You'll need to step through the code to find out what Fnames abnd mypath are being converted to in the code. Add a watch to them both and see if they give you what you expect.

If you get any error messages, post the entire message and the line of code it appeared on.

 

[jose84]

many thanks for the help

 

[RichardMGreen]

No problem.

Don't forget to post back on here if you need any more help.

 

[jose84]

Ok, having a look:

I got this message: error 1004, debug in this line:

basebook.Worksheets(1).Range("B65536").End(xlUp).Offset(offsetvalue).PasteSpecial (xlpastespecialvalues)

The macro opens the file, copy but then get stuk there,

 

[RichardMGreen]

What's the rest of the error message?

::edit::

Ignore that.
Change xlpastespecialvalues to xlpastevalues in the brackets at the end of the line.

 

[jose84]

Runtime: 1004
Pastespecial method of range class failed

refering to: debug line in:

basebook.Worksheets(1).Range("B65536").End(xlUp).Offset(offsetvalue).PasteSpecial (xlpastespecialvalues)

hope this helps,

 

[RichardMGreen]

Change xlpastespecialvalues to xlpastevalues in the brackets at the end of the line.

 

[jose84]

now it works, perfect, thank you very much,