User Profile
Gilgamesh1964
Brass Contributor
Joined 5 years ago
User Widgets
Recent Discussions
How do I reference query data from an Access Macro
I have a simple Select query that returns a number of records. In my Access macro (just for clarification, I do mean Access Macro and not VBA) I want to check how many records the query returns in an IF statement. In this specific case the check would be on zero returned records, but being able to check non-zero in the future might be useful. I have tried various internet searches but the only results I found relate to VBA. Suggestions on how to do this check would be appreciated. Thank YouRe: Excel not able to write to Documents Folder
Hello Sorry for not getting back, I'm still trying to arrange a remote session (family member lives interstate). What I can tell you right now is that when one of the Excel files from the Documents folder is copied (using Windows Explorer) to the new location, it opens correctly and can be edited and saved. When this revised document is copied (again using Windows Explorer) back to the original Documents folder then the problem reappears.Excel not able to write to Documents Folder
Hello, I was asked to help a family member who was having trouble writing files with Excel, and I have to admit their problem has me totally flummoxed; so, I was hoping for some help myself. When they open any Excel file, it says it is Read Only. Looking at document info confirms this, but we can't turn it off. Trying a Save As on the file generates a 0-byte file in the Documents folder and we then get a message that the file already exists, and we can't overwrite it. I set up a new folder and added it to their Documents library. This works perfectly with Excel being able to Read/Write any file in there. I thought it might be user permissions in Documents, but Word and Notepad can Read/Write in there without issue. The permission settings look good, and the problem is limited to Excel. The problem started when they downloaded a CSV file, so the first thought was it was a virus. They sent me a copy of the file which I checked in an isolated VM environment, and it appeared OK. Both of us ran virus scans that came out clean. Key differences between our environments are: that they are using Microsoft 365 and Norton antivirus, and I'm using Office 2016 and Avast antivirus. But both can out of scans clean. They have done a full reinstall of Microsoft 365 but the problem with Excel persists. If worse comes to worst, then there might be a full PC rebuild (ouch), or migrate everything to the new folder I set up and delete the old Documents folder. Ideally, I would like to remedy the cause of this issue which is why I'm asking for help.Re: Problem creating multiple IMAP accounts from the same e-mail provider
RohanWiese84 Sorry for the delay in responding, for some reason I didn't get a notice of your response. 1) The only restrictions are on the number of e-mails that may be sent/received. I can have unlimited accounts/identities/aliases. 2) See 1 3) Each account is setup with different credentials, I consider this a normal thing to do. 4) I do have the correct IMAP Configuration, as I noted in my original message, I have successfully set up the accounts on my phone and another windows desktop mail utility. 5) There are no network connectivity issues (the phone and the other desktop utility function flawlessly) 6) See 4. But I'm after a solution to enable all of the accounts in Outlook 2016 as that is my preferred client. 7) No such restriction exists 😎 There is no mention of OAUTH at my mail provider, so I'll assume it is not available (also see below) 9) See 1 10) I am in the process of consolidating multiple accounts into a smaller number. Previously I had seen using POP3 successfully for the large number of accounts but now I want to use IMAP instead. Merging them further or using email forwarding will not be an option. As you can see in my response to 4, the multiple accounts can be set up on my phone which leads me to surmise that the problem lies within Outlook 2016. I didn't explicitly mention it in my original post, but the problem also occurs when the second account being set up is with a totally different e-mail provider. i.e. I set up the first account with my regular provider, then I try to set up an IMAP account using Microsoft's e-mail service. And I end up with exactly the same problem as when trying to set up two accounts with the same provider.Problem creating multiple IMAP accounts from the same e-mail provider
Hello, I'm using MS Outlook 2016 desktop application, specifically Microsoft® Outlook® 2016 MSO (Version 2403 Build 16.0.17425.20124) 64-bit (so not Office 365 or web-based outlook). I am having issues creating more than one IMAP account. I have successfully set up the multiple accounts on my phone as IMAP and another desktop mail utility but I'm only having trouble with Outlook 2016. When trying to create the account I get consistently an error message "We couldn't create the Outlook data file. Make sure the disk you're saving to isn't full or write-protected and that the file isn't being used". Now I don't have control of the location and it goes to C:\Users\(my userid)\AppData\Local\Microsoft\Outlook and there is plenty of space on the disk. The thing is, is that the file is being created and it is being displayed in the folders pane in Outlook. In addition, the setup is sending the standard Outlook Test Message and that test message is being received by the accounts in question. If I delete the first account I created, I am then able to add one of the other previously failed accounts. But then I am unable to add back the account I deleted and get the same error. I am able to add all the accounts as POP accounts but that sort of defeats the purpose of sharing mail accounts across multiple devices. I look forward to suggestions on how to create these secondary IMAP accounts. Thank You.Is there a way to determine the location of the OneDrive exe file in code?
Hello I have some VBA macros that have sync issues with OneDrive for Business. I have found that I can use the /shutdown and /background switches to stop and restart OneDrive as needed; but I still need to find the location of the OneDrive.exe file to run these switches. If it was only me I could find the file and hardcode the location, but other people may be using the macros on different PCs with different locations; so I'm looking for a way to dynamically find the location. Are there any environment variables, shell command, registry locations, etc I can use to find this? Thanks in advanceRe: Accessing SharePoint Text file via VBA
Thank you for taking the time to respond. I wasn't doing anything in VBA to authenticate. Our browsers, teams, OneDrive can access SharePoint after a normal logon to our domain with no further authentication requests, so some sort of federation exists. My (obviously mistaken) belief was that would be sufficient for VBA, and I wouldn't need anything else. I have looked at the links you provided and the details there are (currently) a bit much for me to worry about. My need to read from SharePoint would have added a "nice to have" feature rather than a "must have" feature, so for the time being I may let it drop.Accessing SharePoint Text file via VBA
I originally posted this query in the SharePoint community a while ago but didn't get a response so I'm posting here. I have a VBA macro in MS Project that needs to read a text file located on SharePoint. I have tried two different methods of opening the file (code below [https replaced with xxxxs as it wouldn't let me post]), both of which work fine on a local copy but fail when using SharePoint. The SharePoint I'm using is not an in-house system but is using Microsoft's SharePoint service offering. I have tried names using %20 instead of spaces and just using spaces with the same result. In my searches I have found multiple old references (10 years plus) about mapping drives or using UNC names. But mapping drives or using UNC are not possible with the SharePoint we use (I have tried). I have also found this - https://learn.microsoft.com/en-us/previous-versions/office/developer/sharepoint-2010/ms470176(v=office.14) that originally gave me some hope, but that is for VB and not VBA. I did consider OneDrive but the location of the file differs depending on whether the user has set up OneDrive sync or OneDrive shortcut from SharePoint. Plus, I need an option that will work regardless of a OneDrive connection being set up or not. Thanks in advance for any assistance. ======================================================= 'Const strLookup As String = "xxxxs://xxx.xxx.com/sites/xxxx/Shared%20Documents/xxxxx/xxxxx/My%20Text%20File.txt" ' Const strLookup As String = "\\xxx.xxx.com/sites\xxxx\Shared Documents\xxxx\xxxx\My Text File.txt" ' Const strLookup As String = "C:\Temp\My Text File.txt " intOutFileNum = FreeFile Open strLookup For Input As #intOutFileNum Line Input #intOutFileNum, strDataLine ' read in first line MsgBox (strDataLine) Close #intOutFileNum Const ForReading = 1, ForWriting = 2, ForAppending = 8 Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Set objFileSystem = CreateObject("Scripting.FileSystemObject") Set objFileSystem = CreateObject("Scripting.FileSystemObject") Set objTextStream = objFileSystem.OpenTextFile(strLookup, ForReading, False, TristateFalse) strDataLine = objTextStream.ReadLine MsgBox (strDataLine) objTextStream.CloseAccessing SharePoint Text file via VBA
I have an MS Office app that needs to read a text file located on SharePoint via a VBA macro. The specific app shouldn't matter but if you are interested it is MS Project. I have tried two different methods of opening the file (code below [https replaced with xxxxs as it wouldn't let me post]), both of which work fine on a local copy but fail when using SharePoint. The SharePoint I'm using is not an in-house system but is using Microsoft's SharePoint service offering. I have tried names using %20 instead of spaces and just using spaces with the same result. In my searches I have found multiple old references (10 years plus) about mapping drives or using UNC names. But mapping drives or using UNC are not possible with the SharePoint we use (I have tried). I have also found this - https://learn.microsoft.com/en-us/previous-versions/office/developer/sharepoint-2010/ms470176(v=office.14) that originally gave me some hope, but that is for VB and not VBA. I did consider OneDrive but the location of the file differs depending on whether the user has set up OneDrive sync or OneDrive shortcut from SharePoint. Plus, I need an option that will work regardless of a OneDrive connection being set up or not. Thanks in advance for any assistance. ======================================================= 'Const strLookup As String = "xxxxs://xxx.xxx.com/sites/xxxx/Shared%20Documents/xxxxx/xxxxx/My%20Text%20File.txt" ' Const strLookup As String = "\\xxx.xxx.com/sites\xxxx\Shared Documents\xxxx\xxxx\My Text File.txt" ' Const strLookup As String = "C:\Temp\My Text File.txt " intOutFileNum = FreeFile Open strLookup For Input As #intOutFileNum Line Input #intOutFileNum, strDataLine ' read in first line MsgBox (strDataLine) Close #intOutFileNum Const ForReading = 1, ForWriting = 2, ForAppending = 8 Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Set objFileSystem = CreateObject("Scripting.FileSystemObject") Set objFileSystem = CreateObject("Scripting.FileSystemObject") Set objTextStream = objFileSystem.OpenTextFile(strLookup, ForReading, False, TristateFalse) strDataLine = objTextStream.ReadLine MsgBox (strDataLine) objTextStream.CloseRe: Is there an alternative to Auto_Open for when MS Project lauches.
Hello I won't actually provide all the code but here are key parts I use the following to get to the CustomUI file strFN=Environ("LocalAppData") & "\Microsoft\Office\MSProject.officeUI" If the file does not exist then my code creates it. I then treat this file as a simple text file, scan it to an appropriate place, and then insert my own xml code in for the new ribbons I'm adding such as Print #intOutfile, "<mso:tab id=""MyTab"" label=""New Tab"">" Print #intOutfile, "<mso:group id=""MyGroup"" label=""New Group"">" Print #intOutfile, "<mso:control idQ=""mso:ChangeWorkingTime"" size=””small”” visible=""true""/>" Print #intOutfile, "<mso:control idQ=""mso:TaskOutdent"" size=””small”” visible=""true""/>" Print #intOutfile, "<mso:control idQ=""mso:TaskIndent"" size=””small”” visible=""true""/>" Print #intOutfile, "</mso:group>" Print #intOutfile, "</mso:tab>" Between MS Project sessions it appeared that MS Project was rewriting the MSProject.officeUI file to add autoScale to the group and remove the size from the control such as <mso:group id="MyGroup" label="New Group" autoScale="true"> <mso:control idQ="mso:TaskIndent" visible="true"/>" Now this is where I need to apologise. It has been some time since I last thought about this issue and I thought using an AutoExec style macro might help to rewrite the ribbon when MS Project starts. Hence the reason for my original post. However, in the intervening time project has been patched a couple of times and it now appears that MS Project is not removing the formatting. So I'm still curious as so methods of running code on starting MS Project (desktop version) but as far as the specific formatting problem I was having, that has been solved. Thank you all for your assistance.Re: Is there an alternative to Auto_Open for when MS Project lauches.
Thank you for responding. Unfortunately we only use Project Desktop and don't have access to Project Online or Server so enterprise options won't work for us. I did put Auto_Open into the local global.mpt file but it didn't run when launching project. I do have a separate file with a macro that installs the ribbons so that part is covered. But MS Project removes some of my ribbon formatting between sessions, so I thought if there was something that runs are startup I might be able to reinstate the formatting each session (testing would be required to see if it would work). Asking the PMs to run this file once every time they start Project probably won't fly, so we may need to live with the lost formatting. 😞Re: Is there an alternative to Auto_Open for when MS Project lauches.
Hello, Unfortunately there has been no response so I'm now assuming that having a macro run when launching MS Project is not possible. I was hoping for something along the lines of AutoExec is MS Word. However I will still thank those who took the time to consider this query.Is there an alternative to Auto_Open for when MS Project lauches.
Hello, I want to have a macro in my global.mpt that will activate when MS Project launches regardless of the schedule file. I'm only looking for it to happen that once (to modify the xml in some custom ribbons) and do not need it to activate for each file open. Is there such a macro entry that I can use? (I'm using MS Project Online Desktop) Thanks in advanceIssue with Resource Calendar exceptions and their Base calendar
Hello I'm having a little problem with changing a resource Base Calendar where a resource already has exceptions. I think it is best described with a little scenario There is a project calendar of 5 working Days (Mon-Fri). Most project resources work this standard week, so they inherit the project calendar as their Base Calendar. There is one Team that only works three days (Wed-Fri), so a calendar has been created where the “Work Weeks” has Mon and Tue as non-working time. This calendar has been assigned as the Base Calendar for each member of that team. There is one week only where one member of that team is able to work on a Monday as well as the three standard days, so a resource calendar has been created for them noting that extra day’s availability as an Exception. This resource still has the three-day work week as their base calendar. So far so good, everything is working with tasks being scheduled for their expected days. Now here is the problem In this scenario that team has changed to working Tue to Thu instead (still three days). I then update the work weeks on the special team calendar to be Tue-Thu instead. This special three-day team calendar is still the Base Calendar for all resources in that team. Resources in that team that do not have any exceptions now have their work scheduled for Tue to Thu. However, that resource with the one day exception is still being scheduled for work Wed-Fri with no work on Tue. It “appears” that a snapshot of the Base Calendar was applied to the resource at the time the exception was made with changes to the Base Calendar no longer being applied. I would have thought that by changing the Work periods in the Base Calendar that would automatically propagate to all of the resources that use it. Am I missing something? Is there anyway to force it to propagate through? I do have a work-around whereby I manually change the base calendar for the resource to something else and then change it back to the special three day calendar. This retains the exceptions for the resource but appears to reset the Base component. Thanks in advanceRe: What algorithm does MS Project use when calculating BCWP
Paul_Mather Thank you for responding but I am getting some results that don't align with that description. (I hope you are able to read the attached images) These two tasks were identical when baselined, it is the actuals and remaining work forecast that differs. I believe the top one is behaving as per that description you referred to, but I would expect the bottom one to have a BCWP of $5.50 rather than $5.61. This was done with: Microsoft Project Online Desktop Client MSO (16.0.14326.20936) 64-bit Version 2108 (Build 14326.20962 Click -to-run) (This is from the Semi-Annual Enterprise Chanel) It was getting this result that lead to my initial post. I repeated the same test with Version 2204 (Build 15128.20248) Microsoft Project 2016 MSO (Version 2204 Build 16.0.15128.20240) 64-bit. (My home version of MSP 2016 Professional) With this second test I get the expected result of $5.50 on the second task. Is this a bug with the Microsoft Project Online Desktop version?What algorithm does MS Project use when calculating BCWP
Specifically I'm interested where the Earned Value Method is set to Percent Complete. I have been having a play around with a couple of simple short duration tasks. Each is 10 days where the work is front loaded before being baselined. I set the status date half way through. On the first task I recorded progress pretty much to plan. On the second task I delayed the start by 4 days then in the one day left of the week I only recorded actual work equivalent to half that was planned on the first day. I also extended the duration of this task to 20 days. When looking at the Time-Phased EV fields in a Task Usage view on the first task I see what I expect to see. On the second task I see Cumulative Percent Complete as 5% (correct for one day in 20). If it had followed baseline duration then it would be 10% rather than 5%. However the BCWP for the task is a bit more than the half the BCWS for the first day of the task. Given that the actual work was half of the baseline work, I would expect BCWP to be half of BCWS. For Earned Value Method = Percent Complete, MS Project obviously takes into account: Baseline Duration Time-Phased Baseline costs Current Duration Cumulative Percent Complete There may be more that I have not considered but is there any documentation I can reference that describes the algorithm that Project uses in calculating BCWP so I can get a better understanding of what to expect. ThanksServer 2008 R2 WSUS no longer syncing with Microsoft
Hello I have an old 2008 R2 licence I use at home (in a VM) to run a WSUS 3.0 server to patch my systems rather than downloading direct from MS. I feel this gives me better control of patching (e.g, No Preview patches in Windows 10, etc.). However earlier this year the synching to Microsoft to find new patches stopped working and it took me awhile to get around to it. I couldn't figure out why but thought that some config or patch may have corrupted my system so I rebuilt a new 2008 R2 VM to reinstall WSUS from scratch. During configuration to sync with Microsoft I now get the following error. ============================================================== --------------------------- Synchronization Error Details --------------------------- WebException: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. ---> System.Security.Authentication.AuthenticationException: The remote certificate is invalid according to the validation procedure. at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request) at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRequest request) at Microsoft.UpdateServices.ServerSync.ServerSyncCompressionProxy.GetWebResponse(WebRequest webRequest) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at Microsoft.UpdateServices.ServerSyncWebServices.ServerSync.ServerSyncProxy.GetAuthConfig() at Microsoft.UpdateServices.ServerSync.ServerSyncLib.InternetGetServerAuthConfig(ServerSyncProxy proxy, WebServiceCommunicationHelper webServiceHelper) at Microsoft.UpdateServices.ServerSync.ServerSyncLib.Authenticate(AuthorizationManager authorizationManager, Boolean checkExpiration, ServerSyncProxy proxy, Cookie cookie, WebServiceCommunicationHelper webServiceHelper) at Microsoft.UpdateServices.ServerSync.CatalogSyncAgentCore.SyncConfigUpdatesFromUSS() at Microsoft.UpdateServices.Serve ============================================================== If someone knows how to fix this please let me know. I realise that the server software and WSUS version I run are no longer supported by Microsoft but does anybody know if changes at MS have stopped WSUS synching altogether? I haven't upgraded the software because, well, the current licencing models make it prohibitively expensive just to run a patch server. Thanks in advance
