Excel 2016

30 Topics

PowerShell Direct - Unable to open Excel workbook with Excel.Application ComObject
Hello All, Hoping someone may be able to enlighten me or provide an alternative. Running in a local PS session on a Win10 client with Office 2019 installed, I am able to run these commands, open, and use content from an xlxs file: $file = "C:\TestFiles\Test.xlsx" $excel = New-Object -ComObject Excel.Application $workbook = $excel.Workbooks.Open($file) <...more code here but not relevant to the conversation....> When I try to use the same command using PowerShell Direct on the host where the VM is running, I get this: <Enter-PSSession -VMName WIN10.....command already run and connected. User account connecting is a local administrator in the Workgroup joined VM...> [WIN10]: PS C:\Users\TestUser\Documents> $file = "C:\TestFiles\Test.xlsx" [WIN10]: PS C:\Users\TestUser\Documents> $excel = New-Object -ComObject Excel.Application [WIN10]: PS C:\Users\TestUser\Documents> $workbook = $excel.Workbooks.Open($file) Microsoft Excel cannot access the file 'C:\TestFiles\Test.xlsx'. There are several possible reasons: • The file name or path does not exist. • The file is being used by another program. • The workbook you are trying to save has the same name as a currently open workbook. At line:1 char:1 + $workbook = $excel.Workbooks.Open($file) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : OperationStopped: (:) [], COMException + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException Yes, the file is there. No it isn't opened by another program. Not trying to save. Again, the commands work over and over when run in a local PS session. I'm leaning toward using the ImportExcel PS module but I'm not sure it will allow me to find formulas in a spreadsheet (I've not seen where that can be done). Regardless, I've repro'd this on three different Hyper-V servers and it seems like there is a problem with ComObject. I'm not versed well enough to know much more than that. I've read about DCOM Config and allowing permissions to Excel but there are no entries for either Excel or a CLSID for Excel. This is simple for anyone to repro. Just have a Win10 VM with Office and create an Excel file. You don't need to do anything with the file. This all falls apart in PS Direct when the workbook is being opened. Thank you in advance for any help. Tom
Solved
Tom Phillips
Nov 23, 2023 Place Windows PowerShell
20KViews
0likes
3Comments
Table will not convert to range
I am trying to convert a Table to a normal range. I right click on the table and choose "Table>Convert to Range" . After selecting "yes" the data remains unchanged in a Table format. Are there any other checks I can take?
Clinton Azotea
May 24, 2021 Place Excel
48KViews
0likes
9Comments
Excel web query DataFormat.Error "not a valid path"
Hi, I have a workbook that grabs data from a few online Excel workbooks. This was working fine from when I set it up about six months ago until recently, when it started producing a DataFormat.Error. One of the data sources that my workbook queries is the Reserve Bank of Australia exchange rates in this file: http://www.rba.gov.au/statistics/tables/xls-hist/2014-current.xls This is the error that I get: DataFormat.Error: 'C:\statistics\tables\xls-hist\2014-current.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. Details: 2014-current.xls This used to work just fine with no errors. I've tried on multiple computers with the same issue, and I've tried three other links to publicly available online excel workbooks with the same issue. I've tried adding a new query (by selecting query from workbook or query from web) in a fresh workbook and pasting that link as the source, but I still get the same error. It's weird that the error is referring to a file at C:\ and not the URL that I entered. I'm using MS Office Professional Plus 2016, Excel version 16.0.4266.1001 64 bit. Does anyone know how to get around this issue? Thanks in advance, Simon
Solved
Simon Pearce
Apr 27, 2021 Place Excel
16KViews
0likes
10Comments
Looking for trends in unrelated related data
I don't know if this is in the exact right spot, but here goes. Let's say that I'm a biologist analyzing litters of puppies: I've got six litters I want to analyze. Each of those litters could have anywhere from five to ten puppies. Each one of those puppies should be identical (for the sake of the thought experiment), but there's variation between each one. Each of those puppies has roughly 30 different areas of the body that I want to analyze. These 30 areas are the exact same spots on all of the puppies across the six litters, and, functionally speaking should be identical to each other of the chosen areas on the same puppy For each one of those areas, I'm investigating aspects of up to each of the following features: skin, fur, and muscle Each one of those features has a given set of characteristics Fur: length, density, color, pattern Skin: color, pattern, elasticity (okay, I know that sounds weird, but I'm grasping at straws for this analogy) Muscle: strength, volume, stamina (lots and lots of straws being grasped) Combining the above results in hundreds of trios of data that I'm trying to analyze: Area - Feature - Characteristic Each trio has its own unique set of allowable values: Area 2 fur length can be between 1 and 2 inches, whereas area 18 fur length can be any length under 4 inches Area 7 skin color can be pink, white, or black, but area 20 skin color can only be brown The Area 12 muscles need to be strong enough to lift 4 lbs, but there is no specification for the strength of the Area 2 muscles Each unique trio of data points has the same allowable range as that same trio of data points across all puppies across all litters Area 6 fur density from puppy 2, litter 1 has the same set of allowable values as Area 6 fur density for puppy 9, litter 10, as does area 12 fur density from puppy 2, litter 1. Despite the fact that the range of allowable values for each trio of data is unique, each area is affected by the same things that affect all other areas; the same is true for Features and Characteristics: Whether or not the puppies are indoor or outdoor dogs affects all fur length The puppy's pedigree affects all puppies' skin color and elasticity How much exercise any one puppy gets affects the strength of all of its muscles Additionally, one trio of data may lend clues to another trio of data Area 10 skin pattern often has an affect on area 10 fur pattern Given all of that: I have thousands of data points that I'm trying to analyze and draw conclusions from, and I'm looking for the best way(s) to do so. Pivot Table seems like it'd be helpful, but, as I move the data around to better understand it, I can't make conditional formatting follow individual cells around that would highlight cells showing bad values based on that data point's area, feature, and characteristic. I'm also poking around with Power Query, or whatever it's called now, but I haven't been able to make anything useful. Suggestions would be incredibly helpful; otherwise I just have to look at all of this data manually.
Solved
jamesson_kaupanger
Sep 20, 2019 Place Excel
5.5KViews
0likes
19Comments
Excel overlapping day of week and times
Hi there, Where I am currently working we have spreadsheets listing course lectures and seminars. As some courses/seminars cannot conflict with others, I need to find a way to identify which courses/seminars conflict. A sample spreadsheet is below. The day and time cannot conflict with another day/time that is the same or has overlapping times. I need a column at the end to identify all the exact and overlapping courses and seminars, not just by the times but by the day as well. I have been able to come up with a calculation to find overlapping times and another to find the same days/times however I am lost at how to create one calculation to do both. I am using Office 16. Subj Num DU CType Sec Days Time LING 1F25 1 LEC 1 M 1500-1700 LING 1F25 1 SEM 1 1 T 1600-1700 LING 1F25 1 SEM 2 1 W 1500-1600 LING 1F25 1 SEM 3 1 F 1600-1700 LING 1F25 1 SEM 4 1 F 1000-1100 LING 1F25 1 SEM 5 1 F 900 -1000 LING 1F25 1 SEM 6 1 W 1600-1700 LING 1F25 1 SEM 7 1 R 1400-1500 LING 5P01 3 LEC 1 R 1100-1400 LING 5P02 3 LEC 1 T 1100-1400 LING 5P03 2 LEC 1 T 1200-1500 LING 5P04 3 LEC 1 W 1100-1400 LING 5P05 2 LEC 1 M 1400-1700 LING 5P07 2 LEC 1 R 1400-1500 LING 5P08 3 LEC 1 M 1400-1700 LING 5P85 2 LEC 1 W 1100-1400 Any help would be greatly appreciated. Arlene 1070
Arlene1070
Aug 19, 2019 Place Excel
2.2KViews
0likes
5Comments
Conditional Formatting - how to minimize formatting rule duplication with editing
Hi all, I built a spreadsheet for management to track project deliverables, and am running into two issues. I attached a slimmed down version of the spreadsheet for review. 1. The file contains several conditional formatting rules. When I turn it over for use, the end user has to insert rows in the table at times as new projects come in. I think this is causing the conditional formatting rules to explode. As a result, I have lots of rules that pertain to only one or a few cells, instead of the entire range as initially created. Is there a way to prevent this from happening? 2. The Projected columns contain rather complicated nested formulas for projecting due dates. I've asked the end user to NOT manually enter values into these columns, as it erases my formula, but with little luck. Is there a way to protect the integrity of these cells while allowing the end user to still add/remove rows from the table? Any guidance on these issues is greatly appreciated!! Thanks, Casey
Casey Mims
Jul 12, 2019 Place Excel
4.2KViews
0likes
8Comments
Date Formatting
I wonder if anyone could help me please? Its probably really simple but I can't for the life of me get Excel to do as I would like. On all our company graphs/pivots etc, date is displayed as 18/06 (yy/mm) I have tried using a custom format but it always defaults to a full date 01/06/2018 in the display bar even though displaying correctly in the data It causes havoc for my pivots. How can I have the column format my dates as YY/MM to display in the data and in the display bar? Please? Thank you Clair
Solved
Clair Huntley
Apr 30, 2019 Place Excel
3.6KViews
0likes
10Comments
Import Hyperlinked Text into a Data Validation Drop Down Menu
I am interested in creating a Data Validation drop down menu that contains report names (text) with an embedded hyperlink. The way I'm doing this now is creating a table on another sheet, listing all the reports (Report 1, Report 2, etc) within that table, and then embedding hyperlinks on all those report names. Then, on another sheet, I'm creating the drop down menu through Data Validation that sources those report names. Right now, the drop down menu appears, and I can choose the reports, but the embedded hyperlinks don't carry over. Is there a way to do this? I've found some answers online that suggest workarounds using HYPERLINK pointers, but this isn't what I want to do. Any help appreciated, thanks!
Adam Cronce
Mar 02, 2019 Place Excel
1.5KViews
0likes
1Comment
Power pivot Excel 2016 oracle
Tengo instalado un Excel 2016 profesional y observo que desde Power Pivot, no se despliega en el menú "De otros Origenes" nada, por lo que no puedo seleccionar Bases de datos de Oracle. Necesito ayuda, muchas gracias
JavierVE
Mar 01, 2019 Place Excel
857Views
0likes
0Comments
Unable to open .xls that have protected sheets or protect workbook structure
Hi, Since last week, I am no longer able to open .xls files that have protected sheets or workbook structure. Before that everything was working well. I can open those excel files on other computers with the same version of excel. Excel 2016 Version 1901 (build 11231.20130) I have try the following things: - quick Repair - Online Repair - Ignore other application that use Dynamic Data Exchange (DDE) - removed all add-ins and Com - enable Macro - remove the 3 check box of the Protected View screen - Remove the file block settings - Uninstall office completely from the computer with the Microsoft fix-it program and did a fresh re-install - Deleted the user profile from the computer and even try with a different user. - can open other .xls file that does not contain protect functionality in it. - I tried to open the file from the network or locally on my computer. When I open the problematic workbook, it freeze completely with CPU at 30% and memory at 3.2 gig and go up to 3.6 gig before closing. I have 16 gig of ram on my computer. If I open Excel then I open the file, I get a grey background with the menu and formula bar. the I click in the grey area and I get a message saying "Not enough system resources to display completely" Anybody as seen that and know the fix? Thanks
Ghis71
Feb 08, 2019 Place Excel
1.1KViews
0likes
0Comments