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

[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

 

[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/


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[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!


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[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/


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Bryon Ford]

This works great.

Thanks,

Bryon

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!