User Profile
JerryDNYC
Copper Contributor
Joined Sep 03, 2020
User Widgets
Recent Discussions
Re: Webservice function not working in Excel and generating #VALUE error after launch of python in excel
Sachin Jain I got fed up waiting for either a fix or a response from Microsoft, so decided to write a VBA function to use instead. Function WebSvc(sURL As String) As String Dim XMLHTTP As Object ' Create an XMLHTTP object Set XMLHTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0") ' Fetch the data With XMLHTTP .Open "GET", sURL, False .send WebSvc = .responseText End With ' Clean up Set XMLHTTP = Nothing End Function A quick FIND/REPLACE of WEBSERVICE with WebSvc fixed my problem. I'd much rather not have to use a Macro Function, but it works! I'm not that well versed in VBA, but be aware that I'm using 64 bit Excel. I don't know if it makes a difference here, but I am aware that sometimes the code could be different for 32 bit Excel. Reply here if you find that problem - and the fix!5.7KViews1like1CommentRe: Webservice function not working in Excel and generating #VALUE error after launch of python in excel
SergeiBaklan I track what beta version of Excel I have irregularly. My notes have the following information: 09/01/2023: Version 2309 Build 16.0.16827.20000 64-bit - Includes Python 09/09/2023: Version 2310 Build 16.0.16830.20000 64-bit 09/21/2023: Version 2310 Build 16.0.16907.20000 64-bit - WEBSERVICE problem surfaces 09/28/2023: Version 2310 Build 16.0.16924.20002 64-bit - WEBSERVICE problem NOT FIXED 10/04/2023: Version 2311 Build 16.0.16926.20000 64-bit - WEBSERVICE problem NOT FIXED Maybe that would help?5.9KViews1like2CommentsRe: Webservice function not working in Excel and generating #VALUE error after launch of python in excel
I sent feedback four days ago and it has not been touched! Very disappointed. I usually get a response within a few days. I didn't see the problem until Version 2310 Build 16.0.16907.20000 64-bit, and just updated to Version 2310 Build 16.0.16924.20002 and the problem persists. Very frustrating as I have a template that uses the FRED API to pull in financial data and not only is it now useless, but all the workbooks that use it are as well!5.6KViews0likes1CommentRe: Stockhistory function is suddenly blocked
I've been working with STOCKHISTORY and the Stock Data Type since they were introduced. I was in the middle of putting together a Stock Portfolio yesterday putting together a complex start and end dates for STOCKHISTORY when I abruptly got the #BLOCKED! message. I was logged in, and checked my account. Everything is in order, but I found it interesting that my 365 subscription just renewed on the first of this month. What I wonder is how is this not fixed quickly. It's been well over 12 hours and no fix? It makes me suspect of their security!5.6KViews0likes0CommentsRe: Excel's stock data type can't find several stock exchange indexes (i.e: FTSE, CAC 40, Hang Seng...)
I totally agree. It's incredible that the page you show above doesn't have a column for the symbol needed for the STOCKHISTORY function. And while the NAMES in the screenshot leolapa posted can be found using the Stock data type (which would have been MUCH more useable had he included the TEXT instead of just a screenshot), the "Ticker Symbol" for FOUR of the 14 names don't work with STOCKHISTORY! I don't understand why all the major indexes aren't listed in one page as well as the CORRECT Ticker Symbol needed to use with the STOCKHISTORY function!17KViews0likes1CommentVBA to delete Power Query Connections or Folder
I have a master workbook with 10 Power Query connections. That workbook is a Read-Only file, and has a macro that saves the workbook with a different file name. Once the file has been saved I want to delete all the queries. I've tried using ActiveWorkbook.Connections("Query - Report").Delete but it's not deleting the queries. Is there something else that needs to be done before being able to delete a query? Incidentally, all 10 queries are contained within a query folder named Reports. I am able to right click on the folder and delete all the queries that way. Unfortunately the VBA recorder doesn't record that action or the deletion of individual queries. I'd rather just delete the folder in VBA if that's possible. Thanks.Solved20KViews0likes9CommentsExcel Version 2204 Insider 3/16/2022 - ScreenUpdating Macro problem
I have a file I use daily that has a macro that starts with Application.ScreenUpdating = False to stop the updating of the screen because the macro switches to a few other worksheets. Of course, the value is set to True at the end of the macro. Yesterday I received the Insider Update (that added 14 new functions!), and now when the macro executes that line of code, the entire worksheet area - between the formula bar and status bar - goes blank! I tried running a repair on Office, but the problem persists. Anyone else seeing this?1.5KViews0likes0Comments
Recent Blog Articles
No content to show