topic Excel VBO Extended - Copy and Paste - Keep Source Formatting in Product Forum

Excel VBO Extended - Copy and Paste - Keep Source Formatting

john.hammond — Fri, 27 May 2022 10:54:00 GMT

Morning all.

I've attempted this morning to make some amendments to the MS Excel VBO Extended Object - more particularly the Copy and Paste Worksheet Range function in order to keep the source formatting of the original worksheet. I'll put the code I've used below (with the amendments emboldened). However, when I run the code, I receive back the error message 'The given key was not present in the dictionary.'. Considering that I haven't made many changes, I imagine that the error relates to the fourth line below, where I assign the value -4122 to xlPasteFormats. I'm sure that it is something simple that I'm missing, but for the life of me I cannot work out what it is. Any assistance would be greatly welcomed!

Dim sw, dw As Object

Dim ss, ds As Object

Dim excel, sheet, source, destination, cells, cell As Object

Dim xlPasteFormats as Integer = -4122

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.PasteSpecial(xlPasteFormats)

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

------------------------------
John Hammond
------------------------------

RE: Excel VBO Extended - Copy and Paste - Keep Source Formatting

michaeloneil — Mon, 30 May 2022 13:49:00 GMT

Hi John

You wont need to set your pasteformat as an integer at the start you can set this in the paste itself, for example in one I created previously to paste either values or everything the vb reads - excel.Selection.PasteSpecial(Paste:=-4122, Operation:=-4142, SkipBlanks:=False, Transpose:=False).
I actually set this as an input parameter so the user sets true or false for values only and a calculation stage then sets a data item to the correct enum for the code to use e.g. excel.Selection.PasteSpecial(Paste:=Paste_Option, Operation:=-4142, SkipBlanks:=False, Transpose:=False)

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

RE: Excel VBO Extended - Copy and Paste - Keep Source Formatting

john.hammond — Wed, 01 Jun 2022 07:58:00 GMT

Hi there Michael, thank you for your response (and my apologies for the delay in getting back to you - currently mid-development!).

Just to be clear, where I had 'sheet.PasteSpecial(xlPasteFormats)' in the code above, replacing that with 'excel.Selection.PasteSpecial(Paste:=-4122, Operation:=-4142, SkipBlanks:=False, Transpose:=False)' should (after also removing 'Dim xlPasteFormats as Integer = -4122') just paste the values? I've tried to replicate this today and am still getting 'The given key was not present in the dictionary.'. For reference, I'm using the most recent Excel VBO from the DX as the base of my code. I've pasted the full code below with the changes discussed implemented. Apologies in advance, I'm very new to VBA!

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.PasteSpecial(Paste:=-4122, Operation:=-4142, SkipBlanks:=False, Transpose:=False)
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

------------------------------
John Hammond
------------------------------