Find Last Row

VJR · Aug 23, 2018

PriyaArvind said:
Thanks VJ for your response. Its failing again with the same reason.

: Failed to copy worksheet: Exception from HRESULT: 0x800A03EC

(I have found an alternative way of doing it by loading into collection and writing back from collection, but if this code works, it looks neat and it will be useful for others hence trying; much appreciate your time)

The updated code produced the desired output for me with only the values. So need to see where the issue is. It could be on my side too, but can you share what parameters are you passing such that it throws an error.

PriyaArvind · Aug 23, 2018

View attachment 1535026417957.png

PriyaArvind · Aug 23, 2018

if i change the service to copy and paste in work sheet range with same parameters it works.. so i can confirm parameters are right.

VJR · Aug 23, 2018

In the destination range just give "B5" and let me know your findings.

PriyaArvind · Aug 23, 2018

Error - : Failed to copy worksheet: Exception from HRESULT: 0x800A03EC

VJR · Aug 23, 2018

This is Sheet 4. Refer formula in C column
View attachment 1535008779677.png

Sheet5 without any data
View attachment 1535008838802.png

Parameters to the new custom action
View attachment 1535008892295.png

Sheet5 after running the new action pasted with only values and without the formula
View attachment 1535008989820.png

Below is the code in the new action

Code:

Dim sw, dw As Object
Dim ss, ds As Object
Dim excel, sheet, source, destination, cells, cell As Object

Try

sw = GetWorkbook(Handle, Source_Workbook)
dw = GetWorkbook(Handle, Destination_Workbook)

ss = GetWorksheet(Handle, Source_Workbook, Source_Worksheet)
ds = GetWorksheet(Handle, Destination_Workbook, Destination_Worksheet)

sw.Activate()
ss.Activate()
excel = ss.Application
sheet = excel.ActiveSheet
cell = excel.ActiveCell

If Source_Range="" Then
    cells = sheet.Cells
    Destination_Range = "A1"
Else
    cells = sheet.Range(Source_Range)
End If

cells.Select()
source = excel.Selection
source.Copy()
cell.Select()

dw.Activate()
ds.Activate()
sheet = excel.ActiveSheet
cell = excel.ActiveCell
destination = sheet.Range(Destination_Range)
destination.Select()
'sheet.Paste()
destination.PasteSpecial(Paste:=-4163)  '-4163 for xlPasteValues
cell.Select()

My.Computer.Clipboard.Clear()

Success = True

Catch e As Exception
    Success = False
    Message = e.Message
Finally
    sw = Nothing
    ss = Nothing
    dw = Nothing
    ds = Nothing
    excel = Nothing
    sheet = Nothing
    source = Nothing
    destination = Nothing
    cells = Nothing
    cell = Nothing
End Try

VJR · Aug 23, 2018

Refer the above input, output and code details.
Just make sure it is not throwing the same error from the previous code instance.
Reset, Refresh and Save the Object after the changes are made to it.

PriyaArvind · Aug 23, 2018

Thanks VJ.. i get it now.. i had given Sheet.PasteSpecial where as it should be
destination.PasteSpecial(Paste:=-4163)

It works !! thanks a lot for your efforts

Rulaxx · Dec 7, 2018

VJR said:

This is Sheet 4. Refer formula in C column
View attachment 1752

Sheet5 without any data
View attachment 1753

Parameters to the new custom action
View attachment 1754

Sheet5 after running the new action pasted with only values and without the formula
View attachment 1755

Below is the code in the new action

Code:

Dim sw, dw As Object
Dim ss, ds As Object
Dim excel, sheet, source, destination, cells, cell As Object

Try

sw = GetWorkbook(Handle, Source_Workbook)
dw = GetWorkbook(Handle, Destination_Workbook)

ss = GetWorksheet(Handle, Source_Workbook, Source_Worksheet)
ds = GetWorksheet(Handle, Destination_Workbook, Destination_Worksheet)

sw.Activate()
ss.Activate()
excel = ss.Application
sheet = excel.ActiveSheet
cell = excel.ActiveCell

If Source_Range="" Then
    cells = sheet.Cells
    Destination_Range = "A1"
Else
    cells = sheet.Range(Source_Range)
End If

cells.Select()
source = excel.Selection
source.Copy()
cell.Select()

dw.Activate()
ds.Activate()
sheet = excel.ActiveSheet
cell = excel.ActiveCell
destination = sheet.Range(Destination_Range)
destination.Select()
'sheet.Paste()
destination.PasteSpecial(Paste:=-4163)  '-4163 for xlPasteValues
cell.Select()

My.Computer.Clipboard.Clear()

Success = True

Catch e As Exception
    Success = False
    Message = e.Message
Finally
    sw = Nothing
    ss = Nothing
    dw = Nothing
    ds = Nothing
    excel = Nothing
    sheet = Nothing
    source = Nothing
    destination = Nothing
    cells = Nothing
    cell = Nothing
End Try

Hello VGR I get the next error ": Failed to copy worksheet: The given key was not present in the dictionary."

VJR · Dec 8, 2018

Rulaxx said:
Hello VGR I get the next error ": Failed to copy worksheet: The given key was not present in the dictionary."

The dictionary key error generally appears when the parameters either input or output or both are not correctly generated from its corresponding actions for eg; handle from Create Instance Or Workbook Name data item Or sheet name or incorrect path. Anything else like if the parameters are not passed correctly eg; handle is created for one workbook but passed for another also creates the issue.

Waszeq · Jan 18, 2019

VJR said:

This is Sheet 4. Refer formula in C column
View attachment 1752

Sheet5 without any data
View attachment 1753

Parameters to the new custom action
View attachment 1754

Sheet5 after running the new action pasted with only values and without the formula
View attachment 1755

Below is the code in the new action

Code:

Dim sw, dw As Object
Dim ss, ds As Object
Dim excel, sheet, source, destination, cells, cell As Object

Try

sw = GetWorkbook(Handle, Source_Workbook)
dw = GetWorkbook(Handle, Destination_Workbook)

ss = GetWorksheet(Handle, Source_Workbook, Source_Worksheet)
ds = GetWorksheet(Handle, Destination_Workbook, Destination_Worksheet)

sw.Activate()
ss.Activate()
excel = ss.Application
sheet = excel.ActiveSheet
cell = excel.ActiveCell

If Source_Range="" Then
    cells = sheet.Cells
    Destination_Range = "A1"
Else
    cells = sheet.Range(Source_Range)
End If

cells.Select()
source = excel.Selection
source.Copy()
cell.Select()

dw.Activate()
ds.Activate()
sheet = excel.ActiveSheet
cell = excel.ActiveCell
destination = sheet.Range(Destination_Range)
destination.Select()
'sheet.Paste()
destination.PasteSpecial(Paste:=-4163)  '-4163 for xlPasteValues
cell.Select()

My.Computer.Clipboard.Clear()

Success = True

Catch e As Exception
    Success = False
    Message = e.Message
Finally
    sw = Nothing
    ss = Nothing
    dw = Nothing
    ds = Nothing
    excel = Nothing
    sheet = Nothing
    source = Nothing
    destination = Nothing
    cells = Nothing
    cell = Nothing
End Try

Hi VJR,

I have different issue:
View attachment issue.png
Do you have any idea how to fix this?

mandeepsharma2004 · Aug 5, 2019

Hi Priya/All,

Can you suggest solution because I have same requirement too?

I want to copy data of last column that has value which means, first go to last empty column and then "-1"

PriyaArvind · Aug 7, 2019

mandeepsharma2004 said:
Hi Priya/All,

Can you suggest solution because I have same requirement too?

I want to copy data of last column that has value which means, first go to last empty column and then "-1"

Use getRange service and see if you get the desired output

mandeepsharma2004 · Aug 18, 2019

Thanks It worked

charan369 · Mar 9, 2020

Rulaxx said:
Hello VGR I get the next error ": Failed to copy worksheet: The given key was not present in the dictionary."

hi VJR,

even i am facing the same issue. "Failed to copy worksheet: The given key was not present in the dictionary"

when i am using same parameters and trying for default action it is working fine .
but when i am trying to use my customized code (i.e. code with your suggested changes ) it is trowing error
"Failed to copy worksheet: The given key was not present in the dictionary" . please suggest thanks in advace

charan369 · Mar 9, 2020

VJR said:
The dictionary key error generally appears when the parameters either input or output or both are not correctly generated from its corresponding actions for eg; handle from Create Instance Or Workbook Name data item Or sheet name or incorrect path. Anything else like if the parameters are not passed correctly eg; handle is created for one workbook but passed for another also creates the issue.

hi VJR,

even i am facing the same issue. "Failed to copy worksheet: The given key was not present in the dictionary"

when i am using same parameters and trying for default action it is working fine .
but when i am trying to use my customized code (i.e. code with your suggested changes ) it is trowing error
"Failed to copy worksheet: The given key was not present in the dictionary" . please suggest thanks in advace

Dolly · Apr 5, 2020

hi VJR

I tried with your code but I am getting exception "The given key was not present in the dictionary."
Can you please guide if you have idea regarding this.

charan369 · Apr 8, 2020

Dolly said:
hi VJR

I tried with your code but I am getting exception "The given key was not present in the dictionary."
Can you please guide if you have idea regarding this.

hi Dolly ,

have used the same MS Exel VBO and created a new action in that

or You have customized in a new object

if you are creating a new action in default VBO it should work or else you are not passing parameters correctly (syntax error)

if u have customized the logic in new object make sure that you need to use attach action of your customized code and then u need use your copy & paste worksheet range (customized) action

Find Last Row

VJR

Well-Known Member

PriyaArvind

Member

PriyaArvind

Member

VJR

Well-Known Member

PriyaArvind

Member

VJR

Well-Known Member

VJR

Well-Known Member

PriyaArvind

Member

Rulaxx

New Member

VJR

Well-Known Member

Waszeq

New Member

mandeepsharma2004

New Member

Attachments

PriyaArvind

Member

mandeepsharma2004

New Member

charan369

New Member

charan369

New Member

Dolly

New Member

charan369

New Member