Comments by Randomuser787 (Top 41 by date)

yes posting in the next question please let me know in the answers

SURE will work on that thanks

thanks i figured rather directly giving time stamp from here i gave trigger for after insert.

now im trying to do is that in this same application when a user logged inand creates/edits the records i need that current logged in username to be passed into db how can i do that any reference or links with yt videos would help

i can see that i can use old and new .
should i give old and new for all the columns there is there any way that i can only take updated ones old and new in after update

Yes your correct i want to give a time stamp for the registration and update of a student in MVC
I want to figure out how to do that
1. when registering username+ timestamp
2. when updating username+ timestamp +previous value along with present value with changed time

I'm trying this out from scrath to implement on student application of MVC
I tried given db approach and now I'm trying for code first approach

I'm trying this below code which I'm adding above now on what I have tried

private static void EndProcess()
{
System.Diagnostics.Process[] process = System.Diagnostics.Process.GetProcessesByName("Excel");
foreach (System.Diagnostics.Process p in process)
{
if (!string.IsNullOrEmpty(p.ProcessName))
{
try
{
p.Kill();
}
catch { }
}
}

}

private static void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Unable to release the Object" + ex.ToString());
}
finally
{
GC.Collect();
}
}

tried this both no luck as it is leaving 2 in process

done. i have passed sheet password as string it didn't work now I changed to object it is working now

I have tried this in my code where I'm having 6 sheets and ding like this but not worked yet.

foreach (string Sourcesheet in Source)
{

WorkSheet1 = SourceWB.Worksheets[Sourcesheet.ToString()];
WorkSheet1.Protect(sheetPassword);
SourceWB.Save();
}

same as source to blank template

foreach (string Blanksheet in Blank)
{

WorkSheet2 = SourceWB.Worksheets[Blanksheet.ToString()];
WorkSheet2.Protect(sheetPassword);
BlankWB.Save();
}

BlankWB.Save();
MessageBox.Show("Template saved!!");
releaseObject(_excel);

this was my code ending still :-( its not protecting.

Thanks for your suggestion sir!!

Have tried debugging all
as I have given used range the if loop on row and col goes to 2000 on all 6 sheets unnecessarily if there is no data also. and now just for testing hard coded numbers of columns and rows
and if columns are mismatching it is just removing the columns header name and not going to next on the destination but instead starting at first on the next row.

these are what I noticed on debugging.

Actually the task was to copy and paste cells/sheets from the source excel workbook to destination workbook which doesn't have formulas ignore if columns are missing in destination

Thanks tho. but a minor change require in mine with 2 conditions which I'm working on

1. copy whole workbook and paste to destination dynamically
2. copy only values which doesn't have formulas both places

both are done but in cell by cell approach the list string makes all cells there in file till 2000
and taking a lot of time

so im working on it

yeah sorry , my mistake somone in my system posted.. i tried to delete its not happening..

so meanwhile as It came out i have 1 question for u if i use xl paste values in that its an error that "to do this all merged cells need to be same size" as in source there 6 columns and in destination there are 8 that too in extra 2 columns in between, so it is braking there so i tried for cell by cell copy pasting but using string there making every cell in that file copy paste till 2000 rows. so figuring it out what change here.

Here is the main code for whole workbook::- can u optimise it that it only goes through the usedrange cells rather than empty cells from wb1

*Workbook workbook1 = _excel.Workbooks.Open(excelFile1);
Workbook workbook2 = _excel.Workbooks.Open(excelFile2);
List<string> wb1 = GetAllSheetName(excelFile1);
List<string> wb2 = GetAllSheetName(excelFile2);
            //Compare both sheetNames
            Worksheet worksheet1 = null;
Worksheet worksheet2 = null;
//Range ranges = ;
for (int i = 1; i <= 6; i++)
{
foreach (string sheet1 in wb1)
{
worksheet1 = workbook1.Worksheets[sheet1.ToString()];
foreach (string sheet2 in wb2)
{
worksheet2 = workbook2.Worksheets[sheet2.ToString()];
if (sheet1.Equals(sheet2) && i == 3)
{
for (int rowIX = 1; rowIX <= worksheet1.UsedRange.Rows.Count; rowIX++)
//if (Range.UsedRange.Rows.Count == 0)

{
for (int colIX = 1; colIX <= worksheet1.UsedRange.Columns.Count; colIX++)
// if (Range.UsedRange.Columns.Count == 0)
{
Range src = worksheet1.Cells[rowIX, colIX];
if (!src.HasFormula )
{

Range dst = worksheet2.Cells[rowIX, colIX];
if (!dst.HasFormula)
{

dst.Value = src.Value;

}
}

}
}
}
}
}
}
workbook2.SaveAs(Filename: excelFile2, AccessMode: XlSaveAsAccessMode.xlNoChange);
workbook2.Close();
workbook1.Close();
releaseObject(_excel);
_excel.Quit();
}*

hi, how can i check the same
Range ranges = worksheet1.UsedRange.Cells.SpecialCells(XlCellType.xlCellTypeConstants);

to worksheet2 to paste to the cells which doesn't have formulas..

and to copy paste whole work book rather than sheet


which is actually copy pasting from source complete cells to destination cells checking both files for formulas

thank you both figured it out. it was my excel version error ran in another system got the output perfect t

yes i donno what is going wrong .. all cells are going good... why this leaving column which have formulas making this much tough...!!can u share the email id .. ill share the doc what I'm trying to do

donno ended it. and checked , both programs I have tried ,, earlier graeme copying all values but now both new solutions for it not working.. ran it for 10 mins each and cell by cell ended due to auto debug ending and graeme I ended at 5 mins no cells where copied ... now I tensed..... ill share u the zip u can check out and could share the same with excel files if u can send a hi text to my mail.

i think cell by cell still running from 10 mins, don't know what happened!!

thanks sir. Ran cell by cell code. from my previous reply, it is running still. I will try this thank you very much!!

sure.. i tried will give a try once more

yeah got this earlier from stack overflow. So by using this where will I get usedrange.copy or copy statement from source??


does that look like ??

Range ranges = worksheet1.UsedRange.Cells.SpecialCells(XlCellType.xlCellTypeConstants);

foreach (object rangeObject in ranges)
{
Range range = (Range)rangeObject;
string address = range.Cells.Address;
// output to console, debug or log, etc


// copy all used cells with formatting & formulas
worksheet1.UsedRange.Copy(Type.Missing);

// paste all copied cells with formatting & formulas
worksheet2.UsedRange.PasteSpecial(
XlPasteType.xlPasteAll,
XlPasteSpecialOperation.xlPasteSpecialOperationNone,
Type.Missing, Type.Missing);
}

i have a-o columns and 250 rows,

I needed d-n columns to be copy pasted as graeme said to use "excel range as we use in excel for column" confused and had a little idea on that..!!

thanks . ::-) i ran it for a min and stopped to check ,,,a single cell has not copied I think i shld run it for more time. my data starts from d column to k column in the sheet.. i think that takes more time...hope the data gets copied ::-)

So figured it but stuck here

worksheet1.UsedRange.Copy("D1:K1");

or

worksheet1.Copy("D1:K1");

what should i do ??

shld i start with
adding to your code
int StartColumn
int EndColumn
int[] ColumnsToSkip


sorry , im confused here because of u mentioned "worksheet1.UsedRange.Copy(Type.Missing);. You would need to copy a range or multiple ranges of cells"


needed from d to k columns

So i place this at !!!

yes so i tried -



            int lastLine = Math.Max(
                worksheet1.UsedRange.Rows.Count,
                worksheet2.UsedRange.Rows.Count);

            for (int i = 1; i <= lastLine; i++)
            {
                //for (int j = 1; j <= lastLine; j++)
                //{
                    Range c = worksheet1.Cells[i];
                    if (!(c.HasFormula))
                    {}}

where should i correct myself and implement in my code

i just wanna ignore the columns which have formulas, as I tried-

            int lastLine = Math.Max(
                worksheet1.UsedRange.Rows.Count,
                worksheet2.UsedRange.Rows.Count);

            for (int i = 1; i <= lastLine; i++)
            {
                //for (int j = 1; j <= lastLine; j++)
                //{
                    Range c = worksheet1.Cells[i];
                    if (!(c.HasFormula))
                    {//copy and paste //}}



so where should i change if i need to check the column as it has formula or not and ignore if it has and and paste next column and so on..

thanks for your time and if possible can u help me with this

ok thanks. check if the template file's formula will be overwritten/removed if u clear after u paste

have tried this but, now not a single cell is pasting and if we clear all the cells in destination which have a formula then the formulas in the template file gets removed/over written

so how can i get the data only without formulas .

can u get me logic/code to implement in mine

no, just want to leave the cells blank if there is a formula in source!!

yes, its copy pasting all cells from source to template but if there is formula then I dont want that to get paste in template.. any solution??

Thanks for the code it is copy pasting all cells from source to workbook from your code how can I only copy paste the cells which doesn't have formulas ?? in source workbook and destination template