Questions tagged [excel]

Only for questions on programming against Excel objects or files, or complex formula development. You may combine the Excel tag with VBA, VSTO, C#, VB.NET, PowerShell, OLE automation, and other programming related tags and questions if applicable. General help regarding MS Excel for single worksheet functions is available at Super User.

Filter by
Sorted by
Tagged with
0
votes
0answers
10 views

Reading number format in pandas and openpyxl

How can i read the number format for a column value in excel using pandas.For eg i have columns stored as currency like $. import pandas as pd data = pd.read_excel('temp.xlsx', engine="openpyxl") ...
-2
votes
1answer
15 views

Show text if multiple cell values are True

I want a formula for column E3 depend on column A3,B3,C3 and D3. If multiple columns show yes or single column show yes I want show as below in pink. Need to combine shctin names which show "yes"....
0
votes
0answers
5 views

Convert CSV or TAB File Having 1.44 Million Lines to EXCEL Format Given EXCEL Limit

This CSV or TAB file (generated by Notepad++) has 1.44 million lines but only 5 fields per line. I cannot pull the whole file into Excel due to Excel "line limit". Is there a way to convert a CSV or ...
-1
votes
0answers
12 views

Include columns as placeholders in Excel [closed]

I'm creating a report where I have a group of columns in Excel, two of this need to include as as placeholders if they are followed by a non-blank value in a later column but I don't know how to do it....
-2
votes
0answers
20 views

VBA Runtime Error “select method of object Range” is shown in Error Box then crashes Excel [closed]

I have enabled errors on all breaking code. This is not allowing me to press the debug button or know what line of code is causing the crash, as excel crashes right after the Error Message. I am not ...
0
votes
0answers
6 views

How to do grouping column wise in SAS similar to pivot table

I am trying to group data in the column in SAS like how you can do in pivot table in excel. I'm trying the produce the following desired output. Problem 11/17/2019-11/23/2019 INC 25 ...
1
vote
0answers
16 views

How to extract Excel PivotCache into Pandas Data Frame?

First time posting here, I apologize if this question has been asked before - I can't find anything that applies. Is there a way to read the underlying data from an Excel PivotTable into a Pandas ...
2
votes
3answers
40 views

Transposing dummy variables with a vba script

I would like to transform the structure of an excel file so that I can import it into a system which needs it to be structured in a given way. Here is a small extraction of the excel file. There is a ...
0
votes
1answer
25 views

Looking for how to put user input into my code with a loop

Sub Delete0() Dim myRow As Variant myRow = InputBox("What row does the data start in?") Dim myColumn As Variant myColumn = InputBox("What Column does the data start in? (number format, i.e A=1, B=2)")...
-1
votes
1answer
13 views

EXCEL VBA -Loop of function that runs on a folder

Good Afternoon, i have a issue with a macro that i did that will require Error handlers, however this macro is going to be used inside another macro i found on internet. The second macro, makes a ...
0
votes
0answers
12 views

How do I create a status bar in vba while the user is waiting to update a web query table?

I am an amateur coder and I have the following web query table updates automatically launched in the activate section of the sheet. But I would like to be able to show a status bar of some sort so ...
-2
votes
0answers
11 views

Words in one column A identified in another column B and then copy and paste associated rows from column B into another worksheet [closed]

I am a beginner to VBA. I would like to know VBA code to recognize words (which I paste) into one column (e.g. column A) of Excel Sheet1, then search for the same words in a different column (e.g. ...
-1
votes
0answers
13 views

Extract repeated string in Excel Column [closed]

I have a excel collumn with the following text (example) MI1A10 MI1A20 MI1A21 MI1A30 MI1A31 MI1A40 MI1B10 MI1B11 MI1B20 MI1B30 MI1B40 MI1C10 MI1C20 MI1C30 MI1C31 MI1C40 MI1D10 MI1D20 MI1D21 MI1D30 ...
0
votes
1answer
16 views

VBA Chart Macro Range Using Union - Error [duplicate]

I am trying to create a scatter chart using a macro that uses data in columns that are not adjacent. Once that is working, I would like to add more series to the chart, stepping across columns, but I ...
-4
votes
0answers
30 views

Is there a way to write to specific rows and coloums in an excel file using python [closed]

I am currently trying to make a log in system. I'm using an excel file that is saved as a csv file to save and create the accounts. What I want to do is have each column contain a certain value such ...
-1
votes
0answers
16 views

How to import Excel data to Gridview in VB.Net

I want to Import all Excel into GridView using vb, but only import one This is the Example I use for Import: Imports System.Data.OleDb Private Sub Button5_Click(sender As Object, e As EventArgs) ...
0
votes
0answers
20 views

Understanding an Index/Match/Array Excel Formula

I know this code works, but I'm not exactly sure how. Can someone please break this out for me? I'm assuming the "dragability" of this is due to the last part locking the first reference and not the ...
0
votes
0answers
11 views

Excel ranges typed in with two periods [migrated]

Until recently, I was able to specify summing all the cells in column a by using =sum(a..a) which upon pressing enter would automatically convert to =SUM(A:A) Is there a reason this no longer ...
0
votes
2answers
57 views

Simple VBA macro will work on all computers except one

I wrote a program and it has worked on all computers in my workplace except one. The program errors out on a line like: MsgBox Workbooks(WB).Sheets(IO).Range("A1").Value I'm getting a run-time ...
0
votes
0answers
25 views

Looking for Excel macro to move quantity of a cell into a new cell if a certain value appears in another cell

I need to run a macro that looks for a range of SKUs in column (A). if it finds one of the SKUs to take the value of column (B) and stick it in column (C), and do that for every entry in the ...
0
votes
0answers
27 views

Excel VBA database application [closed]

I am in the process of building an asset tracking application in VBA for Excel. When it comes to searching the data, would it be better to keep a cached copy of the data in my front end workbook or ...
0
votes
0answers
11 views

VSTO C# Excel modify and save xltx file

I am building a VSTO add-on to replace the string "Engineer" to "Designer" in every worksheet of all the excel files in the directory. So far, my code is working except for xltx files. FileInfo[] ...
0
votes
1answer
25 views

Open two existing word files from excel

Hi I'm trying to write code to use excel to work with two existing word documents but I keep getting OLE errors. This is just the start but it keeps crashing. What am I doing wrong? Sub BoQtoWord() ...
0
votes
1answer
33 views

how to pull records from access to excel WEEKLY using ADO

I am trying to create a macro that will export new records from access to an Excel file each week. Currently my code has it adding a new sheet to the Excel file, but how can I get it to just update a ...
0
votes
1answer
20 views

How do I call macros based on cell value?

I'm brand new to vba, and I am struggling with trying to get a macro to start based on the value of a merged cell. Looking around online, I was able to get this far, but I keep getting error 13 type ...
0
votes
1answer
16 views

How to put an input box that whatever the user puts in will put in a formula?

Sub test() 'insert input box to ask user to decrease by how much Dim LR As Long LR = Range("D" & Rows.Count).End(xlUp).Row Range("F1:F" & LR).FormulaR1C1 = "=RC[-2]-0.56"<---...
4
votes
2answers
40 views

How do I add a column to my dataframe that says what sheet name each row is from? Python

I am working with a Dataframe that has five sheets and I want to use four of them. So I can load it in: df = pd.read_excel('***.xls', sheet_name=['a', 'b', 'c', 'd']) But now I would like to add a ...
0
votes
1answer
43 views

How are the double-quotes managed when I put this code into a loop?

On SO, I was just given two answers that both work when called a single time. Now I want to put them in a loop and loop over several rows of data. However, I'm having a heck of time getting the code ...
0
votes
0answers
12 views

How to Export select tags of an HTML table into an excel file

I have a small HTML form with three tables. Each table has select tags. My goal is to export the HTML tables into an excel file and have the selected value ONLY to be exported. Each table should have ...
0
votes
2answers
43 views

.net Excel Range Address limitation

I want to define multiple areas in one single Excel.Range object. The purpose is to colorize multiple different areas by setting one single Range. This should save time using the Excel interop, which ...
0
votes
1answer
18 views

Conditional Formatting on Sum of Two Columns in Excel

I have two conditional formatting rules. One highlights the minimum value of the column and works correctly (Column B). I want to also highlight the adjacent columns within the same row (Columns D ...
0
votes
0answers
10 views

Linebreaks are not show up using PHPExcel_RichText()

I'm using PHPExcel and new lines worked great until I needed to use PHPExcel_RichText() to get different formatting in the same cell. Is there any way around this? (I know that PHPspreadsheet is ...
0
votes
1answer
22 views

Lookup ticket data for various groups located on one sheet and move to another sheet based on group name

I have a sheet with service ticket data on it, things like ticket number, opened time, assignment group, etc. I then have tabs with the various group names that I currently have to manually sort and ...
0
votes
0answers
14 views

Onedrive and MySql [closed]

How can I get data from excel sheet placed on oneDrive in MySQL or any other database?
2
votes
0answers
48 views

How do I “AND” across a range of cells in VBA?

Given that I have a range of cells (say A1:F1), in which all those cells contain either TRUE or FALSE. I know that I can write the formula =AND(A1:F1) in G1 which will "AND" across that range and ...
-3
votes
0answers
24 views

How to Lock Top Row from being Sorted [closed]

I am sorting multiple rows of data. How do I stop the Top row which is my header from being included in the sorting. What can I add to stop the top row from being sorted.
0
votes
1answer
21 views

Writing pandas dataframe into one excel file

I'm trying to write the data from 2 excel files into one file. Of course the code below only writes the data from the first sheet into the consolidated sheet because the loop ends when writing the ...
2
votes
1answer
13 views

How to add a list of data into an excel column using openpyxl?

I'm new to using openpyxl, and I have a list of data I want to add to a specific column/row in excel. I know how to add a value to a specific cell using sheet.append, but this only allows you to ...
0
votes
0answers
19 views

How to copy rows from Pivot Table and paste as values across rows [closed]

I'm new VBA and I'm trying to figure out on how to copy data from pivot table and paste as values across rows in a different sheet, let's says C2:G2 on sheet2..any help be appreciated.
0
votes
1answer
34 views

VBA code for timestamp- cell's containing formulas

I have a workbook with 3 worksheets. Worksheet1 has rolls ups of worksheet2 and worksheet3. So worksheet1 is never manually updated because the data/information is updated in worksheet2/3 and it ...
0
votes
2answers
26 views

VSTO C# Excel workbook all worksheets

I am using C# VSTO to make a text replacement in all Excel files in a directory. Currently, my code works, but only to the active worksheet (first worksheet) of each Excel file. How do I perform this ...
2
votes
0answers
40 views

Strange behavior using custom shortcut keys in excel

When executing a subroutine via a custom shortcut key set in either Alt+F8 or using the VBA code: Application.OnKey "+^{o}", "open_all_DI_logs" I get different behavior when assigning it with Shift ...
0
votes
1answer
34 views

Sending Data from Word Into Excel

I am trying to send some data to Excel from Word after an email is sent. I have the email and the rest of it working fine. Now, I am just trying to get the part with Excel working right. Private ...
0
votes
1answer
27 views

Remove the element throughout the workbook

Continuing the previous query: Copy the element throughout a whole workbook I would like to have my shape removed. In this purpose I used the following code: Sub asbuilremove() Dim ...
0
votes
0answers
8 views

copying a sheet from a workbook to another using openpyxl

I am trying to combine multible excel files with same sheet name in each one into one file using openpyxl. I am getting this error IndexError: list index out of range Please check the my code from ...
-1
votes
0answers
49 views

When my if Condition is met vba doesnt exit the loop

Hello i have some code that takes a value from a worksheet based on two criteria (Date and Vessel Size)and then fills in that value in the corresponding field in another sheet. It seems as all is in ...
0
votes
0answers
28 views

Sorting 2 different sheets with code in same macro

Goal: Sort Sheet1 Alphabetically, then sort Sheet2 Alphabetically. Code should be in the same macro. Both sorting should be done in column A on respective sheets. Headers are in rows 1 through 6. Data ...
0
votes
1answer
17 views

Copy the element throughout a whole workbook

Continuing the question: TextBox object customisation - Compile error: Invalid or unqualified reference I am going to copy this element - textbox into all worksheets throughout my document. I would ...
0
votes
2answers
25 views

Excel report with SQL source connection

My Excel report retrieves data from a SQL Server table using a stored procedure with parameters. I have two connections in work book: 'A', 'B' bullet. A. Connections in workbook Query from SQL ...
-3
votes
0answers
33 views

Converting “Date Numbers” to “Dates” [closed]

I have a question regarding python and Matlab. I got a matlab file (.mat) or table from client which contains values for variables at increasing "Time". Now its difficult for me to understand the time ...