124 questions
0
votes
1
answer
83
views
How to apply conditional formatting only on the top level rows in the pivot table hierarchies
I’m trying to find the addresses of all the top-level rows in hierarchies to apply conditional formatting to the pivot table. It seems that using the method below, I can only extract the definitions ...
- 636
0
votes
1
answer
219
views
How do I copy from a filtered sheet and paste in a different sheet using Excel Automate Code Editor?
My issue is based on Excel's new Automate/Code Editor feature.
I am trying to copy a filtered range from one sheet to another by using the getLastRow() function to determine the range of the filtered ...
0
votes
0
answers
86
views
Internet Explorer to Edge Automation: Getting a run time error'-214767259(80004005)': Method ' Document' of object 'IWebBrowser2' failed
I am trying to open the Microsoft edge using internet explorer automations controller using the below code.
Here is the sub routine:
Sub OpenURL(ByVal SURL As String)
Dim OIE As Object
Dim ...
0
votes
1
answer
67
views
How do I modify the formatting of an excel worksheet without overwriting any existing formatting styles using openpyxl
I'm trying to build easy to use functions that can be applied to excel sheets on top of openpyxl. But what I noticed is that anytime I apply formatting to a cell, any previous formatting is reverted.
...
0
votes
1
answer
128
views
How to fix this Exception in Excel automation?
As I mentioned elsewhere, I'm writing a program to extract and reconstitute Excel files.
Until a few days ago, everything was fine, but suddenly Excel started throwing exceptions when it never did.
...
- 11.7k
0
votes
1
answer
104
views
How to intercept VBE errors during automation?
I created a simple tool that extracts an Excel file into a folder and, conversely, gets this folder and regenerates the Excel file.
This tool also extracts the VBA code to text files in order to make ...
- 11.7k
0
votes
2
answers
899
views
How do I write a VBA function in Excel that allow me to lookup values (with XLOOKUP) on 60+ Worksheets
I have an Excel workbook consisting of 60+ Worksheets. In the main worksheet, I want to use the XLOOKUP to find and return the value of what I'm looking up. The VLOOKUP function will not work in this ...
- 1
0
votes
1
answer
65
views
VBA Application-defined error Querying Data from a website
I am trying to query data from the website listed below in VBA. The query is resulting in a 1004 error--Application-defined or object-defined error.
This is the website: https://www.troa.net/tis/?...
- 177
0
votes
2
answers
180
views
Excel not closing using the COM in Python
My Excel workbook not closed by executing the highlighted last statement.But throws the error as below..
My code....
import win32com.client as win32
excel = win32.gencache.EnsureDispatch('Excel....
1
vote
0
answers
122
views
How can I apply conditional formatting in C++ MFC?
https://learn.microsoft.com/en-us/previous-versions/office/developer/office-2007/bb404903(v=office.12)?redirectedfrom=MSDN
Microsoft provides example codes in C# and Visual Basic.
For example C# code ...
0
votes
1
answer
162
views
Cannot rename excel file
Imports System
Imports System.IO
Imports Microsoft.VisualBasic.FileIO
Imports Microsoft.Office.Interop
Module Program
Dim oxl As Excel.Application
Dim owbs As Excel.Workbooks
Dim owb As ...
- 49
0
votes
0
answers
384
views
Selecting filtered rows in excel using VB.net
Imports System
Imports System.IO
Imports Microsoft.VisualBasic.FileIO
Imports Microsoft.Office.Interop
Module Program
Dim oxl As Excel.Application
Dim owbs As Excel.Workbooks
Dim owb As ...
- 49
1
vote
1
answer
434
views
Not able to extract value from a particular cell in excel sheet
I am trying to access the cell A7 of an excel file which has only one excel sheet. However i am getting the following error Public member 'Range' on type '__ComObject' not found. in the statement Dim ...
- 49
0
votes
2
answers
1k
views
Confusion in creating excel application object using VB.net: CreateObject vs New
I am facing difficulty in understanding the following concepts. I had posted a question some time back - read through the answers but some things are still not clear. I state my confusion below:
My ...
- 337
1
vote
1
answer
713
views
Difference between CreateObject and New when opening a new excel application
What is the difference between the following two statements? I was writing a basic VB.net code which creates a new excel workbook and adds a new sheet. Both seem to be doing the same thing:
Dim oxl As ...
- 337
0
votes
1
answer
413
views
Updating a table "Y" in file "B" with new added rows from table "X" in file "A"
I am trying to create an "instant cloud flow" on Power Automate.
Context:
Excel file "A" contains a table "X" which gets updated regularly with new rows at the bottom ...
- 555
2
votes
0
answers
2k
views
Excel SAP GUI - Automation Error Invalid Syntax
I logged into my SAP GUI and recorded a basic process to test using SAP GUI script recording option. After that I went to excel and wrote the following code. When I run it, I get the error :
Run-time ...
- 323
1
vote
1
answer
44
views
Moving cells between cells in excel using python
I need to move cell D3 to D4 in excel if cell A3 is not equal to C3 can anyone tell how to do this using python?
0
votes
0
answers
523
views
How to run advance Macro in excel when cell value changes by formula
Why does code1 work, and code 2 doesn't?
The only difference I see is Macro in code 2 is more complex.
What I wanted to do
Run advance macro automatically when cell value changes due to formula. The ...
- 30
0
votes
0
answers
473
views
using System.Drawing not working in C# when executed in Linux Environment [duplicate]
I am generating an excel programmatically, but it works fine in VS, when I tried automating it and compiling via Linux environment, it shows error.
`
using System.Drawing;
ws1.View.FreezePanes(2, 1);
...
0
votes
0
answers
35
views
Syntax is correct but showing Subscript out of range (Runtime error 9)
Not sure why I'm getting subscript out of range error on this code but as per my understandings the code is correct. This code was working fine few hours back but now getting this error. Can anyone ...
- 17
0
votes
1
answer
403
views
Getting a COM class factory Path not found exception when the path clearly exists
I'm trying to use Microsoft.Office.Interop.Excel library to convert a csv to an xlsx.
This is the function I am using
public static string ConvertToXlsx(string _sFilePath)
{
string _newFilePath = ...
- 31
3
votes
1
answer
886
views
ExportAsFixedFormat's IgnorePrintAreas parameter seems not to have effect
In a Delphi application I am using since years the following code to export xlxs to pdf:
function TExportTool.ExportExcelToPDF(aFileName, aNewFileName: String): Boolean;
// reference : http://...
- 15.5k
0
votes
2
answers
2k
views
Office Script with Power Automate to Dynamically Change Excel Worksheet Date
I am using the following office script to hide a daily excel worksheet at the end of the work day. It runs fine when run from the script in excel, however, it fails when triggered by Power Automate ...
0
votes
1
answer
174
views
How do I use an Outlook macro to connect to an excel file that's already open?
I'm trying to write a Macro that will refer out to a file I use to track credits(I call it Master6 below). In the code excerpt below I try to do some initial testing like, see if excel is even running,...
0
votes
2
answers
444
views
How can I turn on Data/Filter mode on Excel sheets from MATLABvia ActiveX?
My MATLAB code produces data that I want to send to Excel. I use ActiveX Server in MATLAB to both produce the Excel file and to do the export (I can control more properties of the resulting Excel file ...
- 423
2
votes
1
answer
451
views
Using pattern matching to match an array with a non-zero lower bound
I'm writing code that calls Excel macros via COM Automation. There is a need to handle different return types and pattern matching is perfect for that:
var result = ExcelApp.Run("MyMacro", .....
- 107k
0
votes
1
answer
740
views
Generating random float number in Python
I just want to generate random float numbers between the range.But i am facing some issue
This is my code
import xlsxwriter
import random
wb = xlsxwriter.Workbook('abc.xlsx')
ws = wb.add_worksheet()
...
- 21
0
votes
0
answers
257
views
PowerShell - Join excel files using ImportExcel
I am new to the PowerShell world, and my need is very simple. I found this utility https://github.com/dfinke/ImportExcel that meets my need but I could not find a suitable exampe But I am struggling ...
1
vote
2
answers
210
views
How to create an array like this in powershell?
I need to store data like below for TextToColumns Excel automation.
I need to implement Code-2 or Code-3 or Code-4 is that any way to achieve?
I have more than 350+ data so I cant use Code-1, that's ...
- 72
0
votes
0
answers
847
views
Find Next visible available Row in filtered data VBA
I am trying to write a program, where I must first apply the filter to Column S with 6 possible filter values (1,2,3,4,5,6). Once the filter is applied, I must copy the last used cell in column T and ...
0
votes
0
answers
241
views
Microsoft Graph workbook API - any standard way to prevent data races between multiple clients?
Scenario:
Two clients on different machines read a range of cells from a shared workbook (.../drive/{drive-id}/items/{id}/workbook/worksheets/{id|name}/range(address='...').
If they both don't find ...
- 431
1
vote
0
answers
34
views
I am trying to read excel file and its formulas and write them in new file by updating the formulas accordingly but got stuck to update them
I am trying to split excel spreadsheet based on columns and retain the formulas used in it. I was using openpyxl to read the formulas but got stuck as it reads formulas and write as it is but i need ...
- 11
3
votes
2
answers
7k
views
VB.Net Excel Automation how to handle Sensitivity Labels
When I'm creating an Excel file for a user in VB.Net, I am getting an error ("You can't perform this action when the file is read-only") when I try to save the file and it seems to be ...
- 143
0
votes
0
answers
315
views
Unprotect sheet in foxpro
I have an xlsm file that I need to insert data but my problem is that the xlsm in protected. I use the oWorkbook.Protect(,,.F.) to unprotect but the function is not working. I s there any way to ...
- 537
2
votes
1
answer
380
views
How to convert all columns to Text, while using TextToColumns, by default it was in General format - Using Powershell
I need to split texts using delimiter |.But after split its truncating front zeros:
Input
0001|00030455|testing
0003|00004532|testing
Expected Output
0001 00030455 testing
0003 00004532 testing
...
- 72
-2
votes
1
answer
349
views
Getting IE Automation Error "Object variable or with block variable not set"
I am Getting error while automating an existing IE window.
error is
"Object Variable or with block variable not set"
Code is
Sub accessExistingIEBrowser()
boolWindowFound = False
...
1
vote
0
answers
62
views
Excel Automation without Interop
We have excel reports which are having connections from external sources. We need to refresh these connections in a day twice. We are already having an application for this which uses Interop. Now we ...
- 21
-1
votes
2
answers
288
views
Multiple lookup in a closed workbook using ADO Connection
From the below Image I want to compare Second Workbook(Records.xlsm) with First Workbook(HandBook.xlsm)
I want to check if Department ID and Course ID Combination is valid by comparing it with the ...
- 21
0
votes
0
answers
94
views
MS Office last updates causing some strange behavior in handling VBA code
Our admins ran some MS Office 2016 updates (Build 11929.20838) and all of a sudden I noticed a few unexpected errors in one of my previously written Excel VBA macros. For example below error is ...
- 99
2
votes
0
answers
524
views
openpyxl: left right border for merged cell
The documentation says need to change only the top-left cell of merge cells.
ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=3)
ws.cell(1,1).border=Border(right=Side(border_style='...
- 1,077
0
votes
0
answers
128
views
How to copy data from One Excel sheet tab to another particular Workbook Tab Using Python
Can you please help me in copying data from a particular tab of excel sheet to another tab of excel sheet bearing the same name and data but in different workbook using Python.The workbook in which ...
0
votes
0
answers
463
views
Doc variables in Word template are not being updated from Excel VBA
I have certain ranges within an Excel file and I create an instance of Word template from within Excel VBA. This Word template has a bunch of Doc variables so that I can replace the values of those ...
- 99
0
votes
0
answers
166
views
Automation of Twinfield add in execution in Excel preferably using Python libraries
I have been trying to automate the process of executing the "Twinfield Analysis" add-in in Excel for quite some time. The challenge I face is that this add-in is needed to be clicked and ...
2
votes
1
answer
128
views
Print Timesheet PDFs
So my wife manages the timesheets for her employers company and has to print a pdf timesheet for each employee that worked on client A's project 1. For instance:
Client A - Project 1 - Employee 1 - ...
- 33
1
vote
1
answer
513
views
Excel: search if a specific keyword exists in a column
I was wondering if anyone out there can help to resolve the issue I am facing right now, actually I wanted to ask that I have 1 column with 5k + rows, and each row has a statement in it and if I ...
0
votes
0
answers
78
views
Query is not redirecting to original URL after submit Data on Webpage
I am trying to create multiple users on a web application using Excel VBA script. Below code works fine for the first row user creation but stops at set document for the second row. Actually Web app ...
0
votes
1
answer
51
views
Resize column width of a particular column
I need to resize excel particular column in a particular sheet.But not able to do so
Sub resizeColumn(sourceWb As String, Sheet As String, column As String)
Dim wkbSource As Workbook
Dim sheetName As ...
- 3
-1
votes
1
answer
126
views
I want delete all the rows that matches specific column value in excel using vba but facing error
Sub Delete_Rows()
Dim lo As ListObject
Dim ActiveSheet As String
Dim wkbSource As Workbook
Application.ScreenUpdating = False
Set wkbSource = Workbooks.Open("C:\Users\nani\Desktop\11.0\deleteRows\...
- 3
0
votes
1
answer
45
views
Using excel formula that does not overkill the file to automate process
I have an Excel dataset and I can't use any plug-ins - only Excel formulas, for the following task:
There is a table: Column A, containing text labels, and Column B, containing numbers corresponding ...
- 3