Nesting a sub procedure within another looping sub procedure

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:user:Documents: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?
 

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

Top