Link to home
Create AccountLog in
Avatar of tscott_72
tscott_72Flag for United States of America

asked on

GetOpenFile Function: A way to close dialog box without hitting Cancel or Close

Hi Experts,

I am using the GETOpenFile Function to call the Save File Dialog box. All is working well. Once the file pathway and name is entered, my code performs some other actions, such as copying and populating an Excel template to the indicated location, etc. These processes require about 3 mins.. The problem is the SAVE Dialog box stays open for the duration of the processing. I would like to close the box before proceeding in my code.

Is there some code I could use to close the box?

I appreciate the help.
Avatar of VTKegan
Flag of United States of America image

Can we take a look at your code to see what you are doing as of now?
Avatar of tscott_72


Here you go:

Thank you.
Public Function GetOpenFile(Optional varDirectory As Variant, _
Optional varTitleForDialog As Variant) As Variant

Dim strFilter As String
Dim lngFlags As Long
Dim varfilename As Variant
' Specify that the chosen file must already exist,
' don't change directories when you're done
' Also, don't bother displaying
' the read-only box. It'll only confuse people.
lngFlags = ahtOFN_FILEMUSTEXIST Or _

If Forms!Form1!MARKET_COMBO = "ny" Then
varDirectory = "J:\Hospital Network Analysis (Rachael)\Interim Analysis"
varDirectory = "c:\"
End If


' Define the filter string and allocate space in the "c"
' string Duplicate this line with changes as necessary for
' more file templates.
strFilter = ahtAddFilterItem(strFilter, _
"Excel (*.xls)")
' Now actually call to get the file name.

varfilename = ahtCommonFileOpenSave( _
OpenFile:=False, _
InitialDir:=varDirectory, _
Filter:=strFilter, _
Flags:=lngFlags, _

If varfilename = "" Then

DoCmd.Hourglass False
Exit Function
DoCmd.Hourglass True

'copy shell xls to a new deal specific file'
Dim sSource As String
Dim excelvar As String
If Forms!Form1!MARKET_COMBO = "ny" Then
sSource = "\\oh2shared\reimbstrategyanalysis\HealthCare Analytics\Hospital Contracting\DEAL SUMMARY APPLICATION\templates\deal_summary_template.XLS"
sSource = "\\oh2shared\reimbstrategyanalysis\HealthCare Analytics\Hospital Contracting\DEAL SUMMARY APPLICATION\templates\deal_summary_template_not_ny.XLS"
End If

FileCopy sSource, varfilename & ".xls"

'open the logic for packets dbase and run the queries'
'Dim appAccess As Access.Application
    'Dim dbStr As String
    'dbStr = CurrentProject.Path & "\Logic for Packets.mdb"
    'Set appAccess = New Access.Application
   'appAccess.Visible = False
   ' appAccess.OpenCurrentDatabase dbStr
   ' appAccess.DoCmd.OpenForm "frm_automate_deal_summary"
'Set appAccess = Nothing

'transfer tbl_financial_calculations to spreadsheet'
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "tbl_financial_calculations", varfilename, True

Dim rs As DAO.Recordset
Dim ssql As String

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Set xlApp = New Excel.Application
Dim Sheet As Object

    With xlApp
        .Visible = False
    Set xlWB = .Workbooks.Open(varfilename & ".xls", , False)

Set Sheet = xlApp.ActiveWorkbook.Sheets("raw_data")

ssql = "SELECT tbl_data_tab.* from tbl_data_tab;"
Set rs = CurrentDb.OpenRecordset(ssql)
Sheet.Range("A2").CopyFromRecordset rs

Set rs = Nothing

End With

Set xlWB = Nothing
Set xlApp = Nothing

DoCmd.Hourglass False

If Forms!Form1!SYS_FAC_OPTION = 1 Then
excelvar = Forms!Form1!COMBO_FAC_SYS
excelvar = Forms!Form1!LST_RENEWAL_SELECTED.ItemData(0)
End If

Dim messagevar
messagevar = MsgBox("Your Deal Summary Report for " & excelvar & " has been successfully saved.", vbInformation, "Confirmation")

End If

End Function

Open in new window

Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Terrific! Problem solved.

Now the important part: LEARNING.
found this definition of DOEVENTS online:

DoEvents is used to force the application to yield it's use of the processor for one cycle (usually 50ms), it's commonly used to allow other events in your application to fire while you're performing a resource intensive operation.

Makes sense.

Thank you very much.
Generally, you can insert a DoEvents wherever you want to give windows a chance to catchup.  This is generally needed when you have screen updates in sequence with an intensive time consuming process.  I frequently add the line in loops as well.