Excel VBA - Iterative File Problem

  • Thread starter Thread starter fcsmith1061
  • Start date Start date
F

fcsmith1061

I need to combine data from several Excel workbooks into one. Eac
workbook will only use one worksheet with data in two columns, neithe
column will have headers. The following code (which I found on th
Excel Tips tab of this site) works great, but only pulls the data fro
cell A1 and returns the workbook name, which I don't need.

Sub ReadDataFromAllWorkbooksInFolder()
Dim FolderName As String, wbName As String, r As Long, cValue A
Variant
Dim wbList() As String, wbCount As Integer, i As Integer
FolderName = "C:\ExcelTest"
' 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), "Sheet1"
"A1")
Cells(r, 1).Formula = wbList(i)
Cells(r, 2).Formula = cValue
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


How do I change the code to make it iterate through any existing row
in the closed workbooks and append them to the master file.

Any help would be greatly appreciated
 
How about just opening the workbooks, copying the data and pasting into the
master workbook?

fcsmith1061 < said:
I need to combine data from several Excel workbooks into one. Each
workbook will only use one worksheet with data in two columns, neither
column will have headers. The following code (which I found on the
Excel Tips tab of this site) works great, but only pulls the data from
cell A1 and returns the workbook name, which I don't need.

Sub ReadDataFromAllWorkbooksInFolder()
Dim FolderName As String, wbName As String, r As Long, cValue As
Variant
Dim wbList() As String, wbCount As Integer, i As Integer
FolderName = "C:\ExcelTest"
' 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), "Sheet1",
"A1")
Cells(r, 1).Formula = wbList(i)
Cells(r, 2).Formula = cValue
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

How do I change the code to make it iterate through any existing rows
in the closed workbooks and append them to the master file.

Any help would be greatly appreciated!
Click to expand...
 
Back
Top