Announcement

Collapse
No announcement yet.

Using COM to Insert Picture in Excel

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Kevin Brown
    replied
    Importing Pictures into Excel Cells

    Has anyone worked out how to get pictures into specific cells when using PB and excel ?


    I'm currently using the code posted by Jose to position pictures (icons) to specific x y coordinates in the excel form. If I control the cell heights then this is a reasonable solution that I can work with in the meantime.

    However I would like to assign the icons to the cell.

    I note that if I increase the height of a cell on the produced excel form this does not mess up the position of all the icons in the rows below. Excel must then have some recognition of the position of the icon relative to the cell that I've placed them in.

    However if I delete rows from the excel form then all the icons remain stacked one on top of the other.


    My code is below where I'm taking records from a listview including the icons on that listview and pumping them into excel. The code at the bottom is the VB macro that we all peek at to see how to do things in excel and is the focus of this question

    Code:
     
     
      'Part Number colum 3 > 3
      IF EXCC& = 3 THEN LISTVIEW GET TEXT S2DLG, %IDC_LISTVIEW2, EXCR&, EXCC& TO TMP$ _
     :LET vX = 3:  LET vY = EXCR&+10 : LET vText1 = TMP$ _
     :OBJECT LET oExcelWorkSheet.Cells.Item(vY, vX) = vText1
         ' EXCC&  Excel current column taken from listview2
         ' EXCR&  Excel current row taken from listview2
          ' Obtain icon file number from listview  to TMP$
          ' Obtain icon file name from Array IcA and create a file path for the icon.  VFile
         ' Add icon at coordinates  X Y to be inside the target cell
          ' e.g  40 from left, 145 + (EXCR&*60) from top
     
       IF EXCC& = 3 THEN LISTVIEW GET TEXT S2DLG, %IDC_LISTVIEW2, EXCR&, 46 TO TMP$ _
       :LET vFile =  DRV$ + "Peprog\EstPBasic\" + TRIM$(IcA( VAL(TMP$) )) _
       :LET vLink = 1 : LET vSave = 1 _
       :LET vLeft = 40 : LET vTop  = 145 + (EXCR&*60) _
       :LET vWidth = 16 :  LET vHeight = 16 _
       :OBJECT CALL oExcelWorkSheet.Shapes.AddPicture(vFile, vLink, vSave, vLeft, vTop, vWidth, vHeight)
     
     
     
          '   A VB Macro
          '    Sub Macro1()
          '    ' Macro1 Macro
          '    Range("E6").Select
          '    ActiveSheet.Pictures.Insert("C:\Peprog\EstPBasic\Elec 3CoreCable22.ico").Select
          '    End Sub

    Leave a comment:


  • Michael Mattias
    replied
    Code:
    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 6/6/2011 by Michael Mattias
    '
    
    '
        Range("F1").Select
        ActiveSheet.Pictures.Insert( _
            "C:\Documents and Settings\Michael\My Documents\My Pictures\ANSI_STANDARDS.bmp" _
            ).Select
        Application.CommandBars("Stop Recording").Visible = False
    End Sub
    (Insert, picture, from file, select, <insert>. Then stop recording but I think you guessed that already.)

    MCM

    Leave a comment:


  • Kevin Brown
    replied
    images into excel

    I found this to work when adding images/pictures into the headers of excel.

    LET vVnt1 = "&G" along with the corresponding OBJECT LET line seems to be crucial to make this work.

    I don't know what the "&G" does but the image is not put into excel without it. I'll add this into a file I'll put into source code soon.


    Code:
     
     
    'Add image to centre header
        LET vFile = "C:\PBWin90\Objects\pbsig.jpg"
        LET vVnt1 = "&G"
        OBJECT LET oExcelWorkSheet.PageSetup.CenterHeader = vVnt1
        OBJECT LET oExcelWorkSheet.PageSetup.CenterHeaderPicture.Filename = vFile
    Last edited by Kevin Brown; 6 Jun 2011, 04:27 PM. Reason: grammar correction

    Leave a comment:


  • StanHelton
    replied
    Originally posted by Calvin H. Chipman View Post
    Stan,

    Thanks for the general information. The link you provided points to general information about COM, but still doesn't answer the specific question.

    I'm very familiar with how COM works. I wrote the PowerBASIC COM Browser and the original sample programs.

    My specific question was concerning something that doesn't work normally. I'm sorry I wasn't more clear in my request.

    Thanks,
    Calvin
    Wow, was that a serious faux pas on my part! Sorry. In the army we all wore medals and stuff so you could sort-of tell who needed help and who was out of your own league. Guess I'm not used to this civilian thing quite yet.

    Stan

    Leave a comment:


  • Calvin H. Chipman
    replied
    Stan,

    Thanks for the general information. The link you provided points to general information about COM, but still doesn't answer the specific question.

    I'm very familiar with how COM works. I wrote the PowerBASIC COM Browser and the original sample programs.

    My specific question was concerning something that doesn't work normally. I'm sorry I wasn't more clear in my request.

    Thanks,
    Calvin

    Leave a comment:


  • StanHelton
    replied
    Originally posted by Calvin H. Chipman View Post
    Stan,

    The link you provided appears to be completely unrelated to the topic. Is there a mistake in the link?

    I don't have the manual, so that's no help.

    Thanks,
    Calvin

    Sorry about that Calvin. I was working on a COM project and it just seemed to me that it might help you get what you wanted if you used the COM interface in your PB app. I will crawl back into my little cabin and be quiet for awhile now.

    The link should have been:
    http://www.powerbasic.com/support/pb...ad.php?t=37352

    The same example is in the online help if you want to look. And there are some COM samples in the /samples directory for Word and Excel.

    Stan
    Last edited by StanHelton; 14 May 2008, 11:00 AM. Reason: clarification

    Leave a comment:


  • Calvin H. Chipman
    replied
    Jose,

    That's what should work, but it doesn't. PB gives a syntax error on "Insert".

    Something is missing, but I don't know what it is. Perhaps it's one of those default interface members.

    This is what I did get to work:

    Code:
        vFile = sFile
        vLink = 1
        vSave = 1
        vLeft = 0
        vTop  = 50
        vWidth = 32
        vHeight = 32
        Object Call oExcelWorkSheet.Shapes.AddPicture(vFile, vLink, vSave, vLeft, vTop, vWidth, vHeight)
    This is a different way of accomplishing the goal. I'd still like to know the solution to the problem, if anyone knows the answer.

    Thanks,
    Calvin
    Last edited by Calvin H. Chipman; 14 May 2008, 08:19 AM.

    Leave a comment:


  • José Roca
    replied
    Code:
    DIM vRange AS VARIANT
    DIM vFile AS VARIANT
    
    vRange = "A4"
    vFile = sFile
    OBJECT CALL Range(vRange).Select
    OBJECT CALL ActiveSheet.Pictures.Insert(vFile)

    Leave a comment:


  • Cliff Nichols
    replied
    Calvin,
    I am sure either stan replied to the wrong post, (or the web goofed).

    If you can post the VB Script, I am sure there are enough of us "VB-Refugees" around here that can help you out.

    Leave a comment:


  • Calvin H. Chipman
    replied
    Stan,

    The link you provided appears to be completely unrelated to the topic. Is there a mistake in the link?

    I don't have the manual, so that's no help.

    Thanks,
    Calvin

    Leave a comment:


  • StanHelton
    replied
    You might find this thread helpful.
    (link removed because it was the wrong one! see my next post, please)

    Take a look at the manual page 53-4 for a simple text example.

    Stan
    Last edited by StanHelton; 14 May 2008, 10:59 AM. Reason: remove erroneous link

    Leave a comment:


  • Calvin H. Chipman
    started a topic Using COM to Insert Picture in Excel

    Using COM to Insert Picture in Excel

    Does anyone know how to duplicate the following VB code in PB?

    Code:
        Range("A4").Select
        ActiveSheet.Pictures.Insert(sFile)
    Thanks,
    Calvin
Working...
X