Trying to import data from many closed excel workbooks into one: Please Help :-)

B

BFord

Hi,

I have been working with this Macro, but I have some issues getting it
to work the way I would like.

I am attempting to do the following:

Creat a master spreadsheet that will collect data from many identical
spreadsheets all contained in one directory. Each spreadsheet in the
directory contains a summary sheet with one row that I need to bring
into the master spreadsheet in one single worksheet.

With this macro I am able to retrieve one cell just fine, but when I
try to specify to get data from A2 through J2 it brings back a total
into one cell in the master. Does anyone know how I can modify this
macro to bring in rows or many cells instead of just one?

Also if thier is a better way to do this I would be greatful for
suggestions.

Thanks in advance.....


Sub Import()
Dim FolderName As String, wbName As String, r As Long, cValue As
Variant
Dim wbList() As String, wbCount As Integer, i As Integer
Dim MyInput As String
MyInput = InputBox("Enter Directory Path To Your TE's I.E. D:\TE")
FolderName = MyInput
' create list of workbooks in foldername
wbCount = 0
wbName = Dir(FolderName & "\" & "*.xls")
While wbName <> ""
wbCount = wbCount + 1
ReDim Preserve wbList(1 To wbCount)
wbList(wbCount) = wbName
wbName = Dir
Wend
If wbCount = 0 Then Exit Sub
' get values from each workbook
r = 0
Workbooks.Add
For i = 1 To wbCount
r = r + 1
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Export
Main", "A2")
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Export
Main", "B2")
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Export
Main", "C2")
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Export
Main", "D2")
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Export
Main", "E2")
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Export
Main", "F2")
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Export
Main", "G2")
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Export
Main", "H2")
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Export
Main", "I2")
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Export
Main", "J2")
Cells(r, 1).Formula = wbList(i)
Cells(r, 2).Formula = cValue
Next i
End Sub

Private Function FinishImport(ByVal wbPath As String, _
wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
GetInfoFromClosedFile = ""
If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
If Dir(wbPath & "\" & wbName) = "" Then Exit Function
arg = "'" & wbPath & "[" & wbName & "]" & _
wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
On Error Resume Next
GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function
 
S

Shailesh Shah

Hi,

Try this,(Not tested)

Sub Import()
Dim FolderName As String, wbName As String, r As Long, cValue As Variant
Dim wbList() As String, wbCount As Integer, i As Integer
Dim MyInput As String, ci as integer
MyInput = InputBox("Enter Directory Path To Your TE's I.E. D:\TE")
FolderName = MyInput
' create list of workbooks in foldername
wbCount = 0
wbName = Dir(FolderName & "\" & "*.xls")
While wbName <> ""
wbCount = wbCount + 1
ReDim Preserve wbList(1 To wbCount)
wbList(wbCount) = wbName
wbName = Dir
Wend
If wbCount = 0 Then Exit Sub
' get values from each workbook
r = 0
Workbooks.Add



For i = 1 To wbCount
For ci = 1 To 10 'A to J
r = r + 1
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Export
Main", Left(Columns(ci).Address(, 0), 1) & "2")
Cells(r, 1).Formula = wbList(i)
Cells(r, 2).Formula = cValue
Next ci
Next i

End Sub

Private Function GetInfoFromClosedFile(ByVal wbPath As String, _
wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
GetInfoFromClosedFile = ""
If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
If Dir(wbPath & "\" & wbName) = "" Then Exit Function
arg = "'" & wbPath & "[" & wbName & "]" & _
wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
On Error Resume Next
GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function


Regards,
Shah Shailesh
http://members.lycos.co.uk/shahweb/
 
B

Bryon Ford

Many thanks for the help, but I think I am still missing something.
After making the suggested changes the macro will only post the workbook
names in duplicate in column A like below.
Feature1.xls
Feature1.xls
Feature1.xls
Feature1.xls
Feature1.xls
Feature1.xls
Feature1.xls
Feature1.xls
Feature1.xls
Feature1.xls
Feature2.xls
Feature2.xls
Feature2.xls
Feature2.xls
Feature2.xls
Feature2.xls
Feature2.xls
Feature2.xls
Feature2.xls
Feature2.xls

After the import it should look like this for example.

row from feature1.xls

Column A Column B ColumnC etc.
title ID quantity
FeatureXX 1234 10

Imported info into the master spreadsheet

Column A Column B ColumnC ColumnD etc.
Feature2.xls FeatureXX 1234 10

Thanks again for the help!
 
S

Shailesh Shah

Hi,

Try this,

This will get data from closed workbook as under :

Book1.xls data1,data2,data3,data4....data10
Book2.xls data1,data2,data3,data4....data10
Book3.xls data1,data2,data3,data4....data10
....
....
so on.

Also in your Original Post you have wrongly mentioned below function
name.

Private Function FinishImport

Change it to Private Function GetInfoFromClosedFile

to work.

Sub Import()
Dim FolderName As String, wbName As String, r As Long, cValue As
Variant
Dim wbList() As String, wbCount As Integer, i As Integer
Dim MyInput As String, ci As Integer
MyInput = InputBox("Enter Directory Path To Your TE's I.E. D:\TE")
FolderName = MyInput
' create list of workbooks in foldername
wbCount = 0
wbName = Dir(FolderName & "\" & "*.xls")
While wbName <> ""
wbCount = wbCount + 1
ReDim Preserve wbList(1 To wbCount)
wbList(wbCount) = wbName
wbName = Dir
Wend
If wbCount = 0 Then Exit Sub
' get values from each workbook
r = 0
Workbooks.Add


For i = 1 To wbCount
r = r + 1
Cells(r, 1).Formula = wbList(i)
For ci = 1 To 10 'A to J
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Pnl",
Left(Columns(ci).Address(, 0), 1) & "8")
Cells(r, ci + 1).Formula = cValue
Next ci
Next i
End Sub




Private Function GetInfoFromClosedFile(ByVal wbPath As String, _
wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
GetInfoFromClosedFile = ""
If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
If Dir(wbPath & "\" & wbName) = "" Then Exit Function
arg = "'" & wbPath & "[" & wbName & "]" & _
wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
On Error Resume Next
GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function



Regards,
Shah Shailesh
http://members.lycos.co.uk/shahweb/
 
J

jimjpoole

Joined
Nov 3, 2008
Messages
1
Reaction score
0
Hi.. I've been trying to use this vb code with some modifications to extract information from an array (E34:J37) rather than a column in each workbook but I'm not having much luck. Does anybody have any ideas?
 
T

timfu83

Joined
Jul 20, 2011
Messages
1
Reaction score
0
Hi, I'm trying to change the code, but without any sucess ...can someone help?
I need to copy the area J2:J25 from more files with Sheet "test" to one summary table - if it is possible transpose cell in column to row. Thanks a LOT!
 
F

fafa

Joined
Oct 11, 2012
Messages
1
Reaction score
0
Hi, I read all codes and tried too but they didn't give what I need. I have a file and the file has a lot of excel workboks as named 001, 002, etc and as same form and the file has a Master excel file which must collect data from other excel workbook. I wanna access that the master collect data from closed excel forms. I tried the codes above but i didn't manage it. Hope there is someone who can help me. thank you so much in advance for the help.



Shailesh Shah said:
Hi,

Try this,

This will get data from closed workbook as under :

Book1.xls data1,data2,data3,data4....data10
Book2.xls data1,data2,data3,data4....data10
Book3.xls data1,data2,data3,data4....data10
....
....
so on.

Also in your Original Post you have wrongly mentioned below function
name.

Private Function FinishImport

Change it to Private Function GetInfoFromClosedFile

to work.

Sub Import()
Dim FolderName As String, wbName As String, r As Long, cValue As
Variant
Dim wbList() As String, wbCount As Integer, i As Integer
Dim MyInput As String, ci As Integer
MyInput = InputBox("Enter Directory Path To Your TE's I.E. D:\TE")
FolderName = MyInput
' create list of workbooks in foldername
wbCount = 0
wbName = Dir(FolderName & "\" & "*.xls")
While wbName <> ""
wbCount = wbCount + 1
ReDim Preserve wbList(1 To wbCount)
wbList(wbCount) = wbName
wbName = Dir
Wend
If wbCount = 0 Then Exit Sub
' get values from each workbook
r = 0
Workbooks.Add


For i = 1 To wbCount
r = r + 1
Cells(r, 1).Formula = wbList(i)
For ci = 1 To 10 'A to J
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Pnl",
Left(Columns(ci).Address(, 0), 1) & "8")
Cells(r, ci + 1).Formula = cValue
Next ci
Next i
End Sub




Private Function GetInfoFromClosedFile(ByVal wbPath As String, _
wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
GetInfoFromClosedFile = ""
If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
If Dir(wbPath & "\" & wbName) = "" Then Exit Function
arg = "'" & wbPath & "[" & wbName & "]" & _
wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
On Error Resume Next
GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function



Regards,
Shah Shailesh
http://members.lycos.co.uk/shahweb/


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
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

Importing cell info from closed workbooks into new spreadsheet 1
named cell value from closed workbooks 1
Excel VBA - Iterative File Problem 1
How to sum value from master worksheet taking value from other close worksheet 1
One Workbook to Several Workbooks 6
import table access to excel 1
Problem with an amended macro. Please help! 4
Transfer Module1 from PC to Mac 1

Top