Posted to microsoft.public.excel.misc
|
|
Stripping out text?
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
|