Working with workbooks shared via Teams/Sharepoint
Hello Excellers, I need some insight on an issue that I am not sure what the source is... A) We sometimes share workbooks via Teams. You know when you are in a particular chat and next to the name of the chat at the top of the screen you see Shared and then you see Files button a bit below the Shared menu and when you click on that Files button you will see a list of what workbooks are shared. B) So I wrote some VBA code to download a copy to the Downloads folder like that: Dim RetVal As Long Dim SharePointFileURL As String Dim LocalDownloadPath As String Dim FileName As String RetVal = URLDownloadToFile(0, SharePointFileURL, LocalDownloadPath & FileName, 0, 0) If RetVal = 0 Then MsgBox "File successfully downloaded to: " & LocalDownloadPath & FileName, vbInformation This will message be replaced by the code we need to run... but for now I needed an indicator that it finished downloading. Else MsgBox "Failed to download the file. Please check the URL or your network connection.", vbExclamation End If So RetVal is = 0, and I do see the workbook in the \Downloads folder and it has a size of 4KB and the correct Date Modified time stamp, BUT When I try to open the workbook via the Excel desktop application I get the message: Excel cannot open the file "File name here" because the file format or file extension is not valid. Verify that the file is not corrupted and that the file extension matches the format of the file. The workbook should be 34KB in size and that 4KB file is not what I need??? Any ideas how to manage this, saving a workbook from a SharePoint / Teams file location to the computer so that we can run VBA code on it. GiGiI need to learn how to use the LET function in Excel...
Hello Excellers, I am using a complex If statement to figure out if a machine is ready to receive work. I need to check if the machine is online, or broken down, or off line for some reason or another, Has a Human worker to work on the machine, and on and on... I am thinking would a LET function help in making the IF function more accurate, and less complicated? I have not worked with the LET function, and I think I would like to learn about it. Any nice resources for examples and tutorials? Thanks in Advance! GiGi!
MS Excell application for Client database working under Windows XP.
Hi all, In my company we have a MS Excell application for Client database working under Windows XP. For Windows 10 it wrks now under a virtual box. I want it to work with Windows 11! How is the best and efficient way to do that? Hope someone can help me out. Thanks for cooperating. Eric
Is the Excel Review -> Optimise feature available through Microsoft.Office.Interop.Excel
Hello Excel community, We have a client with a large collection of Workbooks that they copy every year to produce the next 'generation' of workbooks. Many of these workbooks are overweight with respect to file size, and performance. Is it possible, using dotnet and the Excel Interop COM objects to invoke the Optimise wizard that is available in the Excel user interface? Review -> Performance -> Optimize all
How to compare 2 excel sheets with an Unique identifier?
Hi! How do I compare values in sheet 1 and sheet 2, and display it on sheet3? Example: Sheet 1 has the following columns: ID Age Address Sheet 2 has the following columns: Age of bith Street Global ID How do I, on sheet 3, make the Excel able to compare for example, ID on the sheet 1 , it searches for a match in the sheet 2, and if it finds a match it says "match or no match" on sheet 3?
Enabling macro in trust center
Under the Trusted Center, Macro Settings and Macro Settings dialog the “Enable VBA Macros” has been selected along with “Enable Excel 4.0 macros when VBA macros are enabled” has selected. Along with this I am using the Personal.XLSB file to hold the macros. I have attempted multiple times to enable the macros within the workbook with no success. I first open the Personal file then the workbook of interest. Upon opening I do not receive a header to enable the macros. I then attempt to run a macro and continuously receive the error message that I must enable the macro and reopen the workbook.I need some help digitising our paper order system, but struggling with the code!
I have 4 sheets: 1 where we fill out the orders our students place, 1 where we calculate how much we owe the retailer,1 with the price list in case it changes and 1 for students with discount. Would anyone be able to help with the following? Link the items to the price list and the amount ordered. It needs to show correctly in the PRICE column for the item if a student orders 1, 2 or 3 items. If DISCOUNT = yes, in the PRICE WITH DISCOUNT column, I need the price to be calculated with an 80% discount In the 'order to retailer' tab, I want the excel to see how much of each item is ordered and make a sum of the price WITHOUT discount I want the data (student name, total order amount, amount with discount) from the order sheet to automatically go into the discount sheet if I selected 'yes' to discount and also for the amount that was discounted to be be displayed in the 'to be paid by the city' (students with discount only pay 1/5th and the city pays 4/5th) Please would someone be able to assist? Thanks a million!!!
OLAP connection Open XML adds x: namespace which is preventing slicer to work
I serialise the excel workbook with C# OpenXml to dynamically change the connection.xml file so users can connect to olap cube tabular in pbi. So I programmatically set a connection string for the user this way. All was working fine until the user started to insert slicers to the pivot table. I noticed in the connection.xml the connections xml tag has x: namespace added by OpenXml. eg <x:connection></x:connection> According to doco "When the object is serialized out as xml, it's qualified name is x:connection." https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.connection?view=openxml-3.0.1 Once I removed these x: namespace from the connection xml tag slicers worked. <connections><connection></connection></connections> Is this a bug in excel ? Are there plans to fix this? OpenXml is a lib provided by Microsoft and adds the x: namespace and excel cant handle the namespace, Reporting this bug and to see when a bug fix will be release for excel to use slicers when you dynamically change the connection string using openxml. Thanks
