Replies

Maudibe,

Thank you for your suggestion but it would be hard to implement in practice. I have a library of macro language facilities thousands of lines long written over many years that support very many and diverse applications. Where necessary kind people have enhanced that with Visual Basic functions. That would be very hard to change.

I’ve attached such an enhancement and have tested this to show it is possible to read and write to a macro sheet within Visual Basic. Perhaps this could be used to convert the Destination to be a range and thus write to that range. What I could do is to use this new facility in normal sheets that contain shapes (and that write to macro sheets) and use my current facility with macro sheets that contain shapes. I can control the macro sheet environment. The reason I have this problem is that a user sends me information that contains multiple images with names (of images) greater than 32 characters. These images are often megabytes in size! So he and I have a problem that I’m attempting to resolve!

Regards

Antediluvian

Dear Maudibe,

Thank you for your kind words, and generous encouragement.

I’ve found what appears to be a problem. XShapeList works fine for normal sheets but not for macro sheets, either as the Source or the Destination. I’ve attached a copy of my test system, the original XShapeList and the test procedures, all of which are also contained in the test system. I found even doing this a bit of a struggle! I know that Visual Basic can write to macro sheets. Perhaps converting Destination to a reference might ease the problem and enable a solution. I would very much appreciate your help on this one. It would be rather tricky for me not to be able to write to macro sheets, i.e. I would very much like to be able to have Destination on macro sheets. At a pinch I could live with shapes being unable to be listed when those Shapes are on macro sheets as I have more control over the names of shapes in that environment, and could list them using other techniques.

Kind regards

Antediluvian

My thanks to both of you. I’ve converted your work into a function, the code for which is attached:

XShapeList(Sheet,Target) eg XShapeList(“[myBook.xls]Sheet5″,”[Book7]Sheet1!C74”)

XShapeList returns either the number of shapes in the list or FALSE if any error occurs. It also generates the list of shapes in Sheet and puts that vertical list at the cell starting at Target. Note that the number of shapes is NOT included in the list itself. I need the generality as I don’t know in advance where Sheet or Target will be.

Would the code be more elegant if Target was a reference rather than text?

Again, thank you.

Regards

Dear Maudibe,

That looks great to me, and thank you very much. It worked for me. I obviously exaggerated my VB skills, as most of your code I could not have done myself. That hopefully will give me the opportunity to turn it into a function, and to ensure it reports failures as FALSE.

Regards

Dear RetiredGeek,

Thank you for your input. Unfortunately it does not work for me at this stage. I seek your help as my VB skills are not up to this. I’ve attached my test system. Would it be possible to have the worksheet identified as [book]sheet ? If not I can adjust it externally. I also wish to remove the open statement as the workbook will always be open, and I do not wish to close the file. Therefore the path is not needed. Could the Destination be a cell anywhere in an open workbook please. (If it would be better for you, having the Destination as a single cell Range would be fine.)

I feel I probably have the skills to convert your work into a function and to ensure that it can only fail via a FALSE return.

Regards

Excellent, Thank you.

Maudibe,

Thank you very much for your excellent solution. It will smooth my use of the computer a lot. I’ve chosen the longer solution as it does not require changes to the Excel environment when used on different computers.

I’ve made some variations as in the attachment. I’ve eliminated excess separators when cells are empty and the function replies TRUE on success and FALSE on failure (though FALSE does not work when I deliberately feed in invalid arguments).

Thanks to both of you for your help. I think the basis of the code is there, especially the shorter version. However I was hoping to get a function that could be initiated from my code (I use the macro language, but that’s similar to Visual Basic for this context), not as a keyboard initiated macro. I’m also keen to put the result onto the clipboard as that will offer me a lot of advantages as I move from application to application and would value the permanancy of the clipboard as calculated in Excel. I’ve found a good references on the internet as to how to put the text onto the clipboard. It looks complex to me and I hope you’re able to do it. The major reference is:

http://excel-macro.tutorialhorizon.com/vba-excel-putting-text-in-the-windows-clipboard/

and it refers to: http://excel-macro.tutorialhorizon.com/vba-excel-reference-libraries-in-excel-workbook/

I would be extremely grateful if you could integrate the various aspects. A further benefit would be for the function to reply with TRUE or FALSE, depending on errors, though I know how to do that myself.

I’ve put two attachments in. The Word document is essentially the major reference above and the Excel woksheet shows a Help panel I’ll construct in code that I provide myself.

Barry,

Thank you. Is there a generic way to express an “array enter” formula as a formula? The reason I need this is that I do a lot of coding using the old macro language and thus need the formulae, not the array enter equivalent.

Regards

Andrew,

Thank you. I’ll put this on my to do list.

Geoffrey

Andrew,

Thank you for your references. I’ve spent some time looking at them and I think they’re beyond my skill level to make the necessary extrapolations. It’s great news though that Word can be controlled from Excel. I see that you live in Melbourne, as do I. Perhaps we don’t live far apart!

Regards

Geoffrey

Steve,

Many thanks for sticking with the problem. The code looks so elegant, it works, it’s really useful for me, and I’ve learnt a lot.

Geoffrey

Steve,

I should have explained that my VBA skills are very limited, perhaps the reason why I appear not to be as precise as you’d like. I’ve attempted to write a VBA function and I’m afraid it doesn’t work. I’ve copied it below. I need a function (XCellHyperlinkGet) that receives an input argument of the cell address I want as text. XCellHyperlinkGet would then return the hyperlink as text. XCellHyperlinkGet could be called from a normal spreadsheet or from a macro sheet. If XCellHyperlinkGet fails, FALSE as a Boolean would be returned.

I couldn’t find Hyperlinks in VBA Help.

I hope this gives you a better view.

Regards

Geoffrey

Function XCellHyperlinkGet(myCell As String)

‘ Returns Hyperlink contained in Cell
‘ I want myCell to be the cell with the URL
‘ to be returned
‘ use of ActiveCell below is wrong I think
‘ If the macros fails I would like FALSE returned
‘ FALSE as in Excel

On Error GoTo MyFail
sLink = ActiveCell.Hyperlinks(1).Address
XCellHyperlinkGet = sLink
Exit Function
MyFail:
XCellHyperlinkGet = “False”

End Function

Steve,

Thank you for your help so far. I’ve attached a sample spreadsheet of what I hope to achieve. In essence I need to extract by program the URLs in a large number of cells. The URLs are already stored in the cells. Because there are so many URLs (one to a cell as a hyperlink) I need to be able to open those I select and thus need a loop in the program. The only part I cannot do is the extract of the URL address in the hyperlink. I believe this will need a brief VBA function.

The attached spreadsheet is only shown to give a context of the problem I wish to solve. It has two sheets: one is the macro sheet, and the other is the data sheet

regards

Geoffrey

Rory, Excellent. Worked like a charm. Thank you. Geoffrey