Help Get This Macro Running in OS X Please

[WoodyJI]

I have this macro that a friend of mine wrote on a windows machine, and I can't quite figure out the code changes needed for OS X. For someone who knows how to do this, I'm sure it would only take a second, but I've been banging my head against the wall on this thing for too long.

Please Login or Register  to view this content.

I can get a "open files" dialogue box to pop up, but I can't figure out how to make sure that multiselect is enabled. I need to be able to run this macro on 11 files at once. Thanks so much for your time in reading this post already!

[NickHK]

I don't have a Mac to test this on but it works in Windows, I made a few
changes.
The Help file doesn't indicate that any of this is not Mac compliant. The
only thing that may error is the use of the "*" in "Your Files,*.xls", as
this is an MS-DOS based wildcard.
Don't know what it is on a Mac, but you can try to change it to something
compatible if it proves a problem.
As for the Multiselect, it should be enabled. On a PC, SHIFT+<mouse click>
selects all files between the first and last items clicked, whilst
CONTROL+<mouse click> adds each item one at a time.
This is the same as a normal list box in Windows, so I would imagine it is
the same on a Mac; whatever you "normally" do to multi select items in a
list box.

NickHK

Private Sub CommandButton1_Click()
Dim FileSelected
Dim FileName
Dim WB As Workbook
Dim WS As Worksheet
Dim CompiledDataArray() 'Array to store our statistics
Dim Counter1 As Integer
Dim i As Long, j As Long

FileSelected = Application.GetOpenFilename("Your Files,*.xls", , "Select
Files", , True)
If StrComp(TypeName(FileSelected), "boolean", vbTextCompare) = 0 Then Exit
Sub
'Only create the correct number of elements in the array for the number of
files selected
ReDim CompiledDataArray(1 To UBound(FileSelected) - LBound(FileSelected) +
1, 1 To 53)

For Each FileName In FileSelected

Application.DisplayAlerts = False

Set WB = Workbooks.Open(FileName)
Set WS = WB.Worksheets(1)
With WS
Counter1 = Counter1 + 1
'Easiset way to avoid errors with maths functions
On Error Resume Next
CompiledDataArray(Counter1, 1) = FileName
CompiledDataArray(Counter1, 2) =
Application.WorksheetFunction.Average(.Range(.Cells(10, 2), .Cells(1884,
2)))
CompiledDataArray(Counter1, 3) =
Application.WorksheetFunction.StDev(.Range(.Cells(10, 2), .Cells(1884, 2)))
CompiledDataArray(Counter1, 4) =
Application.WorksheetFunction.Min(.Range(.Cells(10, 2), .Cells(1884, 2)))
CompiledDataArray(Counter1, 5) =
Application.WorksheetFunction.Max(.Range(.Cells(10, 2), .Cells(1884, 2)))
'-------------
'Repetative code cut
'-------------
CompiledDataArray(Counter1, 50) =
Application.WorksheetFunction.Average(.Range(.Cells(10, 14), .Cells(1884,
14)))
CompiledDataArray(Counter1, 51) =
Application.WorksheetFunction.StDev(.Range(.Cells(10, 14), .Cells(1884,
14)))
CompiledDataArray(Counter1, 52) =
Application.WorksheetFunction.Min(.Range(.Cells(10, 14), .Cells(1884, 14)))
CompiledDataArray(Counter1, 53) =
Application.WorksheetFunction.Max(.Range(.Cells(10, 14), .Cells(1884, 14)))
'Return to normal error handling
On Error GoTo 0
End With

WB.Close savechanges:=False

Next

Set WB = Workbooks.Add

With WB.Worksheets(1)
For i = 1 To Counter1
For j = 1 To 53
.Cells(i, j).Value = CompiledDataArray(i, j)
Next j
Next i
End With
End Sub

NickHK

"WoodyJI" <[email protected]> wrote in
message news:[email protected]...
>
> I have this macro that a friend of mine wrote on a windows machine, and
> I can't quite figure out the code changes needed for OS X. For someone
> who knows how to do this, I'm sure it would only take a second, but I've
> been banging my head against the wall on this thing for too long.
>
> Code:
> --------------------
> Option Base 1
>
> Sub Data_Compiler()
>
> Dim FileSelected
> Dim FileName
> Dim CompiledDataArray(1 To 30, 1 To 53) 'Array to store our
statistics
> Dim Counter1 As Integer
>
> Counter1 = 1
>
> FileSelected = Application.GetOpenFilename("Your Files,*.xls", , "Select
Files", , True)
> If StrComp(TypeName(FileSelected), "boolean", vbTextCompare) = 0 Then
Exit Sub
>
>
> For Each FileName In FileSelected
>
> Application.DisplayAlerts = False
>
> Workbooks.Open FileName
> Cells(1, 1).Select
>
> CompiledDataArray(Counter1, 1) = FileName
> CompiledDataArray(Counter1, 2) =
Application.WorksheetFunction.Average(Range(Cells(10, 2), Cells(1884, 2)))
> CompiledDataArray(Counter1, 3) =
Application.WorksheetFunction.StDev(Range(Cells(10, 2), Cells(1884, 2)))
> CompiledDataArray(Counter1, 4) =
Application.WorksheetFunction.Min(Range(Cells(10, 2), Cells(1884, 2)))
> CompiledDataArray(Counter1, 5) =
Application.WorksheetFunction.Max(Range(Cells(10, 2), Cells(1884, 2)))
>
> CompiledDataArray(Counter1, 6) =
Application.WorksheetFunction.Average(Range(Cells(10, 3), Cells(1884, 3)))
> CompiledDataArray(Counter1, 7) =
Application.WorksheetFunction.StDev(Range(Cells(10, 3), Cells(1884, 3)))
> CompiledDataArray(Counter1, 8) =
Application.WorksheetFunction.Min(Range(Cells(10, 3), Cells(1884, 3)))
> CompiledDataArray(Counter1, 9) =
Application.WorksheetFunction.Max(Range(Cells(10, 3), Cells(1884, 3)))
>
> CompiledDataArray(Counter1, 10) =
Application.WorksheetFunction.Average(Range(Cells(10, 4), Cells(1884, 4)))
> CompiledDataArray(Counter1, 11) =
Application.WorksheetFunction.StDev(Range(Cells(10, 4), Cells(1884, 4)))
> CompiledDataArray(Counter1, 12) =
Application.WorksheetFunction.Min(Range(Cells(10, 4), Cells(1884, 4)))
> CompiledDataArray(Counter1, 13) =
Application.WorksheetFunction.Max(Range(Cells(10, 4), Cells(1884, 4)))
>
> CompiledDataArray(Counter1, 14) =
Application.WorksheetFunction.Average(Range(Cells(10, 5), Cells(1884, 5)))
> CompiledDataArray(Counter1, 15) =
Application.WorksheetFunction.StDev(Range(Cells(10, 5), Cells(1884, 5)))
> CompiledDataArray(Counter1, 16) =
Application.WorksheetFunction.Min(Range(Cells(10, 5), Cells(1884, 5)))
> CompiledDataArray(Counter1, 17) =
Application.WorksheetFunction.Max(Range(Cells(10, 5), Cells(1884, 5)))
>
> CompiledDataArray(Counter1, 18) =
Application.WorksheetFunction.Average(Range(Cells(10, 6), Cells(1884, 6)))
> CompiledDataArray(Counter1, 19) =
Application.WorksheetFunction.StDev(Range(Cells(10, 6), Cells(1884, 6)))
> CompiledDataArray(Counter1, 20) =
Application.WorksheetFunction.Min(Range(Cells(10, 6), Cells(1884, 6)))
> CompiledDataArray(Counter1, 21) =
Application.WorksheetFunction.Max(Range(Cells(10, 6), Cells(1884, 6)))
>
> CompiledDataArray(Counter1, 22) =
Application.WorksheetFunction.Average(Range(Cells(10, 7), Cells(1884, 7)))
> CompiledDataArray(Counter1, 23) =
Application.WorksheetFunction.StDev(Range(Cells(10, 7), Cells(1884, 7)))
> CompiledDataArray(Counter1, 24) =
Application.WorksheetFunction.Min(Range(Cells(10, 7), Cells(1884, 7)))
> CompiledDataArray(Counter1, 25) =
Application.WorksheetFunction.Max(Range(Cells(10, 7), Cells(1884, 7)))
>
> CompiledDataArray(Counter1, 26) =
Application.WorksheetFunction.Average(Range(Cells(10, 8), Cells(1884, 8)))
> CompiledDataArray(Counter1, 27) =
Application.WorksheetFunction.StDev(Range(Cells(10, 8), Cells(1884, 8)))
> CompiledDataArray(Counter1, 28) =
Application.WorksheetFunction.Min(Range(Cells(10, 8), Cells(1884, 8)))
> CompiledDataArray(Counter1, 29) =
Application.WorksheetFunction.Max(Range(Cells(10, 8), Cells(1884, 8)))
>
> CompiledDataArray(Counter1, 30) =
Application.WorksheetFunction.Average(Range(Cells(10, 9), Cells(1884, 9)))
> CompiledDataArray(Counter1, 31) =
Application.WorksheetFunction.StDev(Range(Cells(10, 9), Cells(1884, 9)))
> CompiledDataArray(Counter1, 32) =
Application.WorksheetFunction.Min(Range(Cells(10, 9), Cells(1884, 9)))
> CompiledDataArray(Counter1, 33) =
Application.WorksheetFunction.Max(Range(Cells(10, 9), Cells(1884, 9)))
>
> CompiledDataArray(Counter1, 34) =
Application.WorksheetFunction.Average(Range(Cells(10, 10), Cells(1884, 10)))
> CompiledDataArray(Counter1, 35) =
Application.WorksheetFunction.StDev(Range(Cells(10, 10), Cells(1884, 10)))
> CompiledDataArray(Counter1, 36) =
Application.WorksheetFunction.Min(Range(Cells(10, 10), Cells(1884, 10)))
> CompiledDataArray(Counter1, 37) =
Application.WorksheetFunction.Max(Range(Cells(10, 10), Cells(1884, 10)))
>
> CompiledDataArray(Counter1, 38) =
Application.WorksheetFunction.Average(Range(Cells(10, 11), Cells(1884, 11)))
> CompiledDataArray(Counter1, 39) =
Application.WorksheetFunction.StDev(Range(Cells(10, 11), Cells(1884, 11)))
> CompiledDataArray(Counter1, 40) =
Application.WorksheetFunction.Min(Range(Cells(10, 11), Cells(1884, 11)))
> CompiledDataArray(Counter1, 41) =
Application.WorksheetFunction.Max(Range(Cells(10, 11), Cells(1884, 11)))
>
> CompiledDataArray(Counter1, 42) =
Application.WorksheetFunction.Average(Range(Cells(10, 12), Cells(1884, 12)))
> CompiledDataArray(Counter1, 43) =
Application.WorksheetFunction.StDev(Range(Cells(10, 12), Cells(1884, 12)))
> CompiledDataArray(Counter1, 44) =
Application.WorksheetFunction.Min(Range(Cells(10, 12), Cells(1884, 12)))
> CompiledDataArray(Counter1, 45) =
Application.WorksheetFunction.Max(Range(Cells(10, 12), Cells(1884, 12)))
>
> CompiledDataArray(Counter1, 46) =
Application.WorksheetFunction.Average(Range(Cells(10, 13), Cells(1884, 13)))
> CompiledDataArray(Counter1, 47) =
Application.WorksheetFunction.StDev(Range(Cells(10, 13), Cells(1884, 13)))
> CompiledDataArray(Counter1, 48) =
Application.WorksheetFunction.Min(Range(Cells(10, 13), Cells(1884, 13)))
> CompiledDataArray(Counter1, 49) =
Application.WorksheetFunction.Max(Range(Cells(10, 13), Cells(1884, 13)))
>
> CompiledDataArray(Counter1, 50) =
Application.WorksheetFunction.Average(Range(Cells(10, 14), Cells(1884, 14)))
> CompiledDataArray(Counter1, 51) =
Application.WorksheetFunction.StDev(Range(Cells(10, 14), Cells(1884, 14)))
> CompiledDataArray(Counter1, 52) =
Application.WorksheetFunction.Min(Range(Cells(10, 14), Cells(1884, 14)))
> CompiledDataArray(Counter1, 53) =
Application.WorksheetFunction.Max(Range(Cells(10, 14), Cells(1884, 14)))
>
> Counter1 = Counter1 + 1
>
> ActiveWorkbook.Close savechanges:=False
> Next
>
> Workbooks.Add
>
> Cells(1, 1).Select
>
> For j = 1 To 53
> For i = 1 To 30
> Cells(i, j).Value = CompiledDataArray(i, j)
> Next i
> Next j
>
> End Sub
> --------------------
>
> I can get a "open files" dialogue box to pop up, but I can't figure out
> how to make sure that multiselect is enabled. I need to be able to run
> this macro on 11 files at once. Thanks so much for your time in
> reading this post already!
>
>
> --
> WoodyJI
> ------------------------------------------------------------------------
> WoodyJI's Profile:
http://www.excelforum.com/member.php...o&userid=37167
> View this thread: http://www.excelforum.com/showthread...hreadid=571904
>

[WoodyJI]

Thanks for taking a look at this. I got a syntax error when I tested it as you wrote it though, from the first line:

Please Login or Register  to view this content.

Regarding the getopenfilename command, the help file says:

On the Macintosh, this string is a list of comma-separated file type codes (for example, "TEXT,XLA5,XLS4"). Spaces are significant and shouldn't be inserted before or after the comma separators unless they're part of the file type code. If omitted, this argument defaults to all file types.

The thing is, if I run the macro as it was, but I change the getopenfilename line to

Please Login or Register  to view this content.

then I recieve an open files dialogue box, but I cannot use shift+click to select more than one file, like I normally would do. So I need to figure out how to do "multiselect=True" and the help file only says a little about this:

.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect)

MultiSelect Optional Variant. True to allow multiple file names to be selected. False to allow only one file name to be selected. The default value is False
Remarks
This method returns the selected file name or the name entered by the user. The returned name may include a path specification. If MultiSelect is True, the return value is an array of the selected file names (even if only one filename is selected). Returns False if the user cancels the dialog box.
This method may change the current drive or folder.

Maybe the setup for this action is different on a mac or something b/c I don't understand why the "true" already in the getopenfilename code didn't work originally. The "Your Files, *.xls", is understandable, like you say b/c it's MSDOS based, but I dont understand the rest...

[NickHK]

Not having a Mac to test anything, all I can say is it should work.
At a push you you could loop the .getopenfilename get a name each time.
FileSelected = Application.GetOpenFilename()
Do Until FileSelected=False
'process data
FileSelected = Application.GetOpenFilename()
Loop

I'll let you know tomorrow if find anything.

NickHK

"WoodyJI" <[email protected]> 撰寫於郵件新聞:[email protected]...
>
> Thanks for taking a look at this. I got a syntax error when I tested it
> as you wrote it though, from the first line:
> Code:
> --------------------
> Private Sub CommandButton1_Click()
> --------------------
> Regarding the getopenfilename command, the help file says:> On the
> Macintosh, this string is a list of comma-separated file type
>> codes (for example, "TEXT,XLA5,XLS4"). Spaces are significant and
>> shouldn't be inserted before or after the comma separators unless
>> they're part of the file type code. If omitted, this argument defaults
>> to all file types.
>>
>
> The thing is, if I run the macro as it was, but I change the
> getopenfilename line to
> Code:
> --------------------
> FileSelected = Application.GetOpenFilename()
> --------------------
> then I recieve an open files dialogue box, but I cannot use shift+click
> to select more than one file, like I normally would do. So I need to
> figure out how to do "multiselect=True" and the help file only says a
> little about this: > .GetOpenFilename(FileFilter, FilterIndex, Title,
> ButtonText,
>> MultiSelect)
>>
>> MultiSelect Optional Variant. True to allow multiple file names to be
>> selected. False to allow only one file name to be selected. The default
>> value is False
>> Remarks
>> This method returns the selected file name or the name entered by the
>> user. The returned name may include a path specification. If
>> MultiSelect is True, the return value is an array of the selected file
>> names (even if only one filename is selected). Returns False if the
>> user cancels the dialog box.
>> This method may change the current drive or folder.
>> Maybe the setup for this action is different on a mac or something b/c I
> don't understand why the "true" already in the getopenfilename code
> didn't work originally. The "Your Files, *.xls", is understandable,
> like you say b/c it's MSDOS based, but I dont understand the rest...
>
>
> --
> WoodyJI
> ------------------------------------------------------------------------
> WoodyJI's Profile:
> http://www.excelforum.com/member.php...o&userid=37167
> View this thread: http://www.excelforum.com/showthread...hreadid=571904
>

[NickHK]

Woody,
Seems like you are not alone:
http://groups.google.co.uk/group/mic...5b3c33755274cf
-------
But this appears pretty conclusive:
http://www.mcse.ms/archive161-2005-1-1385633.html
-----------
General Mac Excel group
http://www.mcse.ms/archive262-2005-11-1983719.html
---------------

So, looks like this :

Private Sub CommandButton1_Click()
Dim FileTypes As String
Dim FileName As Variant

Dim WBData As Workbook
Dim WSData As Worksheet

Dim WB As Workbook
Dim WS As Worksheet
Dim Counter1 As Integer

Dim i As Long

'Create a new WB
Set WBData = Workbooks.Add
Set WSData = WBData.Worksheets(1)

'Whatever the correct format is the Mac
'FileTypes = "Your Files TEXT,XLA5,XLS4"
FileTypes = "Your Files, *.xls"

Do
FileName = Application.GetOpenFilename(FileTypes, , "Select Files", ,
False)
If FileName = False Then Exit Do

Set WB = Workbooks.Open(FileName)
Set WS = WB.Worksheets(1)
With WS
Counter1 = Counter1 + 1
'Easiset way to avoid errors with maths functions
On Error Resume Next
WSData.Cells(Counter1, 1).Value = FileName
For i = 2 To 14
WSData.Cells(Counter1, i * 4 - 6) =
Application.WorksheetFunction.Average(.Range(.Cells(10, i), .Cells(1884,
i)))
WSData.Cells(Counter1, i * 4 - 5) =
Application.WorksheetFunction.StDev(.Range(.Cells(10, i), .Cells(1884, i)))
WSData.Cells(Counter1, i * 4 - 4) =
Application.WorksheetFunction.Min(.Range(.Cells(10, i), .Cells(1884, i)))
WSData.Cells(Counter1, i * 4 - 3) =
Application.WorksheetFunction.Max(.Range(.Cells(10, i), .Cells(1884, i)))
Next
'Return to normal error handling
On Error GoTo 0
End With

WB.Close savechanges:=False
Loop
'Decide what to do with the new data file
'WBData.Save

End Sub

NickHK

"NickHK" <[email protected]> wrote in message
news:[email protected]...
> Not having a Mac to test anything, all I can say is it should work.
> At a push you you could loop the .getopenfilename get a name each time.
> FileSelected = Application.GetOpenFilename()
> Do Until FileSelected=False
> 'process data
> FileSelected = Application.GetOpenFilename()
> Loop
>
> I'll let you know tomorrow if find anything.
>
> NickHK
>
> "WoodyJI" <[email protected]>
撰寫於郵件新聞:[email protected]...
> >
> > Thanks for taking a look at this. I got a syntax error when I tested it
> > as you wrote it though, from the first line:
> > Code:
> > --------------------
> > Private Sub CommandButton1_Click()
> > --------------------
> > Regarding the getopenfilename command, the help file says:> On the
> > Macintosh, this string is a list of comma-separated file type
> >> codes (for example, "TEXT,XLA5,XLS4"). Spaces are significant and
> >> shouldn't be inserted before or after the comma separators unless
> >> they're part of the file type code. If omitted, this argument defaults
> >> to all file types.
> >>
> >
> > The thing is, if I run the macro as it was, but I change the
> > getopenfilename line to
> > Code:
> > --------------------
> > FileSelected = Application.GetOpenFilename()
> > --------------------
> > then I recieve an open files dialogue box, but I cannot use shift+click
> > to select more than one file, like I normally would do. So I need to
> > figure out how to do "multiselect=True" and the help file only says a
> > little about this: > .GetOpenFilename(FileFilter, FilterIndex, Title,
> > ButtonText,
> >> MultiSelect)
> >>
> >> MultiSelect Optional Variant. True to allow multiple file names to be
> >> selected. False to allow only one file name to be selected. The default
> >> value is False
> >> Remarks
> >> This method returns the selected file name or the name entered by the
> >> user. The returned name may include a path specification. If
> >> MultiSelect is True, the return value is an array of the selected file
> >> names (even if only one filename is selected). Returns False if the
> >> user cancels the dialog box.
> >> This method may change the current drive or folder.
> >> Maybe the setup for this action is different on a mac or something b/c
I
> > don't understand why the "true" already in the getopenfilename code
> > didn't work originally. The "Your Files, *.xls", is understandable,
> > like you say b/c it's MSDOS based, but I dont understand the rest...
> >
> >
> > --
> > WoodyJI
> > ------------------------------------------------------------------------
> > WoodyJI's Profile:
> > http://www.excelforum.com/member.php...o&userid=37167
> > View this thread:
http://www.excelforum.com/showthread...hreadid=571904
> >
>
>

[WoodyJI]

Thanks a lot for you help, it looks like I've got it working!

(All I had to do was put the file type in there, and eliminate some spaces between things.)

Great work!

[NickHK]

What does that statement look like now then ?

NickHK

"WoodyJI" <[email protected]> 撰寫於郵件新聞:[email protected]...
>
> Thanks a lot for you help, it looks like I've got it working!
>
> (All I had to do was put the file type in there, and eliminate some
> spaces between things.)
>
> Great work!
>
>
> --
> WoodyJI
> ------------------------------------------------------------------------
> WoodyJI's Profile:
> http://www.excelforum.com/member.php...o&userid=37167
> View this thread: http://www.excelforum.com/showthread...hreadid=571904
>

[WoodyJI]

Please Login or Register  to view this content.

[NickHK]

OK, so the MultiSelect is a definite No-Go ?

NickHK

"WoodyJI" <[email protected]> 撰寫於郵件新聞:[email protected]...
>
> Code:
> --------------------
> Sub Mac_Compiler()
>
> Dim FileTypes As String
> Dim FileName As Variant
>
> Dim WBData As Workbook
> Dim WSData As Worksheet
>
> Dim WB As Workbook
> Dim WS As Worksheet
> Dim Counter1 As Integer
>
> Dim i As Long
>
> 'Create a new WB
> Set WBData = Workbooks.Add
> Set WSData = WBData.Worksheets(1)
>
> 'Whatever the correct format is the Mac
> 'FileTypes = "Your Files TEXT,XLA5,XLS4"
> FileTypes = "XLS4"
>
> Do
> FileName = Application.GetOpenFilename(XLS4, , File, , False)
> If FileName = False Then Exit Do
>
> Set WB = Workbooks.Open(FileName)
> Set WS = WB.Worksheets(1)
> With WS
> Counter1 = Counter1 + 1
> 'Easiset way to avoid errors with maths functions
> On Error Resume Next
> WSData.Cells(Counter1, 1).Value = FileName
> For i = 2 To 14
> WSData.Cells(Counter1, i * 4 - 6) =
> Application.WorksheetFunction.Average(.Range(.Cells(10, i), .Cells(1884,
> i)))
> WSData.Cells(Counter1, i * 4 - 5) =
> Application.WorksheetFunction.StDev(.Range(.Cells(10, i), .Cells(1884,
> i)))
> WSData.Cells(Counter1, i * 4 - 4) =
> Application.WorksheetFunction.Min(.Range(.Cells(10, i), .Cells(1884, i)))
> WSData.Cells(Counter1, i * 4 - 3) =
> Application.WorksheetFunction.Max(.Range(.Cells(10, i), .Cells(1884, i)))
> Next
> 'Return to normal error handling
> On Error GoTo 0
> End With
>
> WB.Close savechanges:=False
> Loop
> 'Decide what to do with the new data file
> 'WBData.Save
>
> End Sub
> --------------------
>
>
> --
> WoodyJI
> ------------------------------------------------------------------------
> WoodyJI's Profile:
> http://www.excelforum.com/member.php...o&userid=37167
> View this thread: http://www.excelforum.com/showthread...hreadid=571904
>

[WoodyJI]

Yeah, since it looks like I must do loops on a Mac, this macro cannot run as fast as it would on a pc because I need to tell it which file to use every time.

That is unless I can use "automator" to gather the files I'll be working on and tell it to use an apple script written for excel to run the macro we've built together on it. I think I know how to do this, but I'll probably need to bypass the getopenfilename command that we worked together to figure out, and somehow tell the macro just to run on the active sheet or something to that effect. I'll take a look at it and ask some people who know stuff about automator actions. For now, it works as well as I can getThanks!