SendKeys dialog box - close file

neveu

Board Regular
Joined
Jan 27, 2009
Messages
225
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?


Code:
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Try instead

Code:
Application.DisplayAlerts = False
Workbooks(WkbkName).Close False
Application.DisplayAlerts = True
 
Upvote 0
Maybe

Code:
Workbooks(WkbkName).Saved = True
Application.DisplayAlerts = False
Workbooks(WkbkName).Close False
Application.DisplayAlerts = True
 
Upvote 0
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...
 
Upvote 0
Maybe

Code:
Workbooks(WkbkName).Close False
Application.SendKeys "{TAB}", True
DoEvents
Application.SendKeys "{ENTER}", True
DoEvents
 
Upvote 0
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?
 
Upvote 0
Maybe ...
Code:
    '...
    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
    '...
 
Upvote 0

Forum statistics

Threads
1,224,592
Messages
6,179,786
Members
452,942
Latest member
VijayNewtoExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top