GetOpenFilename Arguments DONT work?

E

ectoid

Board Regular
Joined
Jan 18, 2005
Messages
97
Hey all,
I'm running Excel for Mac 2011, VBA 14.1.4.
I'm trying to use the GetOpenFilename method and have realized that if I use any arguments I get Runtime Error 1004 'Method GetOpenFilename of Object '_Application" failed'

The code that fails is:
Sub Test()

'Dimension variables for the Macro Initiate worksheet
Dim ALName As Variant
Dim FilterIndex As Integer
Dim Filter As String, Title As String

Windows("Machine Reconcilliation Macro.xls").Activate

'Set RowVar = Sheets("MacroInitiate").Range("d2")

'Do Until RowVar.Value = ""

'ALName = List of specific call center's Agents exported from console as .CSV
Filter = "CSV Files (*.csv),*.csv"
FilterIndex = 1
Title = "Open CSV file of Call Center's Agents Exported from console"
With Application

ALName = .GetOpenFilename(Filter, FilterIndex, Title)
'ALName = .GetOpenFilename()
End With

' Exit on Cancel
If ALName = False Then
MsgBox "No file was selected."
Exit Sub
Else
Workbooks.Open Filename:=ALName
End If

MsgBox "File Opened."

End Sub

The code that works simply alternates the comment of the "ALName = " lines.


Any ideas?
Thanks,
-E
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Hi

I don't use a Mac I'm afraid (and your code does work on a PC with xl2010) but I wonder if naming your variables after the actual argument names is the cause? Easy enough to test, just rename your 3 argument variables to eg

strFilter
intFilterIndex
strTitle

and see if it still fails
 
Upvote 0
Thanks for replying.
I changed the argument names...No Dice.
Still get the same error.
It's got something to do with Mac I bet.

Somebody has had to figure this out though, no?
 
Upvote 0
Re: ANYONE? Bueller? GetOpenFilename Arguments DONT work?

I can't believe someone hasn't figured out how to make arguments work on a Mac, but I'm not finding it on Google.

So is my only option to create a version for Mac where you have to know/remember what each .getopenfilename file the user is supposed to enter?
And then another for windows where the open filename box is more explicit?

Doesn't seem right.
Thanks.
-Eric
 
Upvote 0
The code that works simply alternates the comment of the "ALName = " lines.
Click to expand...
Could you post the code that works on your Mac?
 
Upvote 0
Re: ANYONE? Bueller? GetOpenFilename Arguments DONT work?

VBA file command are not Excel 2011's best feature.
The FilterIndex argument is ignored on Macs.

The Filter strings are odd in any case.
Code: 
uiFileName = Application.GetOpenFilename("TEXT")
will show all CSV files, but also .xls, .xlsm, and .xlsx

I find it easier to use Applescripts file chooser (via MacScript command) and conditional compilation.
Code: 
Dim ALName As String

ALName = "False"
#If Mac Then
    Rem code for Mac
    On Error Resume Next
    ALName = MacScript("choose file of type ""TEXT""")
    On Error GoTo 0
#Else
    Rem code for Windows
    ALName = .GetOpenFilename(Filter, FilterIndex, Title)
#End If

If ALName = "False" Then
    Exit Sub: Rem cancel pressed
Else
    MsgBox ALName
End If

http://developer.apple.com/library/...html#//apple_ref/doc/uid/TP40000983-CH216-SW4
 
Upvote 0
Hey Mike! Thanks that's awesome!
I was just poking around more too and found from a post in 2004 or so:

From the Help File:

FileFilter Optional Variant. A string specifying file filtering criteria.
In Windows, this string consists of pairs of file filter strings followed by the MS-DOS wildcard file filter specification, with each part and each pair separated by commas. Each separate pair is listed in the Files of type drop-down list box. For example, the following string specifies two file filters, text and addin: "Text Files (*.txt),*.txt,Add-In Files (*.xla),*.xla".

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.

So I removed the FileFilter argument (leaving it to default to All Files) and it worked fine.

I'm going to try out your code now.
Thanks again,
-E
 
Upvote 0
Re: ANYONE? Bueller? GetOpenFilename Arguments DONT work?

* is not wildcard on a Mac. Also, the Mac OS doesn't rely on file extensions. It uses 4 character "file type codes". That also introduces a problem for files saved with Windows Excel and simply transferred to a Mac.

In any case, see the discussion at the link below. While it is dated it should be just as relevant to Mac Office 2011.

http://forum.soft32.com/mac/GetOpenFilename-FileFilter-syntax-Mac-2004-ftopict84362.html

ectoid said:
I can't believe someone hasn't figured out how to make arguments work on a Mac, but I'm not finding it on Google.

So is my only option to create a version for Mac where you have to know/remember what each .getopenfilename file the user is supposed to enter?
And then another for windows where the open filename box is more explicit?

Doesn't seem right.
Thanks.
-Eric
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Close workbook with variable
Replies
2
Views
545
Ingemar
I
H
  • Question
Macro to limit browsing to specific File
Replies
2
Views
228
howard
H
C
  • Question
Setting the default save path with prompt
Replies
2
Views
318
CaptainGravyBum
C
H
  • Question
Macro to move csv files within-subfolder to folder "Old Templates"
Replies
2
Views
357
howard
H
H
  • Solved
Macro to Browse to C:\extract
Replies
3
Views
261
Micron
M

Forum statistics

Threads
1,214,584
Messages
6,120,384
Members
448,956
Latest member
JPav

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top