Website uses gravity forms, we have a local excel document we use for business processes. We are aiming to either use an integration or an automatic regular download to bring form submissions into the local excel doc.
We do not want to migrate the excel doc to google sheets
Any thoughts on the best process for this?
Tag: Excel
How to iterate through data and write to excel file quicker with C# and openXML
I have a pretty simple method that grabs data from an sql query and then puts it inside of a list and finally iterates through two for each loops onto an excel file. It works fine with small amounts of data, but not at all with larger amounts. What would be the best way to iterate and also write to my workbook the quickest? I am using OpenXML.
Here is the code
private UInt32 AddSheets(SpreadsheetDocument document, WorkbookPart workbookPart, Sheets sheets, string sheetName, UInt32 sheetNumber, List<RentalFeeReportingRecord> currentRecordList)
{
Dictionary<int, string> listofCoreBankingIds = new Dictionary<int, string>();
listofCoreBankingIds = currentRecordList.Select(x => new { id = x.CoreBankingId, regionName = x.RegionName }).Distinct().ToDictionary(y => y.id, y => y.regionName);
foreach (var coreBankingId in listofCoreBankingIds.OrderBy(x => x.Value))
{
WorksheetPart worksheetPart1 = workbookPart.AddNewPart<WorksheetPart>();
string currentCurrency = null;
string regionName = null;
Worksheet worksheet1 = new Worksheet();
SheetData sheetData1 = new SheetData();
Row rowInSheet1 = new Row();
Row firstRow = new Row();
Row lastRow = new Row();
rowInSheet1.Append(
excelController.ConstructCell("Region", CellValues.String, 3), excelController.ConstructCell("Merchant - Terminal", CellValues.String, 3),
excelController.ConstructCell("Fee Charged", CellValues.String, 3),
excelController.ConstructCell("Currency", CellValues.String, 3),
excelController.ConstructCell("Processing Date", CellValues.String, 3),
excelController.ConstructCell("Description", CellValues.String, 3)
);
sheetData1.Append(rowInSheet1);
foreach (var currentRecord in currentRecordList.OrderBy(x => x.RegionName).ThenBy(z => z.Currency).ThenBy(y => y.DateProcessed))
{
if (coreBankingId.Key == currentRecord.CoreBankingId)
{
regionName = currentRecord.RegionName;
currentCurrency = currentRecord.Currency;
double currentTotal = currentRecord.TotalFeeAmount;
string merchant = currentRecord.MerchantRecord.MerchantID + " - " + currentRecord.MerchantTerminalRecord.terminalID;
if (currentRecord.IsPinPad)
{
merchant = merchant + " - PIN Pad";
}
string description = currentRecord.TerminalRecord.description;
if (currentRecord.IsProRated)
{
int days = DateTime.DaysInMonth(currentRecord.DeployedDate.Year, currentRecord.DeployedDate.Month);
description = currentRecord.TerminalRecord.description + " (" + currentRecord.MonthlyFee + " Fee \ " + days.ToString() + " Days) * " + currentRecord.DaysActive.ToString() + " Days Active";
}
rowInSheet1 = new Row();
firstRow = new Row();
lastRow = new Row();
rowInSheet1.Append(
excelController.ConstructCell(regionName, CellValues.String, 2),
excelController.ConstructCell(merchant, CellValues.String, 2),
excelController.ConstructCell(currentRecord.TotalFee.ToString("N2"), CellValues.String, 1),
excelController.ConstructCell(currentRecord.Currency, CellValues.String, 2),
excelController.ConstructCell(currentRecord.DateProcessed.ToString("MMM dd, yyyy"), CellValues.String, 2),
excelController.ConstructCell(description, CellValues.String, 2)
);
sheetData1.Append(rowInSheet1);
}
}
Columns columns = excelController.AutoSize(sheetData1);
worksheet1.AppendChild(columns);
worksheet1.AppendChild(sheetData1);
worksheetPart1.Worksheet = worksheet1;
Sheet sheet1 = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheetPart1),
SheetId = sheetNumber,
Name = regionName
};
sheets.Append(sheet1);
sheetNumber++;
}
return sheetNumber;
}
Any help would be appreciated.
worksheet function – Excel data query
I’m trying to build out a budget monitoring spreadsheet. I want to pull sum of entries by category and based on the month. The data is in rows as follows and will grow each month as I download our spend.
Date Category Amount
03-15-21 Auto 500.00
02-12-21 House 375.00
Ultimately I’ll have it displayed with months across columns and categories in rows so we can monitor our budget.
How can I pull out the data by category and month?
I’ve looked at sumifs, sumproduct, index match, haven’t found a solution. I appreciate any guidance.
microsoft excel – Connecting slicer for boxplot with other pivot table
Is it possible to connect slicer for a boxplot sourcing data from the same table as the other pivot tables? I tried to report connection of my slicer for boxplot, but that feature seems to not available. And if not, is there a way to make a boxplot using pivot table?
excel – Best way to copy data to a new sheet and reorganize it (VBA)
I’m writing a VBA program which copies and organizes data from one master sheet into numerous other sheets. One of the recipient sheets unifies all the data from the master sheet which holds the same id number into a single row. For this operation, I am looping through the master sheet for each id number, copying each row which holds the current id number into a new sheet purely used for calculations and organizing, and rearranging the data in this sheet into the new row. The resultant row is copied into the recipient sheet. This process of organizing data for every id number takes a long time to process, especially given the very large size of this sheet and the processing time of the other recipient sheets. I’m wondering if there is a better way to organize and copy data without using an intermediate calculation sheet.
The below code is the main sub, which calls another sub OrganizeAndCopyToPal, which organizes the data in the calculation sheet and copies the result into the recipient sheet.
Sub PalletAssemblyLog()
Dim allidNum As Range
Dim curridNum As Range
Dim rowCount As Long
Dim idNum
Dim I As Long
Dim j As Long
Dim machineLoc As String
Dim calc As Worksheet
Dim full As Worksheet
Dim pal As Worksheet
Set calc = Sheet3
Set full = Sheet4
Set pal = Sheet1
For I = 2 To rowCount
idNum = full.Cells(I, 17).Value
For j = 2 To rowCount
If full.Cells(j, 17).Value = idNum Then
If allidNum Is Nothing Then
Set allidNum = full.Cells(j, 17)
Else
Set allidNum = Union(allidNum, full.Cells(j, 17))
End If
End If
Next j
Set curridNum = allidNum.EntireRow
calc.Activate
calc.Cells.Clear
full.Activate
curridNum.Copy calc.Range("A1")
OrganizeAndCopyToPal curridNum
Next I
End Sub
The below sub organizes and copies the data for each id number. The final sub to copy the data isn’t related to the matter of simplifying this task so I’m not including it.
Sub OrganizeAndCopyToPal(curridNum)
Dim calc As Worksheet
Dim pal As Worksheet
Set calc = Sheet3
Set pal = Sheet1
calc.Activate
Dim rowCount As Long
rowCount = calc.Cells(Rows.Count, "A").End(xlUp).Row
Dim palRow As Long
palRow = rowCount + 2
Dim partRow As Long
partRow = palRow + 2
Dim currPartCount As Range
Dim assembly As String
Dim id As String
Dim location As String
Dim machType As String
Dim machLoc As String
Dim currPart As String
Dim link As String
Dim tot As Long
tot = 0
With calc
.Cells(1, 1).Copy .Cells(palRow, 2)
assembly = .Cells(1, 1).Value
.Cells(1, 2).Copy .Cells(palRow, 5)
id = .Cells(1, 17).Value
asArray = SplitMultiDelims(id, "|-")
'MsgBox asArray(0) & " " & asArray(1) & " " & asArray(2)
machArray = Split(.Cells(1, 8), "-")
machType = machArray(0)
.Cells(palRow, 3) = machType
machLoc = .Cells(1, 8).Value
.Cells(palRow, 4) = machLoc
.Cells(1, 17).Copy .Cells(palRow, 10)
location = Cells(1, 9)
.Cells(palRow, 1) = location
For I = 1 To rowCount
partArray = Split(.Cells(I, 16).Value, ",")
For j = 0 To UBound(partArray)
partArray2 = Split(partArray(0), "-")
partPrefix = partArray2(0)
If j = 0 Then
currPart = partArray(j)
Else
currPart = partPrefix & "-" & CStr(partArray(j))
End If
tf = 1
For k = 0 To tot
If Cells(partRow + k, 1).Value = currPart Then
tf = 0
Exit For
End If
Next k
If tf = 1 Then
.Cells(partRow + tot, 1).Value = currPart
tot = tot + 1
End If
Next j
Next I
For I = 1 To tot
Cells(palRow, 10 + I).Value = Cells(partRow + I - 1, 1)
Next I
End With
CopyToPal curridNum, palRow
End Sub
Thank you for any tips or help that you can offer.
MACRO COPIA E COLA VBA EXCEL – AJUDA
Bom dia amigos, antes de vir pedir a ajuda eu posso dizer que literalmente quase quebrei a cabeça
Estou aprendendo VBA para agilizar meu serviço na empresa, mas estou travado na seguinte parte:
Estava desenvolvendo uma macro que na teoria faria o seguinte caminho:
1° Moveria o mouse até a célula A2
2° Copiaria o conteudo da celular A2
3° Colaria esse conteúdo em um bloco de notas especifico
4°Fim
5°Como ele está na celula A2, a posição do mouse se moveria para barra e daria 1 clique, movendo para Célula A3 e fazendo o loop no processo.
Consegui faze o processo de mouse, tecla de atalho própria. Porém quando chega na parte de copiar, ele esta selecionando 2 celulas e consequentemente dando erro por nao conseguir fazer 2 celulas ao memso tempo… Além do CTRL V que não está funcionando…
Enfim, se alguem puder me ajudar ou desenvolver para mim seria muito grato. Me ajudaria a ver onde estaria errando e me ajudaria no aprendizado.
microsoft excel – Line graph of a single Cell that is dynamically changing
I have one cell in Excel that is simply the subtraction result of two other cells. The subtraction number is dynamic i.e. it is continually changing throughout the day based on the values of the two original cells.
I want to plot a line graph that keeps track on the chart of every single number change in the subtraction cell. How do I do that.
Currently I can create a simple line chart but it plots ONLY the number currently showing in the cell. i.e when the number changes, the graph doesnt keep the previous number and so on…
excel – MS Flow stops working when I update the file in SharePoint
I have a MS Flow that updates a list in SharePoint from an Excel file that I manually upload.
Whenever I upload a new file, the MS Flow stops working, see, inside the Excel file I have a table, that I pull from another system, then the file is uploaded to a site inside SharePoint. Every time I upload a new file I have to update the MS Flow to grab the “new” file, the file is always named the same as the previous one.
This is the error that shows every time I update the file:
No table was found with the name '{235AC59A-72C7-4A62-B137-00E0B394AB3E}'.
clientRequestId: xxx-xx-xxx-xxx-xxx
serviceRequestId: xxx-xx-xx-xxx-xxx
The table is named “Table 1” before the file is updated, after that, it is shown as below.
Excel Files keep getting saved as 0 KB on an SMB Share
Our users Excel Files keep getting saved as 0 KB on an SMB Share, it seems like it’s a timeout issue, are there any SMB settings that can be changed on the Windows Server service or SMB protocol that can be used to increase the timeout?
do excel data entry, web research, convert pdf to word excel for $5
do excel data entry, web research, convert pdf to word excel
Welcome To my Gig
Hello, My name is samiul and I am Certified with Microsoft workd Specialist. I am an expert data entry worker. I provide you a quality services in this field.
If you need to collect data from any site OR any web directory OR any
document like PDF, word or scanned file, You can choose me.
My services includes
✔ Excel data entry
✔ Typing work
✔ Copy typing
✔ Punjabi typing
✔ Excel Spreadsheet
✔ Excel VBA
✔ PowerPoint Presentation design
EXPERTISE
✔ Data Entry
✔ Document formatting
✔ Microsoft office
✔ Document editing
✔ Typing
✔ Microsoft excel
✔ Data mining
✔ Virtual assistant
✔ Data collection
✔ Word to pdf
✔✔✔Available 24/7
I can deliver your order within the deadline with 100% accuracy.
I would love to hear about your project’s requirements.
Just leave me a message.
.