SendKeys dialog box - close file

[neveu]

Hello,

i have a file that i automatically set to load another file.
after the file is loaded i would like to automatically close the initial file.

i have used the below code to close the file but the problem is that i have a addin that automatically prompts a message box to clasify the file before closing.

i was thinking to use the sendkeys ( i need to press tab and then enter to chosse the "NO" button on the message box that apperears) for this but it doesn't seem to be working.

can you please look into the code and advise?

Private Sub Workbook_Open()
WkbkName = ThisWorkbook.Name
curr_path = ActiveWorkbook.Path
now_stamp = Format(Now, "DDMMYY")
If Dir(curr_path & "\" & now_stamp & "_SOX_PRT_SCRNS_" & Application.UserName & ".xlsm") <> "" Then
Workbooks.Open Filename:=curr_path & "\" & now_stamp & "_SOX_PRT_SCRNS_" & Application.UserName & ".xlsm"
[B]Workbooks(WkbkName).Close False[/B]
[B]Application.SendKeys "{TAB}"
DoEvents
Application.SendKeys "{ENTER}"
DoEvents[/B]

Else
   ActiveWorkbook.SaveAs Filename:= _
        curr_path & "\" & now_stamp & "_SOX_PRT_SCRNS_" & Application.UserName & ".xlsm" _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    Application.WindowState = xlMaximized
MsgBox "Today's (" & Format(Now, "DD-mmm-yy") & ") SOX print screens file has been created. TO access it go to " & curr_path
End If
End Sub

 

[VoG]

Try instead

Application.DisplayAlerts = False
Workbooks(WkbkName).Close False
Application.DisplayAlerts = True

 

[neveu]

hi,

it's still there. it didn't work.

 

[VoG]

Maybe

Workbooks(WkbkName).Saved = True
Application.DisplayAlerts = False
Workbooks(WkbkName).Close False
Application.DisplayAlerts = True

 

[neveu]

Hi VoG,

unfortunatly it doesn't work.

the mentioned Addin prompts whenever closing an Excel file, so it doesn't matter if it is saved or not previously.

i presume the sendkeys will work but I'm missing something...but what? hm...

 

[VoG]

Maybe

Workbooks(WkbkName).Close False
Application.SendKeys "{TAB}", True
DoEvents
Application.SendKeys "{ENTER}", True
DoEvents

 

[neveu]

nope, unfurtunatly it doesn't work.

do you think it has to do to the fact that these are run automatically when the file is opened?

[I've put the code in the Thisworkbook module to be run when the file is opened]

Any ideas why is not running?

 

[shg]

Maybe ...

    '...
    Workbooks.Open Filename:=curr_path & "\" & now_stamp & "_SOX_PRT_SCRNS_" & Application.UserName & ".xlsm"
    Application.EnableEvents = False
    Workbooks(WkbkName).Close SaveChanges:=False
    Application.EnableEvents = True
Else
    '...