Coding Problem with 'GetOpenFilename' .. Need Help

[SSJpn]

I have a problem with this line of code (my program keeps freezing)


I have a form with this in the initialize event

Set ExcelApp = CreateObject("Excel.Application&quot
ExcelApp.Workbooks.Open (FILE_PATH)
ExcelApp.Workbooks(FileName).Worksheets("SomeTab&quot.Select

And on a button click event I have this line of code

MultiFilePath = ExcelApp.GetOpenFilename
(this is where my program keeps freezing, the button is clicked and gets frozen in the 'pushed' state, you know, when its indented. I have to CAD out and end my app.)


I declare my ExcelApp object in a module

Public ExcelApp As Object



Is there something wrong with what I have done?

 

[RobBroekhuis]

What application are you programming in? Another VBA/Office? VB? Have you put some debugging code around your getopenfilename statement to see if the code even gets there?
Rob

 

[SSJpn]

Programming in MS Word.(You fill in the form and it outputs to Word and Excel).

I put some msgboxs around each line of code in the click event of that button. When you click the button the first message box pops up but not the second one. the only line of code between the 2 message boxes is the one i posted previously:

MultiFilePath = ExcelApp.GetOpenFilename


MultiFilePath is declared as text.

 

[TonyJollans]

Hi SSJpn,

Don't know if it'll help but ...

I just mocked this up with a bit of VBA in Word (but without a form) and the code did appear to hang. The reason it was hanging was that it was waiting for a response from the GetOpenFilename Dialog Box which was sitting there waiting but not visible to me. Flicking through windows with <Alt><Tab> turned it up quickly enough and closing it caused my code to resume.

I have no idea how to achieve this in code but maybe you know someone who does.

Enjoy,
Tony

 

[andrew299]

try adding

yourformname.StartUpPosition = 2

in to your code before you show the form
this may help

Andrew299

 

[combo]

Hi SSJpn,
as Tony pointed, the problem is because of hiding somewhere the open dialog. This happens after opening a workbook.
To avoid this you can either first display the dialog, or use two instances of excel, the second for displaying the dialog:

[tt]Set XL_1 = CreateObject(&quot;Excel.Application&quot
Set XL_2 = CreateObject(&quot;Excel.Application&quot
XL_1.Workbooks.Open (FILE_PATH)
MultiFilePath = XL_2.GetOpenFilename[/tt]

Both work, however I did only short test.

combo

 

[RobBroekhuis]

Combo,
I thought that the problem was that Word was the active application, and Excel runs behind Word. The number of open workbooks really shouldn't matter - the form is an application object that goes on top of any workbook windows.
I think SSJpn needs to find a way to make Excel the active application. Not my field of expertise, although the
Application.ActivateMicrosoftApp method may be helpful.
Rob

 

[combo]

Hi Rob,
I'm not so deep in windows (guess here is the problem and you are right).
I had the same &quot;freeze&quot; effect, I removed from SSJpn's code ...Workbooks.Open... line and the dialog became visible.
Now I can't get hidden dialog effect at all. Strange.

combo

 

[SSJpn]

You guys were right, the dialog box was sitting behind my form the whole time... I went through alt/tab and it worked fine.

Im gonna try out andrew299's suggestion real quick and see if it works.

 

[SSJpn]

Ok, I tried combo's suggestion and that seems to work so im all set.

Thanks so much for the help all!!~~


My program does work now but if anyone can figure out how to make that dialog box show without using another Excel Object like combo suggested I would love to know~

 

[TonyJollans]

Just a thought, but you are using the dialog box to obtain a filename from the user. Do you have to use the Excel dialog? Could you not use the Word equivalent?

Dialogs(wdDialogFileOpen) - use the Display method and pick up the Name property afterwards. Being part of your active application, this should always display on cue. Once you have the name you can pass it to your Excel application.

Incidentally, like combo, I find the hidden dialog effect only sometimes and can't pin down the circumstances.

Enjoy,
Tony

 

[TonyJollans]

Sorry, forgot to mention that Word doesn't return the path with the name - nor in any obvious place at all. Look at Application.Options.DefaultFilePath(wdDocumentsPath)

Enjoy,
Tony

 

[combo]

Hi all!
I've just discovered File Open dialog in word XP (NB., it is an office object, and is accessible in other office applications (at least in excel and powerpiont). The FileDialog object can be customized and explored. Here's the sample code:

[tt]Sub OfficeOpenDialog()
With Application.FileDialog(msoFileDialogFilePicker)
If .Show Then
MsgBox .SelectedItems(1)
End If
End With
End Sub[/tt]