Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assistance with VBA code on MAC
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assistance with VBA code on MAC
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assistance with VBA code on MAC
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code assistance | Excel Programming | |||
Code assistance | Excel Programming | |||
I need assistance getting VBA code to do the following... | Excel Programming | |||
Code assistance please | Excel Programming | |||
VBA Code Assistance | Excel Programming |