C-R-A-P !!!! - I Hate Learning that WAY -- How stupid on my part,
Tks Tom
"Tom Ogilvy" wrote:
> Get rid of your On Error Resume Next
>
> and see where you get errors.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Jim May" wrote:
>
> > 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
> >
> >
|
Similar Threads
