This is a part of what may be a continuing series.
Excel is a rather powerful spreadsheet program that mostly works as expected.
However, there are some operations that have never been fixed and others that have been removed (IE: crippled).
Charts are in fact not copied, what pretends to be a copy is actually smaller.
So if you need a number of charts for presentation, it would be nice if they all were the same size.
Therefore, do NOT "copy" from #1 to #2, then "copy" from #2 to #3, etc; you will get a shrinking set of charts.
First make a master chart suitably larger than needed and use "copy" to generate viewing chart #1 and add in specifics for #1. Then use "copy" from the master to generate viewing chart #2 and add in specifics for #2.
Bingo, ALL viewing charts are exactly the same size.
Say you want to make a tic-tac-toe or similar checkerboard-like display.
The code might look something like:
ActiveSheet.Shapes("Picture 6").Select Selection.Copy 'vertical jumper ActiveSheet.Paste Selection.ShapeRange.IncrementLeft -50 Selection.ShapeRange.IncrementTop 235
A few comments are in order here: as far as I can tell, the "Selection.Copy" does not copy.
It is the "ActiveSheet.Paste" that actually creates a "copy" of the current object, and it is NOT a true copy.
The new object is offset horizontally and vertically from the original.
It seems this offset is related to the size of the object.
Worse yet, the movement scale factor looks different between negative increments and positive increments.
Just try to "reset" the position of an object thus:
' This code snippet is from a working program that visually places jumpers ' by selecting an object, copying it and the placing it at a desired position. ActiveSheet.Shapes("Picture 6").Select ' see object #1 selected Selection.Copy ' see NO change ActiveSheet.Paste ' see OFFSET copy of object Selection.ShapeRange.IncrementLeft 163.1 ' (relative) move it to right Selection.ShapeRange.IncrementTop 559.1 ' then move it down ' Now try to move it back.. Selection.ShapeRange.IncrementLeft -163.1 Selection.ShapeRange.IncrementTop -559.1 ' Visually note the object is NOT back where original began; it is OFFSET ' Technically, this moved object is exactly where the offset "copy" was found.
Be advised that the movements required are determined by repetitive trial and error.
One cannot calculate the movements due to the offset which seems to be related to the size of the object.
So in effect one is faced with unpredictable movement amounts.
Ain't Excel wonderful?
Say you want/need to start with an object and later need to remove it:
' delete J7 and attempt to find it (Excel 2003) shState = ActiveSheet.Shapes(6).Select ' shState after execution is "empty" Selection.ShapeRange.Delete ' visibly gone shState = ActiveSheet.Shapes(6).Select ' see no change.;
Well that is fine and dandy..it is gone and cannot be restored. Not useful.
So, try making it invisible:
shState = ActiveSheet.Shapes(6).Select ' shState after execution is "empty" Selection.ShapeRange.Visible = False ' visibly gone shState = ActiveSheet.Shapes(6).Select ' Run time error. ' Method 'Select' of object 'Shape' failed
Well this is worse! So try handy dandy object remover:
num = 1 For Each iobj In ActiveSheet.Shapes 'DrawingObjects iobj.Select If num > 3 Then ' want to keep construction objects iobj.Delete End If num = num + 1 Next iobj
Well this is worse yet! Get the same error (object #6, the NOT visible one).
That means it is so NOT visible that it cannot be selected. However, if one ignores that ugly fact, it CAN be deleted.
Ain't Excel wonderful?
Again, from working code (Excel 2003):
'Initially by hand, put jumpers AKA Shapes(5,,12) in proper places ' to allow jumper removal by making them not visible but STILL accessible. Dim posJUMP(12) ' save positions of jumpers For iJUMP = 5 To 12 ActiveSheet.Shapes(iJUMP).Select posJUMP(iJUMP) = Selection.ShapeRange.Left Next iJUMP 'sample of trick.. If (Not J1) Then ActiveSheet.Shapes(5).Select Selection.ShapeRange.Left = -400 'off screen, therefore not visible but accessible End If '** ReSetJumpers: 'Restore jumper positions for next try For iJUMP = 5 To 12 ActiveSheet.Shapes(iJUMP).Select Selection.ShapeRange.Left = posJUMP(iJUMP) Next iJUMP
This seems a perfect solution. Hand copy and placement can be as accurate as one wants.
Note vertical position is left untouched.
Printing to a printer other than the default may be, in effect impossible.
That all depends on the version of Excel one is using. Excel 2003 works in this regard.
But Excel in Office 2010 has been crippled.
In fact, for Excel in Office 2010, printing seems impossible unless one can open the printer as a file, which may be a problem (untested).
Again working code, assuming Excel 2003 or similar:
vNam = Cells(2, 7).Text + "V" 'board voltage pPath = Cells(2, 10) DLC = Cells(4, 6) 'Date Lot Code SerNum = Cells(5, 6) 'Board serial number DaTm = Date$ + " " + Time$ 'Present date and time Open pPath + "Log.TXT" For Append As #1 eRv = Cells(3, 7) 'programming error voltage dp = InStr(eRv, "."): If (dp > 0) Then eRv = Left$(eRv, dp + 2) Print #1, DLC + " " + SerNum + " " + DaTm + " to " + vNam; _ " error ="; Print #1, eRv Close '''''''''''''''''''''''''''''''''''''''''''''''''''''' ' In Excel 2010, all of the following is moot due to faults in Excel. ' Therefore, quit this while ahead. Stop 'Now is the time to view results, then press F5 key GoTo barf ActivePrinter = "Acrobat PDFWriter on FILE:" ' Above sets printer in Excel 2003; will crash in Excel 2010: ' Run-time error '1004': ' Method 'ActivePrinter' of object '_Global' failed ' In "modern" defective Excel apps, following attempts to print to ' default printer as a file; NOT in PDF format. Fails. ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=False, _ ActivePrinter:="Acrobat PDFWriter on FILE:", PrintToFile:=True, _ Collate:=False, PrToFilename:=pPath + vNam ' Since it fails in Excel 2010, the files do not exist and the Kill fails. ' The PrToFilename assignment is not recognized, and there is no error message. Kill pPath + vNam 'only PDF left barf: Close
Ain't the new, crippled Excel wonderful?