CODEWITHSUNDEEP
excelexcel-2007vba
Iron Man
Iron Man

Reputation: 849

Handling the prompts of another Add-In

The following code, which is part of an Excel Add-In that I created, will create a file name of the current data extract that we have open.

Sub ExtractSave()
'
If InStr(LCase$(ActiveWorkbook.name), "extract") > 0 Then
    Exit Sub
Else
    Dim MyDir As String, fn As String
    MyDir = CreateObject("WScript.Shell").SpecialFolders("MyDocuments") & "\Extract Files" ' change this to valid path
    If Len(Dir(MyDir, vbDirectory)) = 0 Then MkDir MyDir
    fn = MyDir & "\Extract - " & Format(Now, "mm-dd-yyyy hh_mm")
    ActiveWorkbook.SaveAs Filename:=fn, FileFormat:=xlOpenXMLWorkbook
End If

End Sub

When I close the workbook, it does require a save to occur as changes will be made to the workbook. All of this works just fine. The issue is that the company has a .COM Add-In that is required to run on all documents. It labels the document footer with a specific security level identifier. While I do not need to bypass this, I would like to know if it is possible to write code that will replicate hitting enter when this box appears? There are two buttons, Yes and Cancel. The default button is Yes on the popup. I did try adding

Application.DisplayAlerts = False
If Me.Saved = False Then Me.Save
Application.DisplayAlerts = True

into the Workbook_BeforeClose private Sub, but the op still appeared. Also, if I am correct, this bit of code will not work for the Saved workbook because this is running from within the Add-In.

I have not been able to find any equivalent information for this particular issue, so any help would be appreciated. Also, I do not know what other information would be useful in your assistance to finding a solution, so please ask and do not down vote because I missed something.

Order of events:

  1. Import data into new workbook
  2. Run ExtractSave routine
  3. See the .COM addin popup (need to have YES button pressed, YES is the default button)
  4. User performs duties on the workbook
  5. Click on close
  6. prompted to Save the changes to the workbook (again, default is YES, need this "clicked")
  7. See the .COM addin popup again(need to have YES button pressed, YES is the default button)

What VBA code is available to automatically click on the YES buttons for the .COM add-in while saving the workbook?

Upvotes: 1

Views: 110

Answers (1)

Fadi
Fadi

Reputation: 3322

From comments:

Adding Application.SendKeys ("~") before this line ActiveWorkbook.SaveAs Filename:=fn, FileFormat:=xlOpenXMLWorkbook worked when the first save event (#2 from the list in the OP) occurred.

And For #7 .COM addin popup, adding Application.SendKeys ("~") in Workbook_BeforeSave event didn't help (I hope someone can help us to fix it):

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

     SendKeys "~"  

End Sub

Upvotes: 1

Related Questions