#1
|
|||
|
|||
Windows GetOpenFileName Method Workaround for Excel 2011 on MAC
Hi everyone! I use MS Excel 2013, but I'm writing some macros for a person using Excel 2011 for MAC. The codes ran perfectly well on my own system but was returning errors on his own. I think the problem is with the 'GetOpenFileName' code, as that doesn't work on MAC OS. I've tried some codes I've seen online that test the operating system of the machine but none worked. I don't know whether anyone has a working code. I want a code that will run on both MAC and Windows. Please I need this help urgently Thank you in advance. |
#2
|
|||
|
|||
HI,
I found this bit of code see if it helps. Code:
Sub Select_File_Or_Files_Mac() Dim MyPath As String Dim MyScript As String Dim MyFiles As String Dim MySplit As Variant Dim N As Long Dim Fname As String Dim mybook As Workbook On Error Resume Next MyPath = MacScript("return (path to documents folder) as String") 'Or use MyPath = "Macintosh HD:Users:Ron:Desktop:TestFolder:" ' In the following statement, change true to false in the line "multiple ' selections allowed true" if you do not want to be able to select more ' than one file. Additionally, if you want to filter for multiple files, change ' {""com.microsoft.Excel.xls""} to ' {""com.microsoft.excel.xls"",""public.comma-separated-values-text""} ' if you want to filter on xls and csv files, for example. MyScript = _ "set applescript's text item delimiters to "","" " & vbNewLine & _ "set theFiles to (choose file of type " & _ " {""com.microsoft.Excel.xls""} " & _ "with prompt ""Please select a file or files"" default location alias """ & _ MyPath & """ multiple selections allowed true) as string" & vbNewLine & _ "set applescript's text item delimiters to """" " & vbNewLine & _ "return theFiles" MyFiles = MacScript(MyScript) On Error GoTo 0 If MyFiles <> "" Then With Application .ScreenUpdating = False .EnableEvents = False End With MySplit = Split(MyFiles, ",") For N = LBound(MySplit) To UBound(MySplit) ' Get the file name only and test to see if it is open. Fname = Right(MySplit(N), Len(MySplit(N)) - InStrRev(MySplit(N), Application.PathSeparator, , 1)) If bIsBookOpen(Fname) = False Then Set mybook = Nothing On Error Resume Next Set mybook = Workbooks.Open(MySplit(N)) On Error GoTo 0 If Not mybook Is Nothing Then MsgBox "You open this file : " & MySplit(N) & vbNewLine & _ "And after you press OK it will be closed" & vbNewLine & _ "without saving, replace this line with your own code." mybook.Close SaveChanges:=False End If Else MsgBox "We skipped this file : " & MySplit(N) & " because it Is already open." End If Next N With Application .ScreenUpdating = True .EnableEvents = True End With End If End Sub Function bIsBookOpen(ByRef szBookName As String) As Boolean ' Contributed by Rob Bovey On Error Resume Next bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing) End Function |
#3
|
|||
|
|||
@charlesdh: I'm sorry for replying late. Thank you very much for the codes. But will it work for both MAC and Windows?
|
#4
|
|||
|
|||
Story11,
No. The code is for the Mac. If you want to try it copy the code to your pc. |
#5
|
|||
|
|||
Okay, thank you. But I need the one that will run on both MAC and PC.
I will appreciate it if you can get me that too. Thank you. |
#6
|
|||
|
|||
Story11,
I do not think there is a code that will work on both. At least I never came across one. You may need to Id (in some way) that when the user open the file to to see if it's a "Mac" or "PC". A thought is to catch the "Error" msg when you open the file. You can then call the correct code. I've not done this. But it's worth a try. Let me know what happens. |
#7
|
|||
|
|||
Hi,
Here's a code that checks the "Operating" system. Set the users button to run this code. If the "OS" is windows ave the call to the windows code. If it a mac then call the mac code. Ps. I'm going to do this in a file that I created that needs to run in windows or mac. Code:
Sub Test_Me() Dim TheOS As String TheOS = Application.OperatingSystem If Left(TheOS, 7) <> "Windows" Then Call Gather_Data End Sub |
#8
|
|||
|
|||
@Charlesdh: I'm sorry if I sound a little disturbing, the truth is that I'm just starting to learn VBA and I had a job to do on it. I did the job on a System that runs on Windows OS but the person that examined the program uses MAC OS, so the program didn't work on his System.
I think what I really need now is a code that will check whether the operating system is Windows or MAC - If it's either of the 2 Operating Systems, it should prompt you to select which one to use. I don't know whether you can help me with a code like that? if you can, I'll appreciate it. (Please I will be needing a full and complete code if you can). I'm sorry for the troubles, and thank you in advance. |
#9
|
|||
|
|||
@charlesdh: I've gotten a way round the codes, thank you very much.
|
#10
|
|||
|
|||
@charlesdh: Everything is messed up again. Maybe I should show you the code that worked on Windows 7, then you should help me incorporate in it the code that will make it work on both Windows and MAC.
Code:
Sub ImportWordTables() 'Imports cells (1,2), (2,2),(3,2) and (4,2) from Word document Tables 1-50 Dim wdDoc As Object Dim wdFileName As Variant Dim TableNo As Integer 'number of tables in Word doc Dim iTable As Integer 'table number index Dim iRow As Long 'row index in Excel Dim iCol As Integer 'column index in Excel wdFileName = Application.GetOpenFilename("Word files (*.doc*),*.doc*", , _ "Browse for file containing table to be imported") If wdFileName = False Then Exit Sub '(user cancelled import file browser) Set wdDoc = GetObject(wdFileName) 'open Word file With wdDoc TableNo = wdDoc.tables.Count If TableNo = 0 Then MsgBox "This document contains no tables", _ vbExclamation, "Import Word Table" ElseIf TableNo > 50 Then TableNo = 50 'Else TableNo is actual number of tables between 1 and 9 End If Range("A1") = "S/N" Range("B1") = "Name" Range("C1") = "Age" Range("D1") = "Sex" Range("E1") = "CGPA" For iTable = 1 To TableNo With .tables(iTable) 'copy cell contents from Word table cells to Excel cells in column B and C Cells(iTable + 1, "A") = iTable Cells(iTable + 1, "B") = WorksheetFunction.Clean(.cell(1, 2).Range.Text) Cells(iTable + 1, "C") = WorksheetFunction.Clean(.cell(2, 2).Range.Text) Cells(iTable + 1, "D") = WorksheetFunction.Clean(.cell(3, 2).Range.Text) Cells(iTable + 1, "E") = WorksheetFunction.Clean(.cell(4, 2).Range.Text) End With Next iTable End With Set wdDoc = Nothing End Sub Last edited by Story11; 01-23-2015 at 04:28 PM. |
#11
|
|||
|
|||
HI,
Sorry to say I do not know "Word". I code Excel only. |
#12
|
|||
|
|||
@charlesdh: Thank you for your reply. Let us just assume that it's an Excel code... Can you just paste an example of an Excel post that can be accessed on both MAC and Windows (I mean can you just paste one of your Excel's codes that will run on both MAC and Windows)? I think I should get it from there. The problem with me here is that I don't know the right place to put a particular code (I mean I don't know which part of the code to place the code that will check for the operating system). So if you can forward a complete Excel code that will run on both MAC and Windows to me, I should be able to manipulate things from there. Thank you.
|
#13
|
|||
|
|||
@charlesdh: So I will appreciate it if you can give me a working Excel code that can run on both MAC and Windows. I'll study that, then try to find a way around mine. Thank you.
|
#14
|
|||
|
|||
Hi.
This code should do what you want it checkes to see what the "OS" is. [code Sub Test_Me() Dim TheOS As String TheOS = Application.OperatingSystem If Left(TheOS, 7) <> "Windows" Then Call Your mac code else call your windows code end if End Sub |
#15
|
|||
|
|||
@charlesdh: Can you please give me a full code of how to embed the Operating System check code you sent me? I mean can you incorporate that code you just sent me into one of your Excel codes so that I can see how it works? Thank you.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel 2008 vs Excel 2011 for Mac | nfotx | Excel | 0 | 12-05-2014 03:48 PM |
Index Hyperlink Workaround | Phil H | Word VBA | 9 | 10-30-2014 05:14 AM |
Workaround to have UserForm open from Web Link | Kirsti | Word VBA | 11 | 08-23-2012 07:05 PM |
Excel 2011 can't open old Excel 98 or Excel X files | FLJohnson | Excel | 8 | 05-09-2012 11:26 PM |
MAC PPT 2011 compatibility with Windows | stoneygeorge | PowerPoint | 0 | 08-05-2011 10:00 AM |