J
Jim May
Would someone familiar with MAC's look over the below code, and if
You can quickly see a FLAW - please point-it-out!! -- Originally
Written on my PC (xl2003) -- I thought I had successfully made the
"modifications" to make it work - As I step through it in VBE (Using
Step-thru-Mode - It only stops on 5 or 6 lines before reaching the
End Sub. In step mode it reaches and passes the line sName = Dir(sPath)
And I then from the immediate window ? sName (return),, but Nothing,,
Confused here, Note I have commented out UserForm reference that
usable
Only on the PC Version..
Any assistance appreciated,,
Code follows:
Option Explicit
Option Base 1
Sub ExtractDataFromFiles()
Const sPath = "Macintosh HD:CashApp:WIPContracts:"
Dim sName As String
Dim wb As Workbook
Dim j As Integer
Dim k As Integer
Dim n As Integer
Dim r(1 To 14) As Variant
Dim p(1 To 48) As Variant
ActiveSheet.Range("A6:BJ2000").ClearContents
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
sName = Dir(sPath)
j = 6 ' Data starts on Row 6
'UserForm1.Show vbModeless
Do While sName <> ""
Set wb = Workbooks.Open(sPath & sName, UpdateLinks:=0)
With wb.Worksheets("Cost Analysis")
r(1) = .Range("CaPaNum").Value
r(2) = .Range("CaPurName").Value
r(3) = .Range("CaPurCitySt").Value
r(4) = .Range("CaLocName").Value
r(5) = Left(.Range("CaLocCitySt").Value, Len(.Range("CaLocCitySt")) - 2)
r(6) = Right(.Range("CaLocCitySt").Value, 2) 'Pull State only
r(7) = .Range("CaCsStat").Value
r(8) = .Range("CaEqCost").Value
r(9) = .Range("CaInCost").Value
r(10) = .Range("CaComm").Value
r(11) = .Range("CaTotCost").Value
r(12) = .Range("CaEqSale").Value
r(13) = .Range("CaGMDls").Value
r(14) = .Range("CaGMPct").Value
End With
With wb.Worksheets("PurAgreeData")
p(1) = .Range("PaDep").Value
p(2) = .Range("PaRls").Value
p(3) = .Range("PaCom").Value
p(4) = .Range("PaSaH").Value
p(5) = .Range("PaCashInBk").Value
p(6) = .Range("PaUnPdDep").Value
p(7) = .Range("PaUnPdDepDate").Value
p(8) = .Range("PaDepCommt").Value
p(9) = .Range("PaCashInBkRls").Value
p(10) = .Range("PaUnPdRls").Value
p(11) = .Range("PaUnPdRlsDate").Value
p(12) = .Range("PaUnPdRlsCommt").Value
p(13) = .Range("PaCashInBkComp").Value
p(14) = .Range("PaUnPdComp").Value
p(15) = .Range("PaUnPdCompDate").Value
p(16) = .Range("PaUnPdCompCommt").Value
p(17) = .Range("PaPdEq").Value
p(18) = .Range("PaYetPdEqP1").Value
p(19) = .Range("PaYetPdEqP1Date").Value
p(20) = .Range("PaYetPdEqP1Commt").Value
p(21) = .Range("PaPdEq2").Value
p(22) = .Range("PaYetPdEqP2").Value
p(23) = .Range("PaYetPdEqP2Date").Value
p(24) = .Range("PaYetPdEqP2Commt").Value
p(25) = .Range("PaPdEq3").Value
p(26) = .Range("PaYetPdEqP3").Value
p(27) = .Range("PaYetPdEqP3Date").Value
p(28) = .Range("PaYetPdEqP3Commt").Value
p(29) = .Range("PaPdIns").Value
p(30) = .Range("PaYetPdInsP1").Value
p(31) = .Range("PaYetPdInsP1Date").Value
p(32) = .Range("PaYetPdInsP1Commt").Value
p(33) = .Range("PaPdIn2").Value
p(34) = .Range("PaYetPdInsP2").Value
p(35) = .Range("PaYetPdInsP2Date").Value
p(36) = .Range("PaYetPdInsP2Commt").Value
p(37) = .Range("PaScommPd").Value
p(38) = .Range("PaYetPdSCommP1").Value
p(39) = .Range("PaYetPdScommP1Date").Value
p(40) = .Range("PaYetPdScommP1Commt").Value
p(41) = .Range("PaScommPd2").Value
p(42) = .Range("PaYetPdSCommP2").Value
p(43) = .Range("PaYetPdSCommP2Date").Value
p(44) = .Range("PaYetPdSCommP2Commt").Value
p(45) = .Range("PaScommPd3").Value
p(46) = .Range("PaYetPdSCommP3").Value
p(47) = .Range("PaYetPdSCommP3Date").Value
p(48) = .Range("PaYetPdSCommP3Commt").Value
End With
wb.Close SaveChanges:=False
'DoEvents
'UserForm1.Repaint
With ThisWorkbook.Worksheets("CurrentData")
For n = 1 To 14
..Cells(j, n).Value = r(n)
Next n
For k = 1 To 48
..Cells(j, k + 14).Value = p(k)
Next k
End With
j = j + 1
sName = Dir
Loop
Range("G3").Value = Now()
AutoFilterOn
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
'UserForm1.Hide
'Unload UserForm1
End Sub
Sub AutoFilterOn()
If Sheets("CurrentData").AutoFilterMode = False Then
Range("A5:BJ5").AutoFilter
End If
End Sub
You can quickly see a FLAW - please point-it-out!! -- Originally
Written on my PC (xl2003) -- I thought I had successfully made the
"modifications" to make it work - As I step through it in VBE (Using
Step-thru-Mode - It only stops on 5 or 6 lines before reaching the
End Sub. In step mode it reaches and passes the line sName = Dir(sPath)
And I then from the immediate window ? sName (return),, but Nothing,,
Confused here, Note I have commented out UserForm reference that
usable
Only on the PC Version..
Any assistance appreciated,,
Code follows:
Option Explicit
Option Base 1
Sub ExtractDataFromFiles()
Const sPath = "Macintosh HD:CashApp:WIPContracts:"
Dim sName As String
Dim wb As Workbook
Dim j As Integer
Dim k As Integer
Dim n As Integer
Dim r(1 To 14) As Variant
Dim p(1 To 48) As Variant
ActiveSheet.Range("A6:BJ2000").ClearContents
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
sName = Dir(sPath)
j = 6 ' Data starts on Row 6
'UserForm1.Show vbModeless
Do While sName <> ""
Set wb = Workbooks.Open(sPath & sName, UpdateLinks:=0)
With wb.Worksheets("Cost Analysis")
r(1) = .Range("CaPaNum").Value
r(2) = .Range("CaPurName").Value
r(3) = .Range("CaPurCitySt").Value
r(4) = .Range("CaLocName").Value
r(5) = Left(.Range("CaLocCitySt").Value, Len(.Range("CaLocCitySt")) - 2)
r(6) = Right(.Range("CaLocCitySt").Value, 2) 'Pull State only
r(7) = .Range("CaCsStat").Value
r(8) = .Range("CaEqCost").Value
r(9) = .Range("CaInCost").Value
r(10) = .Range("CaComm").Value
r(11) = .Range("CaTotCost").Value
r(12) = .Range("CaEqSale").Value
r(13) = .Range("CaGMDls").Value
r(14) = .Range("CaGMPct").Value
End With
With wb.Worksheets("PurAgreeData")
p(1) = .Range("PaDep").Value
p(2) = .Range("PaRls").Value
p(3) = .Range("PaCom").Value
p(4) = .Range("PaSaH").Value
p(5) = .Range("PaCashInBk").Value
p(6) = .Range("PaUnPdDep").Value
p(7) = .Range("PaUnPdDepDate").Value
p(8) = .Range("PaDepCommt").Value
p(9) = .Range("PaCashInBkRls").Value
p(10) = .Range("PaUnPdRls").Value
p(11) = .Range("PaUnPdRlsDate").Value
p(12) = .Range("PaUnPdRlsCommt").Value
p(13) = .Range("PaCashInBkComp").Value
p(14) = .Range("PaUnPdComp").Value
p(15) = .Range("PaUnPdCompDate").Value
p(16) = .Range("PaUnPdCompCommt").Value
p(17) = .Range("PaPdEq").Value
p(18) = .Range("PaYetPdEqP1").Value
p(19) = .Range("PaYetPdEqP1Date").Value
p(20) = .Range("PaYetPdEqP1Commt").Value
p(21) = .Range("PaPdEq2").Value
p(22) = .Range("PaYetPdEqP2").Value
p(23) = .Range("PaYetPdEqP2Date").Value
p(24) = .Range("PaYetPdEqP2Commt").Value
p(25) = .Range("PaPdEq3").Value
p(26) = .Range("PaYetPdEqP3").Value
p(27) = .Range("PaYetPdEqP3Date").Value
p(28) = .Range("PaYetPdEqP3Commt").Value
p(29) = .Range("PaPdIns").Value
p(30) = .Range("PaYetPdInsP1").Value
p(31) = .Range("PaYetPdInsP1Date").Value
p(32) = .Range("PaYetPdInsP1Commt").Value
p(33) = .Range("PaPdIn2").Value
p(34) = .Range("PaYetPdInsP2").Value
p(35) = .Range("PaYetPdInsP2Date").Value
p(36) = .Range("PaYetPdInsP2Commt").Value
p(37) = .Range("PaScommPd").Value
p(38) = .Range("PaYetPdSCommP1").Value
p(39) = .Range("PaYetPdScommP1Date").Value
p(40) = .Range("PaYetPdScommP1Commt").Value
p(41) = .Range("PaScommPd2").Value
p(42) = .Range("PaYetPdSCommP2").Value
p(43) = .Range("PaYetPdSCommP2Date").Value
p(44) = .Range("PaYetPdSCommP2Commt").Value
p(45) = .Range("PaScommPd3").Value
p(46) = .Range("PaYetPdSCommP3").Value
p(47) = .Range("PaYetPdSCommP3Date").Value
p(48) = .Range("PaYetPdSCommP3Commt").Value
End With
wb.Close SaveChanges:=False
'DoEvents
'UserForm1.Repaint
With ThisWorkbook.Worksheets("CurrentData")
For n = 1 To 14
..Cells(j, n).Value = r(n)
Next n
For k = 1 To 48
..Cells(j, k + 14).Value = p(k)
Next k
End With
j = j + 1
sName = Dir
Loop
Range("G3").Value = Now()
AutoFilterOn
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
'UserForm1.Hide
'Unload UserForm1
End Sub
Sub AutoFilterOn()
If Sheets("CurrentData").AutoFilterMode = False Then
Range("A5:BJ5").AutoFilter
End If
End Sub