To call a VBA script from a T-SQL procedure
I have the following (unusual) task: I need my T-SQL procedure to somehow call a VBA script. The VBA should copy all content of a certain sheet of an input Excel file into a certain sheet of an output Excel file. The physical full path of the input Excel file is available only in the T-SQL procedure. Therefore, the procedure should somehow pass the file path into the VBA script. How can I do that?
Outlook 2016/2021 remains running after CreateObject() from VBA in Version 2412 and later
Hi everyone, I’d like to report a behavior change that affects Outlook (Classic) in Office 2016 and 2021, starting from Version 2412 (Build 16.0.18324.20168). We use VBA to check whether Outlook is installed and configured for sending/receiving mail. We do this at startup of MS Access (2016, 2021 and 365) apps on Windows 11, but I guess this will be the same out of Excel, Word,... . The check is simple and basically does nothing like this: Dim objOutlook As Object On Error Resume Next Set objOutlook = GetObject(, "Outlook.Application") If objOutlook Is Nothing Then bolOutlookIsThere = False Else bolOutlookIsThere = True Set objOutlook = Nothing End If On Error Goto 0 In Build 16.0.18227.20162 (Version 2411) and in Microsoft 365 Outlook, this results in no lasting Outlook process - the instance is released properly. However, starting with Version 2412, this same code causes OUTLOOK.EXE to remain running in Task Manager after execution. The System tray shows up a grayed-out outlook symbol saying "outlook is going to be closed" --- forever. If Outlook had been started manually before running this code, everything works fine, that is: the extra instance will be released and a subsequent manually closing of Outlook will remove the whole. This causes problems in scripts and services that expect to perform a "clean" check without side effects. This same COM automation pattern works exactly as expected with both Excel and Word - they terminate automatically when the last object reference is released (via Set = Nothing), assuming they were not already running. Outlook, however, remains running in memory starting with Version 2412, even though no UI is shown and all references are cleared. This strongly suggests that Outlook now maintains additional internal session state or background services that prevent the process from terminating - unlike other Office applications. Confirmed behavior: Office 2016/2021, Version 2412+ -> OUTLOOK.EXE remains in memory. Office 2016/2021, Version 2411 -> no leftover process. Microsoft 365 Outlook -> behaves as expected. Excel, Word -> behave as expected Can anyone from Microsoft confirm whether this is an intentional change in COM/session behavior (possibly due to caching or MAPI preloading)? If so, is there a recommended workaround for non-interactive detection of Outlook availability? Is anyone else able to duplicate this behavior and/or able to find a workaround (other than downgrading Office to Version 2411)? Thanks in advance!
Looking for Excel macro files alternatives for tablets
Hi, Recently, I created Excel files with macros on Windows in order to be completed by people. It has all features I need : protection, data verification, full protection after data completion and saving in the correct folder. I have been asked : Is it working on Mac and on tablets ? The answer found is : macros cannot be executed on Android and IOS, and they need modifications to be run successfully on Mac (I take the simple case of the paths). In the case of investing money in tablets to be used easily, Android and IOS, according to your experience, what is the best way to have people completing data with the requirements (see below) ? My preference goes to Excel as data can be used for graphics and data analysis, unfortunately Excel macro files cannot be used on tablets... I am opened to other applications and softwares. Requirements : people can enter data and correct it without modifying the document after completing yes to the question : Data are complete ? Data cannot be modified Data can be modified only by authorized people only Data can be viewed by everybody It needs to be usable offline, in case of internet disruption Data completed may be assessed directly when entered (for example a person has written 10 and the number is maximum 8, a "non-conform" in red appears) Thank you for your answers
Word Mail Merge->PDF->Auto Name
I am trying to think of a way to automate a process. Part of this is in word and part with Adobe. Basically, here is the scenario. Every year all the staff in the company (about 350-400 employees) get evaluated and then their compensation is recalculated and an employment contract sent to them for the next year. The contract is in PDF form which they digitally sign and return to the company. Right now the process is like this: A spreadsheet is created with all the employee specific information. The spreadsheet data is merged with the standard employment agreement based on their title. The merge creates a different document for each employee which must then be saved (named with the employee name), converted to a PDF, and then a digital signature is requested via Adobe. I would like to automate this whole procedure. I would like to mail merge, save each document as a Word Doc and then a PDF under the employee's name. Once I have all of that, I would like to get PDF to send out all the documents via email (which will be in the spreadsheet too). I am a programmer so code does not scare me in the least. I am just not sure of the approach to take. Any thoughts? Thanks in advance.
MS Project Professional - VBA Color row based on field value
I have a master project with code below. What it does it: whenever I change the field Text12 to "OK" or "NOK" (and other words) it colors the whole row with a specific color (see Module1). It works great at a master project level, that is, in tasks directly in this master project. (How it works: It checks for changes in the Text12 column and sets a bool to true. Then, in Proj_Change, it applies the colors to the correct task row) However, it doesn't work at a Subproject level. I've added Debug.Print to each sub, and when I edit a Subproject's task, it only calls up to App_ProjectBeforeTaskChange. Is there a way to solve this? Detect changes in Subprojects and color the rows? I've tried running the ApplyColor sub directly from the App_ProjectBeforeTaskChange sub, but VBA says "this method is not available in this situation". Also tried using a Timer, didn't work, same error. (PS: I also posted this on Stack Overflow but I couldnt get help, so I'm posting this on other forums) ThisProject: Private Sub Project_Open(ByVal pj As Project) InitializeEventHandler End Sub Module1: Regular Module Option Explicit Dim EventHandler As EventClassModule Sub InitializeEventHandler() Set EventHandler = New EventClassModule Set EventHandler.App = Application Set EventHandler.proj = Application.ActiveProject End Sub Sub ApplyColor() Dim t As Task Set t = EventHandler.ChangedTask If Not t Is Nothing Then SelectRow Row:=t.UniqueID, RowRelative:=False Select Case EventHandler.NewValue Case "OK" Font32Ex CellColor:=14282722 'green Case "NOK" Font32Ex CellColor:=11324407 'red Case "PROGRESS" Font32Ex CellColor:=65535 'blue Case "REPEAT" Font32Ex CellColor:=15652797 'yellow Case Else Font32Ex CellColor:=-16777216 'no color End Select End If End Sub EventClassModule: ClassModule Public WithEvents App As Application Public WithEvents proj As Project Public NewValue As String Public ChangePending As Boolean Public ChangedTask As Task Private Sub App_ProjectBeforeTaskChange(ByVal tsk As Task, ByVal Field As PjField, ByVal NewVal As Variant, Cancel As Boolean) If Field = 188743998 Then 'Custom field Text12 Set ChangedTask = tsk NewValue = NewVal ChangePending = True End If End Sub Private Sub Proj_Change(ByVal pj As Project) If ChangePending Then ApplyColor ChangePending = False End If End SubAccess fixes released in Version 2410
Initial release Version 2410 (Build 18129.20116). Bug Name Issue Fixed Memory leak when using the Properties collection in VBA code Every time there was a reference to the Properties collection (e.g. obj.Properties("Text")) in VBA code, Access would leak memory. If this was done a large number of times, the memory usage of Access could increase significantly. Please continue to let us know if this is helpful and share any feedback you have.
Inconsistent Results Referencing Nested Subreport Property
I have placed several subreports in an unbound report's Report Header. Each subreport has two subreports. I'm trying to get the value of the Top property of the first of these sub-subreports, but I'm getting inconsistent results. The reference I'm (currently) using in the OnLoad event of the main report is: intSubSubTop = Me.Controls(strSub1).Report.Controls(strSubSub1).Top This works perfectly when I switch from Design View to Print View, but I get a runtime error (2455: the expression "has an invalid reference to the property Form/Report") when I open the report from the navigation pane. What's the difference between these two actions? I've tried different variations to no avail. What do I need to do to make it work consistently? I'm using Access 2010 on Windows 10 Pro
