dcsimg
Results 1 to 12 of 12

Thread: [RESOLVED] How to Import A Picture Into An Excel Worksheet Using VB.net

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Feb 2012
    Posts
    78

    Resolved [RESOLVED] How to Import A Picture Into An Excel Worksheet Using VB.net

    I want to insert a picture into cell A1 on a worksheet that is labeled "TOC". This picture can be grabbed from one of 2 places:
    1. The image is currently on the worksheet labeled "Cover"...so I can try to pull it from there.
    2. I could add the image to my project resources in Visual Studio 2008, and grab it from there.

    I am able to find and select the image with this code:
    Dim CvrSheet As Worksheet

    For Each CvrSheet In Globals.ThisAddIn.Application.ActiveWorkbook.Sheets
    If CvrSheet.Name = "Cover" Then
    CvrSheet.Select()
    CvrSheet.Shapes.Range("Logo").Select()
    End If
    Next
    Everything I have tried to copy the image has failed, so I have not even been able to try to paste the image in cell "A1" on the "TOC" sheet. So, I am at a complete loss as to how to do this.
    Last edited by fdegree; Mar 8th, 2014 at 08:12 PM.

  2. #2

    Thread Starter
    Lively Member
    Join Date
    Feb 2012
    Posts
    78

    Re: How to Import A Picture Into An Excel Worksheet Using VB.net

    I have continued to work on this, and here is what I have so far...

    I recorded a Macro to see how that code looked, and here is that Macro:
    Code:
        Sheets("Sheet1").Select
        ActiveSheet.Shapes.Range(Array("MyLogo")).Select
        Selection.Copy
        Sheets("Sheet2").Select
        Range("A1").Select
        ActiveSheet.Paste
        Range("A1").Select
    Here is the VB.net code I have so far...all of which works perfectly. It selects the "Logo" on "Sheet1" and it also pastes whatever happens to be on the clipboard...the problem is the "Logo" image is not being copied:
    Code:
            Dim CvrSheet As Worksheet
            Dim TOCSheet As Worksheet
            CvrSheet = Globals.ThisAddIn.Application.Sheets("Sheet1")
            TOCSheet = Globals.ThisAddIn.Application.Sheets("Sheet2")
            CvrSheet.Select()
            CvrSheet.Shapes.Range("MyLogo").Select()
    
    'I need the proper code here for copying the "Logo" image
    
            TOCSheet.Select()
            TOCSheet.Range("A1").PasteSpecial()
    I could be wrong, but I think I need to do something like this:
    Code:
            Dim CvrSheet As Worksheet
            Dim TOCSheet As Worksheet
    
            Dim Logo As 'Something, I'm not sure what. Everything I tried didn't work
    
            CvrSheet = Globals.ThisAddIn.Application.Sheets("Sheet1")
            TOCSheet = Globals.ThisAddIn.Application.Sheets("Sheet2")
            CvrSheet.Select()
    
            Logo = CvrSheet.Shapes.Range("MyLogo")
    
            Logo.Copy()
    
            TOCSheet.Select()
            TOCSheet.Range("A1").PasteSpecial()
    I have underlined the changes I think I need. But, everything I have tried has failed. I am truly frustrated now.

  3. #3
    Addicted Member 3com's Avatar
    Join Date
    Jul 2013
    Location
    Spain
    Posts
    253

    Re: How to Import A Picture Into An Excel Worksheet Using VB.net

    Excel macro code

    Code:
    Sub AddLogo(logoPath As String)
    
        ThisWorkbook.Worksheets("TOC").Select
        Set myDocument = ThisWorkbook.Worksheets("TOC")
        myDocument.Shapes.AddPicture logoPath, _
        True, True, 100, 100, 70, 70 'Change this values by yours...
        
    End Sub

    VB.Net code
    Code:
    Private sub addMyLogo()
    
    	Dim oExcel As Excel.ApplicationClass
            Dim oBook As Excel.WorkbookClass
            Dim oBooks As Excel.Workbooks
    
            'Start Excel and open the workbook.
            oExcel = CreateObject("Excel.Application")
            oExcel.Visible = True
            oBooks = oExcel.Workbooks
            oBook = oBooks.Open("c:\book1.xls")
    
            'Run the macro with parameter.
            oExcel.Run("AddLogo", "C:\Logo.jpg")
    
            'Clean-up: Close the workbook and quit Excel.
            oBook.Close (False)
            System.Runtime.InteropServices.Marshal.ReleaseComObject (oBook)
            oBook = Nothing
            System.Runtime.InteropServices.Marshal.ReleaseComObject (oBooks)
            oBooks = Nothing
            oExcel.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject (oExcel)
            oExcel = Nothing
    
    End sub
    HTH

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Feb 2012
    Posts
    78

    Re: How to Import A Picture Into An Excel Worksheet Using VB.net

    Thank you 3com for the input. I was truly hoping to simply copy the image from "Sheet1". But, since I was unable to figure that out, I kept working at it until just now finding a solution...

    I can add a folder to the users appdata folder and store their logo in it. Then I can run the following code to get the logo from that folder and insert it on the TOC sheet:
    Code:
            Dim TOCSheet As Worksheet
            TOCSheet = Globals.ThisAddIn.Application.Sheets("Table Of Contents")
    
            Dim LogoFilePath As String
    
            LogoFilePath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)
            TOCSheet.Shapes.AddPicture(LogoFilePath & "\Templates\Logo\Logo.png", _
            Microsoft.Office.Core.MsoTriState.msoFalse, _
            Microsoft.Office.Core.MsoTriState.msoCTrue, 1, 1, 130, 69)
    ***If someone can solve the issue with my inability to copy the "Logo" from "Sheet1", I would sincerely appreciate it. I would rather solve this issue with that approach, instead of creating folders, and adding files to the users machine.

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,438

    Re: How to Import A Picture Into An Excel Worksheet Using VB.net

    this is the vba code
    Code:
        Set s = Sheets("sheet2")
        s.Shapes(1).Copy
        Set s = sheets("sheet3")
        s.Paste Range("a2")
    should be no problem to convert that to .net, or you can continue to call an excel macro, change the index of the shape (picture) to copy (or you can use the name for the shape object), sheet names and the target cell to suit, you can resize and position new shape after pasting if required
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Feb 2012
    Posts
    78

    Re: How to Import A Picture Into An Excel Worksheet Using VB.net

    Thanks westconn1...

    When I try to transpose the VBA code .net, here is what I have:
    Code:
            Dim CvrSheet As Worksheet
            Dim TOCSheet As Worksheet
            CvrSheet = Globals.ThisAddIn.Application.Sheets("Sheet1")
    
            CvrSheet.Shapes("Logo").Copy()
    
            TOCSheet = Globals.ThisAddIn.Application.Sheets("Sheet2")
            TOCSheet.Range("A1").Paste()
    Intellisense gives the following error, in reference to the underlined part of the code above:
    Interface 'Microsoft.Office.Interop.Excel.Shapes' cannot be indexed because it has no default property.

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,438

    Re: How to Import A Picture Into An Excel Worksheet Using VB.net

    loop through the shapes till the name matches

    Code:
    for each shp in cvrsheet.shapes
        if shp.name = "Logo" then shp.copy:exit for
    next
    i believe should work, Logo would be case sensitive

    as the code is in an addin i would fully qualify the worksheets
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Feb 2012
    Posts
    78

    Re: How to Import A Picture Into An Excel Worksheet Using VB.net

    THANK YOU...THANK YOU...THANK YOU!

    That worked. Here is the exact code that is working for me:
    Code:
            Dim CVRSheet As Worksheet
            CVRSheet = Globals.ThisAddIn.Application.Sheets("Cover")
    
            Dim TOCSheet As Worksheet
            TOCSheet = Globals.ThisAddIn.Application.Sheets("Table Of Contents")
    
    
            For Each shp In CVRSheet.Shapes
                If shp.name = "Logo" Then shp.copy() : Exit For
            Next
            TOCSheet.Paste()
    I didn't think copying this logo would be so challenging.

    Again, thanks...I sincerely appreciate this.

  9. #9
    New Member himsha's Avatar
    Join Date
    Dec 2012
    Location
    India
    Posts
    7

    Re: How to Import A Picture Into An Excel Worksheet Using VB.net

    Quote Originally Posted by 3com View Post
    Excel macro code

    Code:
    Sub AddLogo(logoPath As String)
    
        ThisWorkbook.Worksheets("TOC").Select
        Set myDocument = ThisWorkbook.Worksheets("TOC")
        myDocument.Shapes.AddPicture logoPath, _
        True, True, 100, 100, 70, 70 'Change this values by yours...
        
    End Sub

    VB.Net code
    Code:
    Private sub addMyLogo()
    
    	Dim oExcel As Excel.ApplicationClass
            Dim oBook As Excel.WorkbookClass
            Dim oBooks As Excel.Workbooks
    
            'Start Excel and open the workbook.
            oExcel = CreateObject("Excel.Application")
            oExcel.Visible = True
            oBooks = oExcel.Workbooks
            oBook = oBooks.Open("c:\book1.xls")
    
            'Run the macro with parameter.
            oExcel.Run("AddLogo", "C:\Logo.jpg")
    
            'Clean-up: Close the workbook and quit Excel.
            oBook.Close (False)
            System.Runtime.InteropServices.Marshal.ReleaseComObject (oBook)
            oBook = Nothing
            System.Runtime.InteropServices.Marshal.ReleaseComObject (oBooks)
            oBooks = Nothing
            oExcel.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject (oExcel)
            oExcel = Nothing
    
    End sub
    HTH
    i had tried vbcode it work fine. how to place image in excel cell (row, column)?

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,438

    Re: [RESOLVED] How to Import A Picture Into An Excel Worksheet Using VB.net

    i do not believe you can insert an image into a cell, but you can position it over a specific cell, by setting top,left, height and width to match that of the cell
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  11. #11
    New Member himsha's Avatar
    Join Date
    Dec 2012
    Location
    India
    Posts
    7

    Re: [RESOLVED] How to Import A Picture Into An Excel Worksheet Using VB.net

    Hi,
    Thanks for your reply.
    is any other option available either in c# or other language? as i need to generate excel sheet with images and thereby user define image category. if i place image by position its hard to find the images categories.

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,438

    Re: [RESOLVED] How to Import A Picture Into An Excel Worksheet Using VB.net

    can you manually, in excel, insert the image into a cell as desired, if so record a macro, then post the recorded sample code here, some one will try to help with translating it to .net or whatever
    though i never could really figure why the original solution was to call a vba macro from .net, but if it ain't broke don't fix it

    if you can not make it work as required as an excel user, i doubt any code can do it either
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width