An unbalanced basic recursive Binary Search Tree for Excel VBA with functions (insert, search, delete, in order, pre-order, post-order, minimum, and maximum)
An unbalanced basic recursive Binary Search Tree for Excel VBA with functions (insert, search, delete, in order, pre-order, post-order, minimum, and maximum)
I'd suggest to use a dictionary object. See: Option Explicit 'needs reference to Mictosoft Scripting Runtime library Sub RevertData() Dim srcWsh, dstWsh As Worksheet Dim i, k As Long Dim sTmp As String Dim sValues As Variant ...
Try to use Range.ClearFormats method (Excel) | Microsoft Docs[^] With Worksheets("WB").Range("a1:z500") .ClearContents .ClearFormats .CopyFromRecordset rs End With
Quote: If System.IO.File.Exists(FilePath) Then Else System.IO.File.Create(FilePath).Close() End If You have created an empty file, which is not a valid Excel .xls file. When you try to open that file, you will get a warning from Excel. ...
Of the things you have allegedly tried...Range(RValue, Cells(RValue.Row, LastColumn).Column) gives a Type Mismatch error, assuming that LastColumn is a rangeRange(RValue:cells(RValue,LastCol)) just gives a compile error because of the colon : If...
Access it by the RGB property of Backcolor. chart.ChartArea.Fill.BackColor.RGB = RGB(221, 221, 221) Also if you want hints at how to do something using C# programatically in Excel that you know how to do manually, record a macro. Do it manuually with the macro recording. review the macro code...
You could use the Range.Offset property[^] Keep track of which column you are up to and just increase the column offset by that number. It's the address you want to increase, not the Value E.g. If Me.TextBox1.Text = "" Then MsgBox ("Cannot Be Empty!") Else For lRow = 3 To 100 ...
This is simply achieved using VLOOKUP. For example in cell B2 of your empty sheet put the formula =VLOOKUP($A2,Workers!$A:$C, 2, 0) and in cell C2 of your empty sheet put the formula =VLOOKUP($A2,Workers!$A:$C, 3, 0) Note that it is better (quicker) to use INDEX and MATCH[^] if you have a lot...
It turns out that you CAN get some of the data you need, like the nonviewport height of the browser window and it's position on screen. It helps to go back and re-read the documenation I haven't read in the last decade. ...
You need to format the date into a string - as this is a formula then the Excel function you need is TEXT - TEXT function - Microsoft Support[^] e.g. ="INSERT INTO #TEMP VALUES("&A2 &","&B2 &",'"&TEXT(C2,"YYYY-MM-DD")&"','"&TEXT(D2,...
Any code which includes the line "On Error Resume Next" should be taken out and ritually disassembled. It doesn't "get rid of errors" it hides them until they are too big to ignore - by which point you have probably done significant damage to...
Quote: first i triend a for next loop to return single cell value inside the resulting horizontal array , but i failed The reason you failed is in this line: ActiveCell.Insert shift:=xlRight As Microsoft documentation[^] says: ActiveCell...
Quote: I suspect that it may be for an earlier version of Excel. It's not true. To resolve your issue, please read this: Object required (Error 424) | Microsoft Docs[^] On the first look, it seems lrow variable is not initialized (or it's less than 1). You can easy detect it by using...
You need to place the file in the same directory as the program. However, that is only a temporary (quick and dirty) fix. What you should really do to make it work properly is to stop using harcoded names in your source code. Use an...
Take a look here: Perform Serial Port Communication from VB[^] The usage of code is the same as in VBA. See similar question on SO: What is the best way to access a serial port from VBA? - Stack Overflow[^]
If i understand you well... Try this: Option Explicit Sub Test() 'call AddHyperlink procedure AddHyperlink ThisWorkbook.Worksheets(1), "A5", "BC2006", "Go!" End Sub Sub AddHyperlink(wsh As Worksheet, insertInto As String, saddress As...
Here is Microsoft's instruction page for creating a drop down list in an Excel spreadsheet - Create a drop-down list - Office Support[^] As for getting the data onto the other pages you have several options Most up to date and advocated by...
I'm pretty sure that Filters.Count refers to the number of filters on a specific column not the number of columns included in a filter. This code will list the columns that do have a filter applied, and what those criteria are With...
That application you are launching may be making assumptions about what the "current directory" is when working with files. The "current directory" is never what you assume it is. Your code should NEVER assume the "current directory" is the...
The Worksheet.Cells indexer returns a Cell object not the cell content: try adding ".Value" to the indexer: xlWorksheet.Cells[i, 8] = GenerateRandomDates()+ xlWorksheet.Cells[i, 8].Value;
You have not changed the NumberFormat to text, you have changed it to the custom format "MM/DD/YYYY". This will change the format to textxlWorksheet.Cells[i, datecolm] = dt.ToString("MM/dd/yyyy"); xlWorksheet.Cells[i, datecolm].NumberFormat =...
One of the variables in your code is null, and you are trying to access one of its properties or call one of its methods. We can't tell you which, because we can't run your code, and we don't have access to your document. Debug your code to...
If I was trying to find the minimum and maximum values of data in Excel I would just use a pivot table - either a straight forward pivot or a pivot query using Power Query. In Excel it's probably best to combine the date and time into a single...
Without your sample data it's impossible to determine if this is the only error, however this line is incorrect Row_count=ws.UsedRange.Columns.count 'Given the used rows count of sheet Based on the name of the variable and the comment on that...
Take out the comparison: =IF(A1="",0,$B$1) With your version: =IF(A1="",0,B2=$B$1) It's trying to fetch the current value of cell B2 and compare it with $B$1 in order to modify cell B2 - with is a circular reference because the assignment to B2...
Quote: Please someone help to solve it. If there is any wrong in the formula so please guide me proper function to solve. Learn to use Excel integrated helping tools. Ribbon 'Formula' > 'Formula Audit' Block > 'Formula Evaluate' : this tool will...
In a short, this part of your formula: B2=$B$1 evaluates into boolean value and returns: - true - if B2=$B$1 - false - if B2$B$1 So, the result of formula: =If(A1="",0,B2=$B$1) should be true/false if A1 cell contains data... :) Got it?
Assuming that RValue and LastColumn is the range object (because you are using .Row and .Column property of these variables), then the simplest way to define another range is to use .Range(RValue, LastColumn). See: Dim RValue As Range ...
Can I suggest that you break the problem down. Here is some sample data that I set up Row SLA Name Month Met/Not Met 1 Item 1 2022-01 Not Met 2 Item 1 2022-02 Not Met 3 Item 1 2022-03 Not Met 4 Item 2 2022-01 Not Met 5 Item 2 ...
See here: Understanding Floating Point Precision, aka “Why does Excel Give Me Seemingly Wrong Answers?” - Microsoft 365 Blog[^] But this is not an Excel support site: we are a software development site and we did not produce Excel! There are...
Quote: but all the results seem to be focussed on connections through code (connection strings) rather than through SQL. Can I assume that CDatabase::ExecuteSQL is the MFC class? If so then before attempting to execute the SQL statement through...
I have no idea what you are trying to do (hint: you should improve your question to make it clearer) but when I am trying to compare worksheets I do the following - Add a new column in each sheet - Formula in column is = CONCAT(A1:E1) (adjust...
You need to specify the file format explicitely. This works for me: private void buttonExcel97_Click(object sender, EventArgs e) { string safeExcelFilePath = @"c:\temp\cp.excel"; string excel97File =...
Well... i'd simplify that... The idea is as follow: rsReport.Open sqlreportForNameLucy, wsDB 'then... wbReportForNameLucy.Range("A2").CopyFromRecordset rsReport rsReport.Open sqlreportForNameAmir, wsDB 'then......
If you are trying to use the Excel CONCATENATE function[^] here, then you can't - that is an "in cell" function, rather than a VB function. So join strings, use the appropriate VB code directly - that will depend on the version of VB you are...
Another alternative is to create a pivot table with "Step" in the Columns selection, "Order" in the Rows selection and "Count of Step" in the Values selection. On my sample the pivot table is in columns F to K, rows 1 to 5 and looks like this ...
From the examples they have given in that project, Read example 2 has this snippet var rows = worksheet.Rows.ToArray(); //Do something with rows Console.WriteLine(string.Format("Worksheet Rows:{0}", rows.Count())); It...
You would have to rewrite the code from scratch. There is no conversion tool that will do it for you. Copying and pasting the VB.NET code into a VBA module will do you no good. The two languages are vastly different from each other making it...
Your first mistake is concatenating strings to create your sql statement - see the comment above from @Richard-Deeming. Use Parameterised queries instead. An example could look like this (NB this has not been tested because you haven't shared...
Without access to your system, we probably can't help that much, but at a guess it's the location of the macro that is causing the problem. If the macro is located in (say) personal.xls rather than in the workbook itself, ThisWorkbook.Path will...
You can't use the Bitmap from System.Drawing in VBA. If you right-click on the work "Bitmap" in sampleImage = New Bitmap(Image.FromFile(sampleFileName)) and choose "Definition" you'll get "Indentifier under cursor is not recognized"....
To follow the hyperlink just use ThisWorkbook.Sheets(1).Range("A1").Hyperlinks(1).Follow Edit: I've just re-read your question and realised you want to get website data as well. That will rather depend on the site - the technique you need to...
You have not qualified the object names so the system knows which types you are referring to. Try changing the Dim statements to: Dim excel As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application Dim...
You have already posted this question at Merge cells from different columns in a datagridview C#[^], and been given a number of suggestions. If you have additional informationor questions then please use the Improve question link below the...
Recommended method of doing work from C# was given here: Export datagridview to excel while keeping the formatting[^]: 1. Create a Macro recording of the actions that you wish to perform 2. View the VBA code generated for you 3. Use the VBA code...
Recommended method of doing work from C# was given here: Export datagridview to excel while keeping the formatting[^]: 1. Create a Macro recording of the actions that you wish to perform 2. View the VBA code generated for you 3. Use the VBA code...
As allready mentioned the Code "On Error resume Next" says to the System that the actual Error is to ignore and the System goes automaticly to the next Code-Line. What you can do now is to code an Error-Handling after each line where an Error...
Your cell contains 0.06. Your conversion is this: cell.Value = Round(cell.Value / 100, 2) The result is 0.00 because 0.06 divided by 100 is 0.0006, and when you round that to 2 digits you get 0.00 If you want "6" in the cell, then multiply by...
To be honest, we can't help you with that little info, and probably can't give you a "full solution" at all - simply because we have no way to access your spreadsheet on your system while you code is running, and you need that to begin diagnosing...
You are using the wrong technique to solve this. Excel provides a built-in capability that would do this for you in a matter of seconds. What you want to do is create a pivot table. The steps to accomplish this are: Select the entire range you...
I know DDE is old fashion outdated technology. But I am creating a C# Windows Form application to send data to Excel file for research purpose. I use Win32 DDE functions and below is what I have tried so far. What I have tried: [DllImport("user32.dll", EntryPoint = "DdeInitialize")] public...
I have a PowerApp on a Power BI. The PowerApp gets its data from an Excel file. Is there any way to serialize the data that's in the Excel file within either the PowerApp or the Power BI in order to eliminate the need for the Excel file and have "fewer moving parts?" What I have tried: I...
Hello, I am trying to create ad headlines using 4 columns of 6 entries to concatenate all 4 columns into unique combinations. I found a tutorial in order to do this and am getting the runtime 424 error on the line starting "lrow". I am using Excel 2016 32bit version For i = 2 To 7 For j...
I'm working on creating an Excel Chart from my C# application that pulls data out of my SQL Server database, and using that data, creates some Charts in an Excel instance. I have a pie chart that displays with the data, and all is well, accept I want to change the background color of the...
You don't want a 3rd party library, but what about an Open Source project from which you can incorporate the code ? GitHub - ExcelDataReader/ExcelDataReader: Lightweight and fast library written in C# for reading Microsoft Excel files[^] For .NET Core usage read the note at the bottom, this is...
I'm creating an SSIS package to load .XLSX files in a SQL Server staging table. I need to control the columns number in my Excel source file in order to have exactly the same columns in both source and destination. The issue is that even when I add manually more columns in my Excel source files...
You're going to have to write a script task to do that, and handle the import manually. I have an article here that loads excel and csv files into DataTable objects, which can then be used to import into into a database. Here's the original article, which is a tool for adding SQL Agent...
Having created a VBA with 2 TextBox and 3 OptionButton, I would like when I press the "save" button to enter the contents inside the Excel cells. So far everything is okay, the problem arises when I would like to insert more than one item. So it goes to replace what I have already included...
The simplest way to achieve that via VBA code is to: 1. Create ADODB.Connection[^] to be able to connect to your database 2. Create ADODB.Recordset[^] to open it and grab/fetch data into it 3. Use Range.CopyFromRecordset[^] method in Excel to copy data from recordset object In C# or VB.NET -...
Hello, I have a ReportOwnerQuery Sheet with a list of emails in with Column C being seperated by commas like "emailone@email.com, emailtwo@email.com". I need to Vlookup or a similar method to check to see if one of the emails in column C of ReportOwnerQuery is on the OwnerList sheet which...
Hi I have an issue that how to upload excel 2016 xlsx file into mssql 2017 server Previously I use excel 2010 and Mssql 2012. The code is working fine (Given Below) Now our client upgrade Office 2016 and MSSql server 2017 and VS 2017 Pls Advice Me Thank you Maideen What I have tried: ...
TransferSpreadsheet Method [Access 2007 Developer Reference] | Microsoft Docs[^] How To Import An Excel Spreadsheet With VBA In Access 2013 🎓 - YouTube[^]
I'm trying create dynamic drop down list in MS Excel O365. I've made 2 tables in 2 different sheets. In below are table in "Workers" sheet: https://i.stack.imgur.com/khSb0.png[^] And in "Order_status" sheet: https://i.stack.imgur.com/g5H4I.png[^] As for as these tables are concerned i...
I have two excel sheets, a Master and New Event List. I need to search the Last Name column of the "New Event" and search the "Master" workbook to ensure that they are already a member. If Last Name is found in Master workbook, do nothing, if Last Name is not found in Master workbook, write...
In this case i import data from large file (which is about 37 MB) to datagridview. The table from excel file is in below: https://i.stack.imgur.com/B8UMq.png[^] After loading data from excel to datagridview i'm inserting that data to mysql database: foreach (DataGridViewRow row in...
Ok i've done in different way: 1. I created a table with workers and name it "tblWorkers". 2. I selected the correct range to import the drop down list with the ID_WORKERS, the i went to Data, Data Tools tab, Data Validation, Allow:List & Source: =INDIRECT("tblWorkers[ID_WORKERS]") 3....
Hi All, In Excel, first I would like to check if the value inside the cell that comes under the Range (E2:E40) has the value "X" and if that particular cell has that value, then the value inside that cell should be changed to "Y". Is there any way to mention like this using VBA? What I have...
Hi I am attempting to compile a project but its missing the Microsoft.Office.Interop.Excel.Application reference. When going to Add References I cannot seem to find it. If Office is installed should the reference not automatically be in the list? How can I get it into the list? I'm running...
You might try the repair option of Office in Control Panel as mentioned here: Interop Assemblies for Excel 2016 for c# application[^] It could also be that you need to upgrade to VS2017, as mentioned here: Office primary interop assemblies - Visual Studio | Microsoft Docs[^] Quote: Starting...
Hi I have a C++ project in which i am reading the excel sheet as follows: Note that i am using office 2016 version. #import "libid:2DF8D04C-5BFA-101B-BDE5-00AA0044DE52" \ rename("RGB", "ExcelRGB") \ rename("DocumentProperties", "MSODocumentProperties") #import...
When im trying to open an existing excel file i keep getting this message, and it is not just for this file, any file that i create. I placed the values in the same folder with application.exe, as well as tried in different folders. Has anyone...
Quote: System.Runtime.InteropServices.COMException: 'Sorry, we couldn't find Values.xlsx. Is it possible it was moved, renamed or deleted?' Make sure the directory, in which you try to open the file, is the one you expect. or tell the directory...
E.g. I want to send this string of text "23423,10,20,Planning,2,1,Drop Off" from Sheets("Sheet1").Range("A1") through serial port. What I have tried: Currently, I'm using this and it managed to open the port but the result appeared as /xFF...
I have a unique need for a dynamic hyperlink but not related to text that you can match. I want to be able to enter R1C1 Coordinates in a cell and hyperlink to that cell within the same worksheet. I don’t believe it can be done with the standard...
How about asking this in the forums for Photoshop? You're going to find the most experience with the product over there. Google Adobe Photoshop Forums[^]
Hi . I have Excel as follows: I want to get the information as below on the main page, and link it by class on the next pages. I do not know if I should create a form or a button or something. Also, the class column should be a drop-down list....
there is a sample data in excel file e.g. Name Date CITY ABC 01-01-2020 ny def 02-03-2020 sa now i want to import that excel file through c# code and insert that data into table. ... table is like this. these are the columns...
We are more than willing to help those that are stuck: but that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us...
See Working with MS Excel(xls / xlsx) Using MDAC and Oledb[^] for importing from Excel into a C# program. For information on SQLite see SQLite Home Page[^] and System.Data.SQLite: Home[^].
I am very new to VBA(I do have programming knowledge in other language though). I am creating a very detailed report from multiple sources to eventually merge into one report for management. However when ever it selects my values such as dollar...
I am not very computer savvy but wanted to know if there is a way that I could print different worksheets in Excel (each client has their own worksheet) to an specific individual location in One-Note (each client has a receipt section in their...
how to specify 6 columns and name them in datagridview so when i import an excel sheet with missing columns it adds the missing columns, keep the content empty and arrange the columns based on what i specify in datagridview note: column name does...
Hi All, I am using XlSX.Read Javascript API to upload excel file, while trying to upload the excel file i need to un protect or remove the password, or any other way to un protect or input the password in Javascript, i have attached the code...
I am looking for a way to insert data or replace some data into preexisting pdf file. The scenario is that some specific data would be fetched from excel file and data from it would be mapped onto pdf file. What I have tried: I have tried using...
the was update on my article, but in the site is till show the old version. also the article has 2 part and I need help how to link them together. Thanks. What I have tried: get the link but did not work.
All articles and modifications to articles go to moderation, where the community (or a trusted subset ot if at least) read and decide if it is suitable for publication, or if it needs changes first. This doesn't normally take too long, but all...
So assuming your columns list is static (which you imply), you can read the dataTable columns list after reading in the excel sheet, as you have done, and the missing ones can be added to the data table definition. DataTable.Columns.Add (DataColumn).
I have an excel file with data of about 1000 records. I want to read each employees temperature of 5 consecutive days. If it is greater than 100 for 5 continuous days, I have to return this employee's data. Can anyone help me to query this is...
To be honest, I wouldn't do that in SQL: I'd either create a secondary table which monitored the last reading per employee and the count of instances - then update it every time I insert a new temperature value, or I'd do it in a presentation...
The image shows the rows and columns I have code to loop the value like in first take value from column no 1,1,1,1,1,1,1,1,1,1,1,1 then take value from 1,1,1,1,1,1,1,1,1,1,1,2 then 1,1,1,1,1,1,1,1,1,1,1,3 then 1,1,1,1,1,1,1,1,1,1,2,1 then...
Submit an article or tip