Question involving using the Excel.Getopenfilename..

[WhiteZiggy]

How come when I call this it works, but if i leave the window and comeback it is never on top and I am forced to cntrl-dele and end task. I cant cntrl break or anything out of it. I dont see a way to make it modal.

Any suggestions on how to make/keep it on top?

The idea is that if you needed to copy a long path you would click your shortcut, copy it, and then click back to access and paste it into the filedialog box. But of course, on the click back to access I get a white screen...

DOH...

Any help is appreciated...

Thanks,
WZ

 

[TonyJollans]

Hi WhiteZiggy,

Can you give a bit more detail, please? I don't really understand what you are doing.

I don't think you can run an Excel Dialog and have it belong to the Access Application (if that's what you want) - it will be modal to the Excel Application which will start when you invoke the dialog (unless you already have it running).

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[WhiteZiggy]

Here is my code...

Sub ImportTemplate()
'Imports excel sheet
'asks for file
Dim strSQL As String
Dim strPath As String

DoCmd.SetWarnings False 'turns off deletion warnings

'clear the table
strSQL = "Delete * from MHIAData"
CurrentDb.Execute (strSQL)

'Lets user select path for MHIA upload
strPath = GetFilePath

If strPath = "" Then
DoCmd.SetWarnings True
MsgBox "File selection error. Please try again.", vbOKOnly, "Download from MHIA"
Exit Sub
End If

'Imports MHIA Data
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "MHIAData", strPath, True



'Clean table for all bad rows imported
strSQL = "Delete * from MHIAData where [Job Number] is null"
CurrentDb.Execute (strSQL)

'record history of download
Call AddDownloadHistory("MHIA", 1)

DoCmd.SetWarnings True
MsgBox "MHIA download complete.", vbOKOnly, "Download from MHIA"



End Sub
Function GetFilePath()
Dim xl As Excel.Application, fn
Set xl = CreateObject("Excel.application&quot

fn = xl.GetOpenFilename
If fn <> False Then
GetFilePath = fn 'do something with fn
End If

End Function

The problem is when I run this and the dialog pops up. I want to go to another explorere window and copy paste the LONG network address. When i click over to the other window I can copy . But when I click back to try and find the dialog I opened. I cant get it to open. I want to just get back to it.

Could it be that the excel object is invisible but the dialog isnt?

Thanks,
jeremy

 

[TonyJollans]

Hi Jeremy,

The Dialog belongs to Excel, but while it's on show, Access is waiting for a response from it (via the Excel app) so doesn't respond to any other input. When I tried it, I could switch to the Excel app and see the dialog, no problems. If you can't do that it's worth a try making Excel visible; beyond that I'm not sure what else given that it works for me.

Incidentally, it would be good practice to close the Excel app and set your xl object to Nothing when you're done with it.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[WhiteZiggy]

Thanks.

As I thought, and you pointed out, the reason my object wasnt working was because the excel was invisible. So I couldnt ever get back to it...

Thanks,

Jeremy