Extract data from many workbooks VBA

G

Guest

I finally figured out a way to extract data from all workbooks contained in
one folder. The data being extracted is composed in one column (column b x 26
rows), extracted from 50 files (one for each state). I need this data to be
put into a basebook as rows (transposed) so that for each state abbreviation,
all data will appear to the right of the state (the first row of column b is
the state abbreviation) . I know there is a way to pull in the data so that
it is showing 26 columns with all the data placed directly under these
columns (so 50 rows will be shown, one for each state). I just don't know how
to manipulate the vba accordingly.

As of now, it just pulls everything one block at a time, and now I have
50x26 rows...
Here is how I am pulling the data now:

Sub Example1()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim SourceRcount As Long
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\!Data\Data Collection"
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
basebook.Worksheets("Sheet1").Cells.Clear

rnum = 1

Do While FNames <> ""
Set mybook = Workbooks.Open(FNames, Password:="chris",
WriteResPassword:="chris", UpdateLinks:=0)
Set sourceRange = mybook.Worksheets("Please Complete
(Medical)").Range("C6:C31")
SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets("Sheet1").Cells(rnum, "A")

basebook.Worksheets("Sheet1").Cells(rnum, "D").Value = mybook.Name

sourceRange.Copy destrange

mybook.Close False
rnum = rnum + SourceRcount
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

Need only 50 rows.
Someone please help...!
 
R

Ron de Bruin

If you want to transpose use PasteSpeial with the last argument True

sourceRange.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False

And change rnum = rnum + SourceRcount to
rnum = rnum + 1

You can delete
SourceRcount = sourceRange.Rows.Count

If you need more help post back
 
G

Guest

I am receiving the message "PasteSpecial of Range Class failed" when I try to
run the module.

Any suggestions here?

Basicall I have one column of data that I need to pull from 50 workbooks (1
file x 50 states). Then I want the data that is being pulled to one workbook
and have it set up as a row for each state.
 
G

Guest

To better describe even further...

I have one column of data I need from 50 different files. I would like this
column to be as one row in one seperate workbook. So all together i will have
50 rows of data in this seperate workbook.

Column C <--- in 50 files (1 for each state)
State Abbrev
State Plan
Tier
Special Need
Co-Pay

Seperate Workbook:
Column A | B | C | D | E
1 State Abbrev State Plan Tier Special Need Co-Pay
2 " " " " " " " "
" "
50 State Abbrev State Plan Tier Special Need Co-Pay

Thanks for your help.
 
G

Guest

Thank you. That would be very useful.
Please let me know what the Title of the tutorial will be, and also when it
is posted.

Appreciate your help.
 
R

Ron de Bruin

Hi IntricateFool

This tester is working for me for all files in C:\Data

Sub Example1()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim SourceRcount As Long
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\Data"
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
basebook.Worksheets(1).Cells.Clear
'clear all cells on the first sheet
rnum = 1

Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)
Set sourceRange = mybook.Worksheets(1).Range("A1:A10")
Set destrange = basebook.Worksheets(1).Cells(rnum, "A")

sourceRange.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False

mybook.Close False
rnum = rnum + 1
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub
 
G

Guest

Thanks so much. Your have been of much help!

Ron de Bruin said:
Hi IntricateFool

This tester is working for me for all files in C:\Data

Sub Example1()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim SourceRcount As Long
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\Data"
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
basebook.Worksheets(1).Cells.Clear
'clear all cells on the first sheet
rnum = 1

Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)
Set sourceRange = mybook.Worksheets(1).Range("A1:A10")
Set destrange = basebook.Worksheets(1).Cells(rnum, "A")

sourceRange.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False

mybook.Close False
rnum = rnum + 1
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

CSE formulas in vba(ie: averageif in Excel 2003) 1
Copy Multiple Workbooks to Worksheet 2
Macro Help Needed: Compiling data from multiple sheets and then transposing it during the paste. 2
access closed workbook 2
copying worksheets 3
copy worksheets 6
Error Trap 2
Inserting Data: Multiple Sheets 1

Top