Microsoft Excel – combine data with a different header

I have a majority of several worksheets even having header
but one (1) spreadsheet has a different header

I have this code that will combine them all

Under handset ()

Dim xWs As Worksheet
In case of error, next recovery

Set xWs = ActiveWorkbook.Worksheets.Add (Sheets (1)) = "Combined"
Sheets (2) .Range ("A1"). Destination EntireRow.Copy: = xWs.Range ("A1")
For i = 2 TB Worksheets.Count
Worksheets (i). Range ("A1"). CurrentRegion.Offset (1, 0) .Copy _
Destination: = xWs.Cells (xWs.UsedRange.Cells (xWs.UsedRange.Count) .Row + 1, 1)


Dim s As Worksheet, t As String
Dim j as long, k as long
K = Sheets.Account

For j = K To 1 Step -1
t = Leaves (j) .name
If t <> "Combined" then
Application.DisplayAlerts = False
Leaves (j) .Delete
Application.DisplayAlerts = True
End if
Next j

End Sub

it works well but I want my 1 sheet to be inserted in column D until its last column

sheet3 does not contain the first 3 columns of the other sheet

sheet1, sheet2 and sheet 4 contain these columns

Branch | Population | Store | name | age | ...

while leaf3 contains

name | age | ...

the others are the same as the first 3 columns are not.
I do not do what I am going to add to the code to insert in the designated column.

oh they contain different data values

Thank you!

microsoft excel 2016 – Show unique data against 3 beaches

I have 40 employees and their annual gross income. I need a single bar with 3 ranges / colors. The top of the bar should be green and the range is> $ 20000. The middle of the bar should be yellow and the range is $ 15,000 to $ 19999. The bottom of the bar should be red and the range is <$ 14999. Then I need a horizontal black line, a graduation mark or something wrong with the driver data. The goal is to show where they stand relative to the norms of the business, namely good, good and bad. Please help!

Excel 2016 if cell E2 = "QTR1" or "QTR2", etc. … (drop-down list) retrieves the data from cell F9 from the sheet 'name'.

I have workbook with a tab used to get data from 14 different tabs, each tab has a column QTR1, QTR2, QTR3. QTR4. These columns have questions from A1 to A 29.

I have 14 zones add data in their tab each quarter, on the main tab I would like to have a drop-down list CELL E2, when that changes, it captures the WTR1,2,3 or 4 data from each of the 14 other tabs and then place the right columns on the first tab.

I thought if I make a simple if statement in each cell = IF (E2 = "QTR1" THEN F4 OF THE TAB TAB = "Albatross F4I would then simply change the range of cells or tabs based on = long and not the best, but this seems to be the simplest solution without using too many formulas that will now be understood.

I do not know if v or h look up would work, because I'm trying to copy a cell from a tab to the main element dependent on cell E2.

How can I add my workbook so you can see it?

worksheet function – Using XIRR with calculated dates in Excel

I want to find the internal rate of return of the following data:

data table

I've tried a whole bunch of formulas in R4C2 entered both as standard formulas or as matrix formulas, and nothing seems to work. Should I put the dates in the cells of the spreadsheet or is it possible to put the dates in the formula as a calculation? Here is an example of some of the things I've tried:

= XIRR (R[-3]C: R[-2]C, {today (); today () + 30 * R[-1]C})

= XIRR ({R[-3]C; R[-2]C}, {Today (); Today () + 30 * R[-1]C})

Again, I tried to enter them with the Enter key and Ctrl + Shift + Enter.
It always tells me that there is a problem with the formula. Can we do this with a formula or do I have to put the calculated dates in an adjacent cell range?

Microsoft Excel – Comparison of timestamps

We have a system that we use to generate hours of connection and disconnection throughout the day (which you can export to excel), and we also have an Excel manual tracking that we use to capture the time at which we we are disconnected and reconnected, then we disconnected at that time. Now, the official report is the one generated by the system and I must include in this report the reasons why we logged out during the hours indicated in the system report that will be based on Excel manual tracking. The problem is most often, Excel manual tracking has different timestamps compared to the timestamps recorded and generated by the system. How to compare the two reports to get the reason for the disconnection from Excel manual tracking?

excel – The ThisWorkbook.Activate statement does not work

I have two exercise books. In the first, I call a macro that activates the other and extracts several data. Then he opens a user form, where he runs a macro by clicking the OK button. At this moment, the ThisWorkbook.Activate function does not work. The ActiveWorkbook.Name remains the other. But when the macro stops, Activeworkbook corrects itself correctly. Why can this happen? (I did not have this problem in the old Excel version that I had)

I've tried changing screenupdating. I've been trying to wait a moment before and after activating it. I tried to unprotect the leaf. I have no idea what to do.

Private Sub btOK_Click ()
& # 39; Application.ScreenUpdating = True
Plan_Inser_Ativ = ValorVariavel ("Plan_Inser_Ativ") & # 39; Function to take a saved text from the sheet name (it works)
ThisWorkbook.Sheets (Plan_Inser_Ativ) .Activate I tried to activate the workbook by this method, but it does not work.
ThisWorkbook.Activate & # 39; I tried to activate by this other method, but it does not work too much
& # 39; Application.Wait (Now + TimeValue ("0:00:03"))
ThisWorkbook.Activate I've tried to insist on seeing what happens :-)
WB_Ativa = ActiveWorkbook.Name The variable WB_Ativa remains with the name of the other workbook.
Stop When the macro stops, if I place the mouse on the ThisWorkbook.Name statement, this indicates that the active workbook is correct. But before the macro stopped, this was not the case.

enter the description of the image here

Create an Excel / Word Document Using the SPFX Web Part in SharePoint Online

I have a requirement on preparing Excel and Word documents based on SharePoint list data and uploading them to users in SharePoint Online using the SPFX Web Part.

My question is: Can we create an SPFx Web Part in which it creates a document using code and downloads it from the Web Part? OR is it even possible with the SPFx Web Part to create a document using encoding?

excel – The object does not support the property or the method readAsBinaryString & # 39; & Object does not support the property or the method & # 39; charCodeAt & # 39;

I use xlsx.core.min.js to download Excel files on SP and pass their content to a new list.

The first console error I received was "The object does not support the property or the method" charCodeAt "."

I've read about compatibility issues with .readAsArrayBuffer () and have it replaced. Then I started getting the error "The object does not support the property or the method" readAsBinaryString "."

How can I make this code work? Thanks in advance!

ExportToTable () function
var regex = / ^ ([a-zA-Z0-9s_\.-:]) + (. xlsx | .xls) $ /;
/ * Check if the file is a valid Excel file * /
if (regex.test ($ ("# excelfile"). val (). toLowerCase ())) {
var xlsxflag = false;
/ * Indicator to check if Excel is in .xls or .xlsx format * /
if ($ ("# excelfile"). val (). toLowerCase (). indexOf (". xlsx")> 0) {
xlsxflag = true;
/ * Check if the browser supports HTML5 * /
if (typeof (FileReader)! = "undefined") {
var reader = new FileReader ();
reader.onload = function (e) {
var data =;
/ * Convert Excel data to object * /
if (xlsxflag) {
var workbook = (data, {type: "binary"));
other {
var workbook = (data, {type: "binary"));
/ * Get all Excel sheet names in a variable * /
var sheet_name_list = workbook.SheetNames;

var cnt = 0; / * This is used to limit the script to take into account only the first Excel sheet * /
sheet_name_list.forEach (function (y) {/ * Iterate in all sheets * /
/ * Converts the value of the cell to Json * /
if (xlsxflag) {
var exceljson = XLSX.utils.sheet_to_json (workbook.Sheets[y])
other {
var exceljson = XLS.utils.sheet_to_row_object_array (workbook.Sheets[y])
// alert (exceljson.length + "----" + cnt);
if (exceljson.length> 0 && cnt == 0) {
BindTable (exceljson, # exceltable #);
cnt ++;
$ (# Exceltable #); show ();
if (xlsxflag) {/ * If the excel file is an .xlsx extension, this creates a table buffer from Excel * /
reader.readAsArrayBuffer ($ ("# excelfile")[0].files[0])
other {
reader.readAsBinaryString ($ ("# excelfile")[0].files[0])
other {
// alert ("Sorry, your browser does not support HTML5!");
other {
// alert ("Please download a valid Excel file!");


create a file composed of different lists then export to Excel

Is it possible to create a list in SharePoint composed of several different lists, then export them to Excel? So, for example, suppose I have 10 folders and within these, I have several tables of data. Can I take two fields from each list to create another list and then export that list to Excel?

excel – referencia a objeto no established c

    private void ExportToExcel ()
// Create an Excel object.
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application ();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add (Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;


spreadsheet = workbook.ActiveSheet;

worksheet.Name = "ExportedFromDatGrid";

int cellRowIndex = 1;
int cellColumnIndex = 1;

// Walk through each line and read the value of each column.
for (int i = 0; i <= dataGridView1 .Rows.Count; i ++)
for (int j = 0; j <dataGridView1 .Columns.Count; j ++)
// The Excel index starts at 1.1. As the first line would have the column headers, adding a condition check.
if (cellRowIndex == 1)
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridView1 .Columns[j].On your mind;
j = 1;
worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridView1 .Rows[i].Cellules[j].Value.ToString ();
cellColumnIndex ++;
cellColumnIndex = 1;
cellRowIndex ++;

// Get the location and name of the Excel file to register with the user.
SaveFileDialog saveDialog = new SaveFileDialog ();
saveDialog.Filter = "Excel Files (* .xlsx) | * .xlsx | All Files (*. *) | *. *";
saveDialog.FilterIndex = 2;

if (saveDialog.ShowDialog () == System.Windows.Forms.DialogResult.OK)
workbook.SaveAs (saveDialog.FileName);
MessageBox.Show ("Export Successful");
catch (System.Exception ex)
MessageBox.Show (ex.Message);
excel.Quit ();
workbook = null;
excel = null;