CODEWITHSUNDEEP
excelvb.net-2010vba
Rupesh
Rupesh

Reputation: 1043

Copy picture and data from one excel file to another excel file

I have an excel file which contains picture in first five rows and then the data starts. My job is to copy the file as it is into another excel file. Firstly, I open the file and copy the used range. Next I paste the contents into the another excel file. 

 lobjCurrentWorkSheet.UsedRange.Copy()

 lobjTargetExcelWorkSheet.PasteSpecial(XlPasteType.xlPasteAll)

By doing the above I could copy only the data but not the picture? How would I copy the file as it is to another one?

If I use SaveAs option and if the source file is password encrypted then my target file will also be password encrypted which I don't want.

Upvotes: 1

Views: 10632

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149297

I would never recommend using UsedRange

Reason:

Your data might be from Cell A1 to B4 and the image is in say D1. The UsedRange in this case will always be A1:B4 and not A1:F6

enter image description here

If you want your picture to be copied then either find out the "Actual Range" of the worksheet or specify the range

lobjCurrentWorkSheet.Range("A1:F6").Copy()
lobjTargetExcelWorkSheet.Paste()

EDIT: I was trying to lay my hands on VB.Net so that I could give you an exact sample code. I finally did :)

See this (This will copy the actual range without you having to specify it.)

    Dim shp As Excel.Shape
    Dim lCol As Integer = 0

    '~~> Loop through all shapes and find the last col of the shape
    For Each shp In lobjCurrentWorkSheet.Shapes
        If shp.BottomRightCell.Column > lCol Then _
        lCol = shp.BottomRightCell.Column
    Next

    With lobjCurrentWorkSheet
        '~~> Find actual last Row
        Dim LastRow As Integer = .Cells.Find(What:="*", _
                     After:=.Range("A1"), _
                     LookAt:=Excel.XlLookAt.xlPart, _
                     LookIn:=Excel.XlFindLookIn.xlFormulas, _
                     SearchOrder:=Excel.XlSearchOrder.xlByRows, _
                     SearchDirection:=Excel.XlSearchDirection.xlPrevious, _
                     MatchCase:=False).Row
        '~~> Find actual last column
        Dim LastColumn As Integer = .Cells.Find(What:="*", _
                    After:=.Range("A1"), _
                    LookAt:=Excel.XlLookAt.xlPart, _
                    LookIn:=Excel.XlFindLookIn.xlFormulas, _
                    SearchOrder:=Excel.XlSearchOrder.xlByColumns, _
                    SearchDirection:=Excel.XlSearchDirection.xlPrevious, _
                    MatchCase:=False).Column

        '~~> Check if we have the correct last columnm
        If LastColumn < lCol Then LastColumn = lCol

        .Range("A1:" & Split(.Cells(, LastColumn).Address,
        "$")(1) & LastRow).Copy()
    End With

    '~~> Copies to the current active cell in lobjTargetExcelWorkSheet
    lobjTargetExcelWorkSheet.Paste()

Upvotes: 2

Related Questions