Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer Module1 from PC to Mac
The below code works PREFECTLY on my Exccel 2003 - Windows PC
I took it out to a client's office this morning and we spent 2 hours trying to get the Macro ExtractDataFromFiles() to run -- all without success. Our initial problem started with line 2 - where I needed to replace Const.... with the area on the Mac HD... neither he or I knew exactly How to do it... We might have fixed it because we 30 minutes later noticed the code stopped on the line: UserForm1.Show vbModeless << with the keyword vbmodeless highlite < so I '' Commented it out and things seemed to run a bit further,, but anyway it ended Up being a 2 hour fiasco, unfortunately, and I was so proud of what I had Done on my windows PC,, grrrrrr Can anyone offer some help here - He has an uptodate Macintosh (sorry don't Know versions of it or his excel ver,,, Thanks, Option Explicit Option Base 1 Sub ExtractDataFromFiles() Const sPath = "C:\Documents and Settings\Jim May\My Documents\Boatwright Stan\Projects\Lodging_Technology\Contracts\" Dim sName As String Dim wb As Workbook Dim j As Integer Dim n As Integer Dim r(1 To 14) As Variant ActiveSheet.Range("A6:N2000").ClearContents Application.ScreenUpdating = False Application.DisplayAlerts = False sName = Dir(sPath & "PA*.xls") j = 6 ' Data starts on Row 6 MsgBox "There are " & FileCount(sPath) & " Qualifying Files" UserForm1.Show vbModeless Do While sName < "" Set wb = Workbooks.Open(sPath & sName) With wb.Worksheets("Cost Analysis") r(1) = .Range("J2").Value r(2) = .Range("B4").Value r(3) = .Range("B6").Value r(4) = .Range("G4").Value r(5) = Left(.Range("G6").Value, Len(.Range("G6")) - 2) r(6) = Right(.Range("G6").Value, 2) r(7) = .Range("J1").Value r(8) = .Range("G51").Value r(9) = .Range("G53").Value r(10) = .Range("G54").Value r(11) = .Range("G56").Value r(12) = .Range("G57").Value r(13) = .Range("G58").Value r(14) = .Range("G59").Value End With wb.Close SaveChanges:=False DoEvents UserForm1.Repaint With ThisWorkbook.ActiveSheet For n = 1 To 14 ..Cells(j, n).Value = r(n) Next n End With j = j + 1 sName = Dir Loop Range("G3").Value = Now() AutoFilterOn UserForm1.Hide Unload UserForm1 End Sub Function FileCount(FolderName As String, _ Optional FileFilter As String = "PA*.xls", _ Optional FileTypes As Long = 1, _ Optional SubFolders As Boolean = False) As Long With Application.FileSearch .NewSearch .LookIn = FolderName .SearchSubFolders = SubFolders .Filename = FileFilter .MatchTextExactly = True .FileType = FileTypes .Execute FileCount = .FoundFiles.Count End With End Function Sub AutoFilterOn() If Sheets("Main").AutoFilterMode = False Then Range("A5:N5").AutoFilter End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer Module1 from PC to Mac
Jim,
Mac certainly have a different format for the drive and may be for the path, your const sPath is not a valid location. I'd be surprised if he had a "..\Documents and Settings\Jim May\My Documents.." path anyway, as that is Windows structure. I assume the Mac version has a macro recorder, so see what it gives you for the required path . NickHK "Jim May" ???????:uo4Bg.106109$IZ2.3687@dukeread07... The below code works PREFECTLY on my Exccel 2003 - Windows PC I took it out to a client's office this morning and we spent 2 hours trying to get the Macro ExtractDataFromFiles() to run -- all without success. Our initial problem started with line 2 - where I needed to replace Const.... with the area on the Mac HD... neither he or I knew exactly How to do it... We might have fixed it because we 30 minutes later noticed the code stopped on the line: UserForm1.Show vbModeless << with the keyword vbmodeless highlite < so I '' Commented it out and things seemed to run a bit further,, but anyway it ended Up being a 2 hour fiasco, unfortunately, and I was so proud of what I had Done on my windows PC,, grrrrrr Can anyone offer some help here - He has an uptodate Macintosh (sorry don't Know versions of it or his excel ver,,, Thanks, Option Explicit Option Base 1 Sub ExtractDataFromFiles() Const sPath = "C:\Documents and Settings\Jim May\My Documents\Boatwright Stan\Projects\Lodging_Technology\Contracts\" Dim sName As String Dim wb As Workbook Dim j As Integer Dim n As Integer Dim r(1 To 14) As Variant ActiveSheet.Range("A6:N2000").ClearContents Application.ScreenUpdating = False Application.DisplayAlerts = False sName = Dir(sPath & "PA*.xls") j = 6 ' Data starts on Row 6 MsgBox "There are " & FileCount(sPath) & " Qualifying Files" UserForm1.Show vbModeless Do While sName < "" Set wb = Workbooks.Open(sPath & sName) With wb.Worksheets("Cost Analysis") r(1) = .Range("J2").Value r(2) = .Range("B4").Value r(3) = .Range("B6").Value r(4) = .Range("G4").Value r(5) = Left(.Range("G6").Value, Len(.Range("G6")) - 2) r(6) = Right(.Range("G6").Value, 2) r(7) = .Range("J1").Value r(8) = .Range("G51").Value r(9) = .Range("G53").Value r(10) = .Range("G54").Value r(11) = .Range("G56").Value r(12) = .Range("G57").Value r(13) = .Range("G58").Value r(14) = .Range("G59").Value End With wb.Close SaveChanges:=False DoEvents UserForm1.Repaint With ThisWorkbook.ActiveSheet For n = 1 To 14 .Cells(j, n).Value = r(n) Next n End With j = j + 1 sName = Dir Loop Range("G3").Value = Now() AutoFilterOn UserForm1.Hide Unload UserForm1 End Sub Function FileCount(FolderName As String, _ Optional FileFilter As String = "PA*.xls", _ Optional FileTypes As Long = 1, _ Optional SubFolders As Boolean = False) As Long With Application.FileSearch .NewSearch .LookIn = FolderName .SearchSubFolders = SubFolders .Filename = FileFilter .MatchTextExactly = True .FileType = FileTypes .Execute FileCount = .FoundFiles.Count End With End Function Sub AutoFilterOn() If Sheets("Main").AutoFilterMode = False Then Range("A5:N5").AutoFilter End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run Module1 first then automatically run Module2 | Excel Programming | |||
Install ThisWorkbook and Module1 files with batch script | Excel Programming | |||
Module1 vs Thisworkbook for Macro | Excel Programming | |||
UDF error when renaming Module1 | Excel Programming | |||
size/space problem with module1 in VB even though it is empty | Excel Programming |