OT: Excel problem

This snippet of Excel code works

sPath = "G:\OIL4LESS\FUNDING\RESEARCH\" + StNam + ".gif" Sheets("Charts").Select 'added...else next fails and trashes Excel WSc.ChartObjects(Chart_Name).Activate ActiveChart.Export Filename:=sPath, FilterName:="GIF"

Want way to do equivalent of following

pPath = "G:\ANALOG\HVREG\Boards\TITANTWO\CodatronD" + vNam + ".gif" ActiveSheet.Shapes("Picture 1").Select ActiveSheet.Shapes("Picture 1").Activate ActiveSheet.Export Filename:=sPath, FilterName:="GIF"

Can find no way to activate selected shape. Can find no way to export selected shape.

Help?

** Also, i have made a number of shapes/pictures in the spreadsheet; how the heck can i find their names - alternately,how can i name them?

Thanks

Reply to
Robert Baer
Loading thread data ...

I do not do my best work this late... :)

Private Sub CommandButton1_Click()

Dim shp As Shape Dim x As String

For Each shp In ActiveSheet.Shapes x = shp.Name MsgBox x Next shp

Range("A1").Select End Sub

Reply to
mpm

Seems to work, but found to be not useful as a number of them have same names :(

What i have: An included PNG of a PC board (a "shape") that i want to place components ("shapes") according to VB script which now works fine. Script takes values from 2 cells, computes which shapes goes where, and copies proper shapes (call it a parts bin) onto the board (??digital solder??) at correct places. Have that area preset for print,so that i can file/print to a PDF or PS printer. Do not know how to, in VB, to give filename to printer (damn windows text box of some sort). Would be far easier to export to a file, like: ActiveSheet.Shapes.PrintArea.Select

Reply to
Robert Baer

Either the above (covert to shape) or print as a picture to a file.

Ideas? Thanks. PS: the resulting PCB layout documentation is looking half-way decent now.

Reply to
Robert Baer

You can with some effort trick the help system into answering the right question but you will have to be more specific about exactly which version of Excel you are trying to use as they all have slightly different and mutually incompatible VBA syntactic sugar. The worst discontinuity by far is when XL2007 arrived - the as shipped version was truly worse than useless with a huge number of race condition faults where in VBA axes would attempt to be defined before the graph structure had time to initialise. It was glacially slow as a result!

Certain versions like 2007 early patch levels are incredibly fragile. XL2003 was a global optimum and later patch levels of 2002 were OK too.

You have to ask it to show you what operators are available for a given object. The syntax directed editor will sometimes do this iff you use exactly the right constructs.

Microsoft definition of "Help" :

He - the masculine pronoun LP - obsolete black vinyl disk

Anything is possible but you will have to work at it. Your original problem to export all images from an XL spreadsheet is most simply accomplished by save as filtered HTML and then probe the images subdirectory. I have used a script based emthod to remove accumulated corporate metadata dross from legacy files that have grown to unbounded sizes after being edited on different XL versions in a corporate setting. The worst ones contained 5MB of useful data in a 100MB file!

Everything else was orphaned metadata caused by drag and drop images :(

The names are long gone - particularly if drag & drop was used. You can sometimes find regular reports with images done this way into the low hundreds and with all their orphaned ancestors lurking in the file too. It only happens badly on sites with hetrogeneous XL versions in use in different regions and on the same old file(s) but it is annoying.

--
Regards, 
Martin Brown
Reply to
Martin Brown

Am using Office 2000 now and have Office 2003 Student. Originally was using 2003 but HD crash and had to re-install everything. It demands to be "activated" and NONE of the online methods work (links dead) and NONE of the phone numbers work (dead one way or another). Even the "trick" hack "fix" given that runs umptie-ump somethings (forget, was AGES ago) fails. That is why i fell back to 2000 because it does not care.

Yeah, i ran into a bit of that non-compatibility - had used 2003 to make rather involved app that writes HTML, JavaScript, exports about 60 different charts each has own label DUAL axes, etc. Dies somewhere when run in 2000.

** Finally found way to have a complete arg for printer by a) use make new macro, capture select,print by hand; b) steal code and insert; c) put cursor on the ActiveWindow.SelectedSheets.PrintOut command and use Edit/Quick info to (FINALLY after tearing hair out and getting nowhere) get complete arg list; d) follow exactly syntax used on args already captured. Gack!

So i am almost done, but need a way to "convert" a text cell (made size and color what i need) to a "shape".

Digging into BuildFreeform mess, not very examples. Had to go online "help" and (natch) link was no good but M$ gave a new link. INCREDIBLY complicated, and here is where it would be *extremely* useful to have dictionary-type DEFINITIONS to WTF a lot of those terms mean.

All i need to do is "convert" that text cell to a shape! Ideas - or better yet a working example somewhere?

Thanks.

Reply to
Robert Baer

This snippet of Excel code works

''''''''''''''' Range("Z76").Select ' cell size and contents are what is desired Range("Z76").Activate X1 = 1207 'middle of text cell, pixels ("points") Y1 = 1365 'middle of text cell, pixels ("points") ' Below line works, but gives type Variant/Empty VarMT = ActiveSheet.Range("Z76") ' value = 400 (OK) ' Below runs no errors, BUT where is it, how do i get & look at it? With Worksheets(1).Shapes.BuildFreeform(msoEditingCorner, 1000, 1275) .AddNodes msoSegmentCurve, msoEditingAuto, X1, Y1 .ConvertToShape End With ' Try select that new shape... ' 'iobj.1 'for output display ' 'iobj.2 'vertical jumper ' 'iobj.3 'horizontal jumper ' so.. iobj.4 gotta be it, and sho nuff, tiz last one ActiveSheet.Shapes(4).Select ' yes; see it, but *not* anywhere like the cell contents or size ' It is a diagonal line, sloping down and size is a lot smaller ' than the cell diagonal; position greatly offset. Selection.ShapeRange.IncrementLeft 90.1 Selection.ShapeRange.IncrementTop 120.1 ' copied to new place, not where i want, but that can be fixed

x = x 'dummy for breakpoint stop ''''''''''''''' So, how do i get the CONTENTS?

Help?

Reply to
Robert Baer

You want to add a textbox and then tweak its properties. This captured from XL2012 should give you a basis, but beware that old versions of XL use different means of specifying the objects size and shape.

This version (and XL2007 on) uses XLeft, YTop, Width, Height ISTR Older versions use XLeft, YTop, XRight, YBottom

They are bound to have randomised some of the keywords too!

ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 144.75,

203.25, _ 69.75, 33.75).Select With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 5).Font.Fill .Visible = msoTrue .ForeColor.RGB = RGB(255, 0, 0) .Transparency = 0 .Solid End With With Selection.ShapeRange.Fill .Visible = msoTrue .ForeColor.ObjectThemeColor = msoThemeColorAccent2 .ForeColor.TintAndShade = 0 .ForeColor.Brightness = 0 .Transparency = 0 .Solid End With Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "Fred" & Chr(13) & "" Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 5).ParagraphFormat. _ FirstLineIndent = 0 With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 5).Font .NameComplexScript = "+mn-cs" .NameFarEast = "+mn-ea" .Fill.Visible = msoTrue .Fill.ForeColor.RGB = RGB(255, 0, 0) .Fill.Transparency = 0 .Fill.Solid .Size = 11 .Name = "+mn-lt" End With

You are in a maze of twisty little passages all alike. Exits are NSEW

--
Regards, 
Martin Brown
Reply to
Martin Brown

Thanks,will try that. Found a site that has code that actually gets text from an input box, and places it character by character "on" the spreadsheet (not in a cell AFAIK). It looked nice and useable. But did not work in Excel 2000. And .Font.Name "Arial" was ignored so could not use desired one. So...guess first thing is to re-try installing that 2003 version and see if it complains again re "activation".

Does M$ have loonie-bin rated schitz programers? No consistency reviews? Mandate to make as many changes as possible and still seem to be the same? Mandatory change so latest version can be said to be "latest" and have new hype for new suckers err.. buyers?

Reply to
Robert Baer

Cannot choose enable/disable macros - window ain't there; instead ger "help" message:

You may encounter this error for the following reasons:

Macro security is set to High and you selected Disable when prompted

*---cannot see level------^ ^----NO way to select

by the macro warning dialog when opening the file, or the macro was unsigned and was automatically disabled by the application

If the macro was not signed (no attached certificate of trust) and you have verified the source of the unsigned macro and believe you can trust it, you can enable the macro with the following procedure:

Select the Tools menu option and then select Macro and Security. In the resulting Security dialog, set the security level to Medium by clicking the Medium radio button.

*---------------^ Nope! Gotta use lowest.

Close the file and any other instances of the application currently running on the computer. Open the file again and click the Enable button when prompted to allow for the unsigned macro to run.

*---------- NO prompt at Medium ----------------------^^

If VBA is not installed, it is not possible to accept certificates of trust attached to VBA macros.

  • My question:

Only after i selected lowest security level, would it prompt to install VBA. Then closed Excel, re-opened my XLS app dir i get the Disable/Enable box. NO certs; how do i get a "valid", certified, digitally signed GP cert?

Reply to
Robert Baer

This snippet of Excel code works (ver 2003)

''''''''''''''' '*Do NOT need BuildFreeform

Reply to
Robert Baer

ElectronDepot website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.