VBA for data extraction

Question

With help from this forum I've previously set up a log file that extracts the 2nd row (only) of data from each source file to a log file.

But I would now like to extend this to include varying rows of data in the source files.

Assuming that:

The file to extract to is called 'Data extraction.xlsm'

The source files are variable in number but assume they're called 'Source1.xls': 'Source50.xls'

There are up to 50 columns in the source files, column hadings are being 'templated' at 1 to 50

Rows of data in these columns start in row 2 and can be any number of rows

I would like to be able to extract to row 2 onwards of  'Data extraction.xlsm' from each source files in turn such that if

Source1.xls had say 3 rows of data from row 2 to row 4 and

Source2.xls had 25 rows of data from row 2 to row 26

the vba would enable me to extract Source1.xls to rows 2 to 4 in 'Data extraction.xlsm' and

Source2.xls to rows 5 to 29 in 'Data extraction.xlsm' etc etc

Many thanks

Answer 1

I tried both macros (just copied and pasted from this post) and they ran without giving an error... I did terminate them after 10-12 files were processed.

If you can describe what you did to run this then we may be able to help you further.

Also try in a folder with 2-3 files only... also verify that you have files with extension xlsm. If you have xls files then you need to change the line

FilesPath = Dir(FolderPath & "*.xlsm")

to

FilesPath = Dir(FolderPath & "*.xls")

Answer 2

Thanks for this.

I've tried both, subsituting my folder name 'data extraction' for 'Source Folder' but I get an error message

'variable not defined' (for X) at:

For x = 1 To WB.Worksheets.Count

I've tried declaring it as 'Dim x as Variant' (Integer etc) but then the error message disappears but the code does nothing.

Answer 3

Ok,

1. i assume that all workbooks (source) are in one folder,
2. write the code below in a module in Target Workbook
3. change FolderPath as yours
4. export data in Worksheets(1)

Sub ExportALL()

Dim WB, myWB As Workbook

Dim myWS As Worksheet

Dim FolderPath As String, FilesPath As String

Dim v As Variant

Dim N As Long, LRow As Long, r As Long

Application.ScreenUpdating = False

On Error Resume Next

Set myWB = ThisWorkbook 'Target WB

Set myWS = myWB.Worksheets(1)

FolderPath = "C:\Source Folder"

FilesPath = Dir(FolderPath & "*.xls*")

N = 0

Do Until FilesPath = ""

N = N + 1

ReDim v(N)

v(N) = FilesPath

FilesPath = Dir()

If N = 0 Then Exit Sub

Set WB = Workbooks.Open(FolderPath & v(N))

For x = 1 To WB.Worksheets.Count

r = WB.Worksheets(x).Cells.Find(What:="*", SearchOrder:=xlByRows, _

SearchDirection:=xlPrevious).Row

WB.Worksheets(x).Rows("2:" & r).Copy

LRow = myWS.Cells(Rows.Count, 1).End(xlUp).Row

myWS.Cells(LRow, 1).Offset(1).PasteSpecial xlPasteValues

Application.CutCopyMode = False

Next x

WB.Close False

Loop

On Error GoTo 0

Application.ScreenUpdating = True

End Sub

[Edit]

try and this...

Sub ExportALL_2()

Dim WB, myWB As Workbook

Dim myWS As Worksheet

Dim FolderPath As String, FilesPath As String

Dim v As Variant

Dim N As Long, LRow As Long, r As Long

Dim rTo As Range

Application.ScreenUpdating = False

On Error Resume Next

Set myWB = ThisWorkbook 'Target WB

Set myWS = myWB.Worksheets(1)

FolderPath = "C:\Source Folder"

FilesPath = Dir(FolderPath & "*.xlsm")

Set rTo = myWS.Range("A2")

N = 0

Do Until FilesPath = ""

N = N + 1

ReDim v(N)

v(N) = FilesPath

FilesPath = Dir()

If N = 0 Then Exit Sub

Set WB = Workbooks.Open(FolderPath & v(N))

For x = 1 To WB.Worksheets.Count

r = WB.Worksheets(x).Cells.Find(What:="*", SearchOrder:=xlByRows, _

SearchDirection:=xlPrevious).Row

WB.Worksheets(x).Rows("2:" & r).Copy

rTo.PasteSpecial xlPasteValues

LRow = myWS.Cells.Find(What:="*", SearchOrder:=xlByRows, _

SearchDirection:=xlPrevious).Row

Set rTo = myWS.Cells(LRow + 1, 1)

Next x

Application.CutCopyMode = False

WB.Close False

Loop

On Error GoTo 0

Application.ScreenUpdating = True

End Sub

Answer 4

Thanks for your reply.

Yes, all the data from all the source files to be extracted in sequence to Data extraction.xlsm

Answer 5

Do you want to export data, from every workbook

and from every spreadsheet?