OPEN.DIALOG and GetOpenFilename commands not working

I have been fighting between Visual Basic and XLM approaches to create a macro that can open many similar fixed width text files that have no delimiters so i don't have to go through the tedious import wizard on every one.

i have got the macros working when i type in the path to the file but i cannot get a function that allows me to choose a file from a dialogue box.

visual basic keeps saying method failed when i try

[/CODE] myfile = Application.GetOpenFilename("Text Files,*.txt")Applications [/CODE]

(sorry for a forum that has rules you are supposed to set off code they seem to make it impossible to figure out how to do that. i can't find a glyphic to click or a FAQ that tells me how.)

throws the following error:
Run-time error '1004':
Method 'GetOpenFilename' of object '_Application' failed

I don't know where you find tutorials on syntax and parameters with Visual Basic calls such as GetOpenFilename (for instance i have no idea why it takes the prefix Application. and whether other prefixes are possible) although in the thread seeking help finding this solution on the visual basic side, someone offered me the code that is throwing the error.

and when i went back to my XLM documentation and tried OPEN.DIALOG() instead of getting a dialog box it seems to be making a call to open the active document and then returns an error because a document with the same name is already open.

anyone know a way around these problems.

thanks

brian

Originally Posted by Bernie Deitrick

Are you using Visual Basic (VB), or writing a macro in Visual Basic for Applications (VBA)? If you are using VB, then you need to set a reference to Excel and create an excel application object, which will then have the .GetOpenFilename method

Not sure what i am writing in. i never got visual basic from the very beginning. i still use XLM whenever possible. I recorded a macro in Excel and then opened it in the Visual Basic Editor so i have no idea if the is VB or VBA.

So Application refers to the active application? so basically this is telling Excel to GetOpenFilename?

and any idea why this command keeps throwing an error when i believe - even though i don't understand why - that my code should be correctly entered to get a open dialogue box?

thanks

Originally Posted by jaslake

Hi riwiseuse

If you're using the Mac as your profile indicates, see this link...notice the Notes about the Mac half way down the discussion.
http://www.rondebruin.nl/mac/mac015.htm

read that page you linked and i still am not clear on what code i am supposed to type to get this macro to ask me for a dialogue box to choose what file i want it bring to the import wizard.

when i simply enter a path, the recorded macro works perfectly, but i cannot figure out how to get the macro to ask me for the input of a file from a finder dialog box to populate a filename variable.

(or in the XLM variety I was hoping to enter the command as a parameter of the OPEN.TEXT() function but i cannot even get OPEN.DIALOG() to run on its own.)

I have examples of the code from the macro i recorded from the Excel Virtual Basic Editor but I do not know how to post that denoted as code which the forum prefers. if anyone can tell me how to do that i can post the code.

thanks,

brian

Originally Posted by Bernie Deitrick

That would be VBA then.

Try just this, since Macs can't use the filter

Please Login or Register  to view this content.

that is what i tried and it throws and error:
Run-time error '1004':
Method 'GetOpenFilename' of object '_Application' failed

here is the code i used which just extends that slightly. got it cleaned up after the macro record:

Please Login or Register  to view this content.

and if i run the same code but just put a pathway to a file in place of the

Please Login or Register  to view this content.

e.g.

Please Login or Register  to view this content.

then it works just fine. opens the file. i am simply not able to call the dialogue box so i can choose different files. and have had equally no success with OPEN.DIALOGY form the XLM macro language which ought to do it also.

by no filter, do you me without the if IF testing for false? or . . . . not sure what you mean by filter?

PS i did try it without the IF function and got the same error but maybe that is not what you are referring to.

got it. so that works in visual basic (or is it visual basic for applications). not sure why it doesn't like those filters but works like a champ with them gone.

meanwhile i went back to XLM and this works if I go to macros and step in or run the program but throws an error if I call it with a command option key.

Please Login or Register  to view this content.

the error is the one reported above where somehow it takes the routine as an effort to open the document that is already open and active, i.e. the document containing the macro.

although i've checked and rechecked the target in the INSERT>NAME>DEFINE command this must have more to do with some problem releasing the command option combination i set (shift F) or some anamoly resulting from calling the macro with the key combination instead of just chosing TOOLS>MACRO>MACROS and the selecting the one i want to run (this list includes both XLM and VB macros) and then clicking "RUN" to the right where the various options "STEP-IN" etc. show up. If i do it that way it runs fine but not with the command key combo which apparently was my problem with the XLM approach all along. go figure.