GS wrote:

GS wrote:

You can modify the function as follows and use it in col G to return
numeric value only. (Set col precision to 3dp)

Try...

Public Function GetFileSize(CellRef As Range) As Double
Dim sPath$, sFldr$, sFile$, vFldr, vSize, v1
Const File_Size& = 1

sPath = CellRef.Value
With CreateObject("scripting.FileSystemobject")
sFldr = .GetParentFolderName(sPath)
sFile = .GetFileName(sPath)
End With

Set vFldr = CreateObject("Shell.Application").Namespace("" & sFldr & "")
If vFldr Is Nothing Then Exit Function

With vFldr
For Each v1 In .Items
If v1.Name = sFile Then vSize = .GetDetailsOf(v1, File_Size): Exit
For Next 'v1
End With

v1 = Split(vSize, " ")
GetFileSize = IIf(v1(1) = "GB", CDbl(v1(0)) / 1, CDbl(v1(0)) / 1000)
Set vFldr = Nothing
End Function

Thanks Garry, do appreciate your taking the time and trouble to work on
that. Unfortunately there appears to be something wrong. I've tested
carefully and think I see some pattern. My illustration will hopefully
help you to isolate the flaw:

https://www.dropbox.com/s/ma8cqc9qfp...ion-1.jpg?dl=0

Or
https://www.dropbox.com/s/ma8cqc9qfp...on-1.jpg?raw=1

The key point is that the original function works on the FULL hyperlink.
Yours seems to operate on the displayed text, which is typically just
the filename or subfolder(s) and filename. I tried adding an example
with the full link, and your edit does work OK on that.

BTW, the result was also formatted like the hyperlink in col F, namely
blue bold underlined.


Terry, East Grinstead, UK

Yeah, I used full path to the filename for testing. Try changing this line

sPath = CellRef.Value

to

sPath = CellRef.Hyperlinks(1).Address

Perfect, thanks so much Garry!

Terry, East Grinstead, UK

Glad to help! Sorry about not switching that line of code to ref the hyperlinks
before posting<g!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion