none
Selection.CopyPicture Issue Excel 2003 RRS feed

  • Question

  • Hi there,

    My first time in the forum hopefully some help will be found..

    I have created a small code to generate overnight reports for my work but it seems that the code stops ahen trying to activate one of the workbooks. When stepping through the code using F8 the code works !!!

    this is the code..

     Workbooks.Open Filename:= _
            ThisWorkbook.Path & "\Historical Data.xls"
        Sheets("Data").Select
        Calculate
        Sheets("YTD").Select
        ActiveSheet.Shapes("Group 24").Selection
        Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
        Windows("Daily APC Report_" & Format(Date, "yyyymmdd") & ".xls").Activate <--- stops here!!!
        Sheets.Add
        ActiveSheet.Name = "YTD"
        ActiveWindow.DisplayHeadings = False
        ActiveSheet.Move After:=Sheets("Summary")
        ActiveWindow.DisplayGridlines = False
        ActiveSheet.Paste
        ActiveWindow.Zoom = 40
    I would very much appreciate any input on this rather strange problem..

    Wednesday, November 16, 2011 1:09 PM

Answers

  • One is named XLStart, and is created as part of the installation. It is used to store, among others, your persnal macro file. You can search for XLStart and find it.

    The other is an optional folder that you can specify, but by default is not used. If you don't know about XLStart, it is unlikely that you set up the extra folder.

     


    HTH, Bernie
    Wednesday, November 30, 2011 1:49 PM

All replies

  • The workbook activation worked for me, but this should have failed:

        ActiveSheet.Shapes("Group 24").Selection

    It should be

        ActiveSheet.Shapes("Group 24").Select


    HTH, Bernie
    Wednesday, November 16, 2011 2:51 PM
  • Hi Bernie,

    Thank for your reply. You are right the code stops there as well but that was just a typo when I entered the code in the forum. 

    Do you think that this could be a memory or cache issue? It is very odd how the code executes fine when stepping through it manually.

    I am getting a:

    Run-time error '5':

    Invalid procedure call or argument

    Thank you.


    achilleas
    Thursday, November 17, 2011 6:07 AM
  • I don't know why the activate doesn't work, but then I never (or extremely rarely) activate a window in code. Here's how I would have written the code that you posted: I assumed the code was in a third un-named control workbook, Daily APC Report_20111117.xls is already open, and Historical Data.xls is closed:

    Sub TestMacro()
        Dim WkBk1 As Workbook
        Dim WkBk2 As Workbook
        Dim WkSht As Worksheet
       
        Set WkBk1 = Workbooks("Daily APC Report_" & Format(Date, "yyyymmdd") & ".xls")
        Set WkSht = WkBk1.Sheets.Add(After:=WkBk1.Sheets("Summary"))
        WkSht.Name = "YTD"

        Set WkBk2 = Workbooks.Open(ThisWorkbook.Path & "\Historical Data.xls")

        WkBk2.Sheets("Data").Calculate
        WkBk2.Sheets("YTD").Shapes("Group 24").CopyPicture Appearance:=xlScreen, Format:=xlPicture
        WkSht.Paste

    End Sub

     


    HTH, Bernie
    Thursday, November 17, 2011 2:15 PM
  • Bernie,

    Thank you for the quick reply. The code still seems to stop at the last statement of your code

    WkSht.Paste

    Any ideas?


    achilleas
    Wednesday, November 23, 2011 7:00 AM
  • Is your workbook or worksheet protected, at any level?
    HTH, Bernie
    Wednesday, November 23, 2011 2:12 PM
  • No it is not protected. This is the part of the code as it has been added.

     

        Dim WkBk1 As Workbook
        Dim WkBk2 As Workbook
        Dim WkSht1 As Worksheet
        Dim WkSht2 As Worksheet
        
        Set WkBk1 = Workbooks("Daily APC Report_" & Format(Date, "yyyymmdd") & ".xls")
        Set WkSht1 = WkBk1.Sheets.Add(After:=WkBk1.Sheets("Summary"))
        WkSht1.Name = "YTD"
        Set WkSht2 = WkBk1.Sheets.Add(After:=WkBk1.Sheets("YTD"))
        WkSht2.Name = "Monthly"
    
        Set WkBk2 = Workbooks.Open(ThisWorkbook.Path & "\Historical Data.xls")
    
        WkBk2.Sheets("Data").Calculate
        WkBk2.Sheets("YTD").Shapes("Group 24").CopyPicture Appearance:=xlScreen, Format:=xlPicture
        WkSht1.Paste <--- stops here
        ActiveWindow.DisplayHeadings = False
        ActiveWindow.DisplayGridlines = False
        ActiveWindow.Zoom = 40
        WkBk2.Sheets("MONTH").Shapes("Group 30").CopyPicture Appearance:=xlScreen, Format:=xlPicture
        WkSht2.Paste
        ActiveWindow.DisplayHeadings = False
        ActiveWindow.DisplayGridlines = False
        ActiveWindow.Zoom = 40
    Thanks.


    achilleas
    Wednesday, November 23, 2011 2:31 PM
  • For me, it blows right through that line, but it stops on the line

    WkBk2.Sheets("MONTH").

    since I done't have a sheet MONTH in that workbook.

    What version of Excel are you running? (I have been using XL2003 since the extensions in your code are .xls.) Is there any event code in any of the sheet or workbook object codemodules?


    HTH, Bernie
    Wednesday, November 23, 2011 3:32 PM
  • Yes, I am using XL2003.

    I do not have any other code in that specific workbook..!?!


    achilleas
    Tuesday, November 29, 2011 6:30 AM
  • What is rather strange is that although the code stops at WkSht1.Paste the pasting operation has been completed as far as I can see.
    achilleas
    Tuesday, November 29, 2011 6:36 AM
  • I found this that may apply:

    Usually caused by some third-part add-in and personal.xls.

    However, please check the XLStart-folder if there exist any files that You´re not aware of.

    Check also if there are missing / broken reference.

    - Open XL and push the end-button.
    - Switch to VB-Editor using ALT+F11.
    - Make sure that Book1.xls is selected in the Project Window.
    - Select the command Tools | Reference and look for if some selected post has the name "ISMISSING". If so, unselect the reference and restart XL.


    HTH, Bernie
    Tuesday, November 29, 2011 4:23 PM
  • Which is the XLStart-folder?

    Everything else seems to be fine..

    Thanks.


    achilleas
    Wednesday, November 30, 2011 7:02 AM
  • One is named XLStart, and is created as part of the installation. It is used to store, among others, your persnal macro file. You can search for XLStart and find it.

    The other is an optional folder that you can specify, but by default is not used. If you don't know about XLStart, it is unlikely that you set up the extra folder.

     


    HTH, Bernie
    Wednesday, November 30, 2011 1:49 PM