ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Help Get This Macro Running in OS X Please (https://www.excelbanter.com/excel-programming/370461-help-get-macro-running-os-x-please.html)

WoodyJI[_2_]
Help Get This Macro Running in OS X Please
 

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

NickHK
Help Get This Macro Running in OS X Please
 
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(.Cell s(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(.Cell s(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" wrote in
message ...

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[_3_]
Help Get This Macro Running in OS X Please
 

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[_3_]
Help Get This Macro Running in OS X Please
 
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" 撰寫於郵件新聞:WoodyJI.2cn5k7_1155745813.78@excelf orum-nospam.com...

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
Help Get This Macro Running in OS X Please
 
Woody,
Seems like you are not alone:
http://groups.google.co.uk/group/mic...3c3375 5274cf
-------
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(.Cell s(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" wrote in message
...
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"
撰寫於郵件新聞:WoodyJI.2cn5k7_1155745813.78@excelf orum-nospam.com...

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[_4_]
Help Get This Macro Running in OS X Please
 

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

NickHK[_3_]
Help Get This Macro Running in OS X Please
 
What does that statement look like now then ?

NickHK

"WoodyJI" 撰寫於郵件新聞:WoodyJI.2coyu3_1155830407.6598@exce lforum-nospam.com...

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[_5_]
Help Get This Macro Running in OS X Please
 

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(.Cell s(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

NickHK[_3_]
Help Get This Macro Running in OS X Please
 
OK, so the MultiSelect is a definite No-Go ?

NickHK

"WoodyJI" 撰寫於郵件新聞:WoodyJI.2cozzs_1155831911.3516@exce lforum-nospam.com...

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(.Cell s(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[_6_]
Help Get This Macro Running in OS X Please
 

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!


--
WoodyJI
------------------------------------------------------------------------
WoodyJI's Profile: http://www.excelforum.com/member.php...o&userid=37167
View this thread: http://www.excelforum.com/showthread...hreadid=571904


All times are GMT +1. The time now is 10:05 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com