Making the Conversion...Help

[Jim May]

The below code works PREFECTLY on my Excel 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

 

[JE McGimpsey]

The below code works PREFECTLY on my Excel 2003 - Windows PC

First, MacOffice VBA is version 5.00 for all Office versions, so it's
expected that if you use VBA6 commands (e.g., vbmodeless) they won't
work. In general, if your code works in WinXL97, then it will likely
work in MacXL.

Note that since userforms are modal, your loop will halt after the
userform is displayed, until it is dismissed.

Obviously, you'll need to change hard-coded file locations, which
shouldn't be used in most cases anyway (e.g., it wouldn't work if you
used a different PC, either).

You should make use of MacXL/VBA Help. There you'd find out why using
"PA*" in your Dir() call doesn't work - because the asterisk is a valid
character in MacOS. Instead you need to iterate over the files in a
directory, perhaps using a file type to narrow down the type of files.

FileSearch won't work in MacXL. You can find a fairly decent method
using conditional compilation in MacXL/VBA Help ("Strategies for
Developing Cross-Platform Solutions").

You might also want to look at the "Differences between Excel VBA for
Windows and Excel VBA for the Macintosh" VBA Help topic.

 

[Jim May]

JE Thanks so much for your input here..
Might I ask you a bit more until I have a chance to get on my Client's
MAC to review your suggestions?

1) Since we finally got to the point where the Macro progressed to
"Compile error" and at that point and in the code window showed:

Sub ExtractDataFromFiles() << Hilited in Yellow and the keyword
vbModeless Hilited in Blue - Does that mean that we were successful in
getting past Line 2 of Const "DirectoryFileLocation" (which was our
original problem)?

2) And even though I had instructed the Userform to be Modeless - the
Mac Version 5 aspect (as you say) only sees UserForm1 and looks no
further thereby interpreting it as Modal?

3) All Files in the Search Directory Begin with "PA", but I don't need
necessarily to Test for this. I can eliminate this reference. How would
I write the statement to consider all files in the directory with the
XLS extension? Instead of sName = Dir(sPath & "PA*.xls") it
could/should be ???

4) Lastly, I can eliminate the filecount() function altogether as it
raises the FileSearch issue. This should solve this piece of the
problem.

Thanking you in advance,
Jim May

 

[JE McGimpsey]

Might I ask you a bit more until I have a chance to get on my Client's
MAC to review your suggestions?

First, MAC is Media Access Control, as in a unique number assigned to
networking equipment. Macintoshes are abbreviated as title-case: Mac.
Just a pet peeve, apparently shared by some others.

1) Since we finally got to the point where the Macro progressed to
"Compile error" and at that point and in the code window showed:

Sub ExtractDataFromFiles() << Hilited in Yellow and the keyword
vbModeless Hilited in Blue - Does that mean that we were successful in
getting past Line 2 of Const "DirectoryFileLocation" (which was our
original problem)?

Not necessarily. Dir() returns an empty string if no files are found
matching the criterion. For instance

Dim sName As String
sName = Dir("C:\Bad Mac Path\random filename")
MsgBox sName

compiles and runs just fine. sName will be assigned a null string.

2) And even though I had instructed the Userform to be Modeless - the
Mac Version 5 aspect (as you say) only sees UserForm1 and looks no
further thereby interpreting it as Modal?

VBA 5 does not have a modeless userform, so there is no argument in the
calling sequence. One can use conditional compilation in some cases
(though it probably won't work the way you want it in your case):

#If Mac Then
UserForm1.Show
#Else
UserForm1.Show vbModeless
#End If

3) All Files in the Search Directory Begin with "PA", but I don't need
necessarily to Test for this.
I can eliminate this reference. How would
I write the statement to consider all files in the directory with the
XLS extension? Instead of sName = Dir(sPath & "PA*.xls") it
could/should be ???

Take a look at the "Dir" and "MacID function" Help topics - it's very
well documented. Note that using MacID() will cause an error in WinXL,
so you'll need to use conditional compilation.

4) Lastly, I can eliminate the filecount() function altogether as it
raises the FileSearch issue. This should solve this piece of the
problem.

Should work fine.