G
gls2815
What I am trying to do is this: I am using a looping sub procedure to
go into a directory and write the name of each file as a heading into
the active sheet, structured like this:
Sub GetFileList()
Const nMACPATH As String = _
"volumes:userocuments:Bid folder:"
Dim nCount As Long
Dim sFName As String
#If Mac Then
sFName = Dir(nMACPATH, MacID("XLS8"))
#Else
sFName = Dir("*.xls")
#End If
Do While Len(sFName) > 0
Cells(4 + nCount, 2).Value = sFName
nCount = nCount + 6
sFName = Dir
Loop
End Sub
What I would like to do is call a second, non-looping version of a sub
procedure that J.E. helped me with, that pulls data from each file in
the count from the looping main sub procedure and then writes it in the
cells beneath its results. The procedure I have is this:
Sub GetPayroll()
Const sPATH As String = (handed off by the looping procedure)
Const sMSG1 As String = "A8"
Const sMSG2 As String = "B8"
Const sMSG3 As String = "C8"
Dim vArray1() As Variant
Dim vArray2() As Variant
Dim vArray3() As Variant
Dim wb As Workbook
Dim nCount1
Dim nCount2
Dim nCount3
Dim sFileName As String
Application.ScreenUpdating = False
sFileName = Dir(sPATH, MacID("XLS8"))
nCount1 = nCount1 + 1
nCount2 = nCount2 + 1
nCount3 = nCount3 + 1
ReDim Preserve vArray1(1 To nCount1)
ReDim Preserve vArray2(1 To nCount2)
ReDim Preserve vArray3(1 To nCount3)
Set wb = Workbooks.Open(sPATH & sFileName)
vArray1(nCount1) = wb.Worksheets("Labor
Detail").Range("A8").Value
vArray2(nCount2) = wb.Worksheets("Labor
Detail").Range("B8").Value
vArray3(nCount3) = wb.Worksheets("Labor
Detail").Range("C8").Value
wb.Close SaveChanges:=False
sFileName = Dir()
ActiveWorkbook.Sheets(1).Range("C5").Resize(nCount1, 1).Value =
_
Application.Transpose(vArray1)
ActiveWorkbook.Sheets(1).Range("D5").Resize(nCount2, 1).Value =
_
Application.Transpose(vArray2)
ActiveWorkbook.Sheets(1).Range("E5").Resize(nCount3, 1).Value =
_
Application.Transpose(vArray3)
Application.ScreenUpdating = True
End Sub
The problem is I can't find the proper syntax to make it work. Does
anyone have any suggestions?
go into a directory and write the name of each file as a heading into
the active sheet, structured like this:
Sub GetFileList()
Const nMACPATH As String = _
"volumes:userocuments:Bid folder:"
Dim nCount As Long
Dim sFName As String
#If Mac Then
sFName = Dir(nMACPATH, MacID("XLS8"))
#Else
sFName = Dir("*.xls")
#End If
Do While Len(sFName) > 0
Cells(4 + nCount, 2).Value = sFName
nCount = nCount + 6
sFName = Dir
Loop
End Sub
What I would like to do is call a second, non-looping version of a sub
procedure that J.E. helped me with, that pulls data from each file in
the count from the looping main sub procedure and then writes it in the
cells beneath its results. The procedure I have is this:
Sub GetPayroll()
Const sPATH As String = (handed off by the looping procedure)
Const sMSG1 As String = "A8"
Const sMSG2 As String = "B8"
Const sMSG3 As String = "C8"
Dim vArray1() As Variant
Dim vArray2() As Variant
Dim vArray3() As Variant
Dim wb As Workbook
Dim nCount1
Dim nCount2
Dim nCount3
Dim sFileName As String
Application.ScreenUpdating = False
sFileName = Dir(sPATH, MacID("XLS8"))
nCount1 = nCount1 + 1
nCount2 = nCount2 + 1
nCount3 = nCount3 + 1
ReDim Preserve vArray1(1 To nCount1)
ReDim Preserve vArray2(1 To nCount2)
ReDim Preserve vArray3(1 To nCount3)
Set wb = Workbooks.Open(sPATH & sFileName)
vArray1(nCount1) = wb.Worksheets("Labor
Detail").Range("A8").Value
vArray2(nCount2) = wb.Worksheets("Labor
Detail").Range("B8").Value
vArray3(nCount3) = wb.Worksheets("Labor
Detail").Range("C8").Value
wb.Close SaveChanges:=False
sFileName = Dir()
ActiveWorkbook.Sheets(1).Range("C5").Resize(nCount1, 1).Value =
_
Application.Transpose(vArray1)
ActiveWorkbook.Sheets(1).Range("D5").Resize(nCount2, 1).Value =
_
Application.Transpose(vArray2)
ActiveWorkbook.Sheets(1).Range("E5").Resize(nCount3, 1).Value =
_
Application.Transpose(vArray3)
Application.ScreenUpdating = True
End Sub
The problem is I can't find the proper syntax to make it work. Does
anyone have any suggestions?