I have entries in col G, formatted as General, containing text like the
following. (They represent the sizes of files. Only GB or MB. Always
three digits.)

Col G (text)
-------
3.40 GB
3.82 GB
22.8 MB


I want to place a formula or function or macro in col H which converts
col G to GB. (So that I can subsequently do addition on selected cells,
working only in GB.)

Col H (GB, numbers to 3 dp)
------
3.400
3.820
0.023

Struggling here, particularly with stripping out the GB or MP suffices,
so would much appreciate some help please.

Terry, East Grinstead, UK

I have entries in col G, formatted as General, containing text like the
following. (They represent the sizes of files. Only GB or MB. Always
three digits.)

Col G (text)
-------
3.40 GB
3.82 GB
22.8 MB


I want to place a formula or function or macro in col H which converts
col G to GB. (So that I can subsequently do addition on selected cells,
working only in GB.)

Col H (GB, numbers to 3 dp)
------
3.400
3.820
0.023

Struggling here, particularly with stripping out the GB or MP suffices,
so would much appreciate some help please.

Terry, East Grinstead, UK

How about using a helper col (set to display to 3 dec places) that converts the
numeric value conditional on IF the RIGHT 2 chars are "GB" then extract the
LEFT 4 chars, ELSE divide the LEFT 4 chars by 10K...

=IF(RIGHT($A1,2)="GB",LEFT($A1,4)/1,LEFT($A1,4)/1000)

--
Garry

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

Of course.., you'll need to change the $A1 ref to suit your sheet layout:

Assuming header row
=IF(RIGHT($G2,2)="GB",LEFT($G2,4)/1,LEFT($G2,4)/1000)

--
Garry

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

GS wrote:

Of course.., you'll need to change the $A1 ref to suit your sheet layout:

Assuming header row
=IF(RIGHT($G2,2)="GB",LEFT($G2,4)/1,LEFT($G2,4)/1000)

Brilliant, thanks Garry, works a treat!

I'm wondering if it would be possible to avoid that 'helper' column?

https://www.dropbox.com/s/guvjbe1kg8...ula-1.jpg?dl=0

Or even go further and edit the VBA code (which I kindly received from
an Excel web forum) to deliver the output in that numerical format
directly? That's my preference after realising that I would sometimes
need to SUM various cells. To complete the picture, here's the code:

Public Function GetFileSize(rngcell As Range) As String
Const PROP_FILE As Long = 1
flAddress = rngcell.Hyperlinks(1).Address

With CreateObject("scripting.FileSystemobject")
pathName = .GetParentFolderName(flAddress)
flName = .GetFileName(flAddress)
End With

With CreateObject("Shell.Application").Namespace(pathNa me)
flSize = .GetDetailsOf(.Items.Item(flName), PROP_FILE)
End With
GetFileSize = flSize
End Function

Thanks again for the fast reply.

Terry, East Grinstead, UK

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

--
Garry

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

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

Hi Terry,

Am Thu, 14 Dec 2017 09:33:14 +0000 schrieb Terry Pinnell:

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

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

try the following code in a new workbook. It will write the hyperlinks
for all files in the folder and subfolders of your path in column F and
the size of the files in GB in column G.

Sub Test()
Dim FSO As Object
Dim objFolder As Object, objSubFolder As Object, objFile As Object
Dim MyPath As String
Dim i As Long

Set FSO = CreateObject("scripting.fileSystemobject")
MyPath = Range("A1").Value
Set objFolder = FSO.getfolder(MyPath)

i = 2
For Each objFile In objFolder.Files
ActiveSheet.Hyperlinks.Add _
anchor:=Cells(i, "F"), _
Address:="file:///" & MyPath & objFile.Name, _
TextToDisplay:=objFile.Name
Cells(i, "G") = Format(objFile.Size / 1000000000, "0.000") & " GB"
i = i + 1
Next
For Each objSubFolder In objFolder.subfolders
For Each objFile In objSubFolder.Files
ActiveSheet.Hyperlinks.Add _
anchor:=Cells(i, "F"), _
Address:="file:///" & MyPath & objFile.Name, _
TextToDisplay:=objFile.Name
Cells(i, "G") = Format(objFile.Size / 1000000000, "0.000") & " GB"
i = i + 1
Next
Next
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Hi Terry,

Am Thu, 14 Dec 2017 14:02:42 +0100 schrieb Claus Busch:

Sub Test()
Dim FSO As Object
Dim objFolder As Object, objSubFolder As Object, objFile As Object
Dim MyPath As String
Dim i As Long

sorry, the folder and subfolder names are missing into the code.
Try:

Sub Test()
Dim FSO As Object
Dim objFolder As Object, objSubFolder As Object, objFile As Object
Dim MyPath As String
Dim i As Long

Set FSO = CreateObject("scripting.fileSystemobject")
MyPath = Range("A1").Value
Set objFolder = FSO.getfolder(MyPath)

i = 2
For Each objFile In objFolder.Files
ActiveSheet.Hyperlinks.Add _
anchor:=Cells(i, "F"), _
Address:="file:///" & MyPath & objFolder.Name & _
"\" & objFile.Name, _
TextToDisplay:=objFile.Name
Cells(i, "G") = Format(objFile.Size / 1024 ^ 3, "0.000") & " GB"
i = i + 1
Next
On Error Resume Next
For Each objSubFolder In objFolder.subfolders
For Each objFile In objSubFolder.Files
ActiveSheet.Hyperlinks.Add _
anchor:=Cells(i, "F"), _
Address:="file:///" & MyPath & objSubFolder.Name & _
"\" & objFile.Name, _
TextToDisplay:=objFile.Name
Cells(i, "G") = Format(objFile.Size / 1024 ^ 3, "0.000") & " GB"
i = i + 1
Next
Next
End Sub


Regards
Claus B.
--
Windows10
Office 2016

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

--
Garry

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

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

Claus Busch wrote:

Hi Terry,

Am Thu, 14 Dec 2017 14:02:42 +0100 schrieb Claus Busch:

Sub Test()
Dim FSO As Object
Dim objFolder As Object, objSubFolder As Object, objFile As Object
Dim MyPath As String
Dim i As Long

sorry, the folder and subfolder names are missing into the code.
Try:

Sub Test()
Dim FSO As Object
Dim objFolder As Object, objSubFolder As Object, objFile As Object
Dim MyPath As String
Dim i As Long

Set FSO = CreateObject("scripting.fileSystemobject")
MyPath = Range("A1").Value
Set objFolder = FSO.getfolder(MyPath)

i = 2
For Each objFile In objFolder.Files
ActiveSheet.Hyperlinks.Add _
anchor:=Cells(i, "F"), _
Address:="file:///" & MyPath & objFolder.Name & _
"\" & objFile.Name, _
TextToDisplay:=objFile.Name
Cells(i, "G") = Format(objFile.Size / 1024 ^ 3, "0.000") & " GB"
i = i + 1
Next
On Error Resume Next
For Each objSubFolder In objFolder.subfolders
For Each objFile In objSubFolder.Files
ActiveSheet.Hyperlinks.Add _
anchor:=Cells(i, "F"), _
Address:="file:///" & MyPath & objSubFolder.Name & _
"\" & objFile.Name, _
TextToDisplay:=objFile.Name
Cells(i, "G") = Format(objFile.Size / 1024 ^ 3, "0.000") & " GB"
i = i + 1
Next
Next
End Sub


Regards
Claus B.

Hi Claus,

Thanks for the code which I am trying to implement, even though I do now
have Garry's function fully working.

Don't forget I'm still in 'copy/paste and follow instructions mode'! So
I'm probably not applying your code properly.

By "try the following code in a new workbook" do you mean a copy? Or an
empty book? Or perhaps a new worksheet of my existing workbook?

I placed it in a fresh workbook containing just an extract of col F, as
shown he
https://www.dropbox.com/s/6ptgw0qjeq...cro-1.jpg?dl=0

When I ran your macro it gave the error

Run-time error '5':
Invalid procedure call or argument

The line highlighted by Debug was this:

Set objFolder = FSO.getfolder(MyPath)

I also tried it in a completely empty workbook, although I couldn't
imagine how that coud work with nothing to operate on?

Or should I have edited your code in some way before I used it?

Here's the code I pasted (renaming it was my only change).

Sub ClausGetFileSize()
Dim FSO As Object
Dim objFolder As Object, objSubFolder As Object, objFile As Object
Dim MyPath As String
Dim i As Long

Set FSO = CreateObject("scripting.fileSystemobject")
MyPath = Range("A1").Value
Set objFolder = FSO.getfolder(MyPath)

i = 2
For Each objFile In objFolder.Files
ActiveSheet.Hyperlinks.Add _
anchor:=Cells(i, "F"), _
Address:="file:///" & MyPath & objFolder.Name & _
"\" & objFile.Name, _
TextToDisplay:=objFile.Name
Cells(i, "G") = Format(objFile.Size / 1024 ^ 3, "0.000") & " GB"
i = i + 1
Next
On Error Resume Next
For Each objSubFolder In objFolder.subfolders
For Each objFile In objSubFolder.Files
ActiveSheet.Hyperlinks.Add _
anchor:=Cells(i, "F"), _
Address:="file:///" & MyPath & objSubFolder.Name & _
"\" & objFile.Name, _
TextToDisplay:=objFile.Name
Cells(i, "G") = Format(objFile.Size / 1024 ^ 3, "0.000") & " GB"
i = i + 1
Next
Next
End Sub

Terry, East Grinstead, UK

Hi Terry,

Am Fri, 15 Dec 2017 08:48:11 +0000 schrieb Terry Pinnell:

Run-time error '5':
Invalid procedure call or argument

The line highlighted by Debug was this:

Set objFolder = FSO.getfolder(MyPath)

you can try the code in an empty sheet also.
But make sure you have the path in A1 (e.g.: G:\myvideos\)
Regard the backslash at the end of the path.
With the macro you don't need file names or hyperlinks in column F
because the code writes new hyperlinks for all files in the path's
folder and subfolders and the size in column G.

If you only want the size of the existing files in column F use Garry's
code.


Regards
Claus B.
--
Windows10
Office 2016

Claus Busch wrote:

Hi Terry,

Am Fri, 15 Dec 2017 08:48:11 +0000 schrieb Terry Pinnell:

Run-time error '5':
Invalid procedure call or argument

The line highlighted by Debug was this:

Set objFolder = FSO.getfolder(MyPath)

you can try the code in an empty sheet also.
But make sure you have the path in A1 (e.g.: G:\myvideos\)
Regard the backslash at the end of the path.
With the macro you don't need file names or hyperlinks in column F
because the code writes new hyperlinks for all files in the path's
folder and subfolders and the size in column G.

If you only want the size of the existing files in column F use Garry's
code.


Regards
Claus B.

Oh, I see, thanks for clearing that up Claus. I totally misunderstood.

Yes, I do want to retain the structure of my large and quite complex
existing workbook, so I'll use Garry's code.
https://www.dropbox.com/s/slfzuoljpj...dex-1.jpg?dl=0

I click on the hyperlink col, F, to open that video. And col G shows me
its size, useful in various ways such as for deciding what uploads to
Vimeo I can make within my upload limit this week.

--------------------

BTW, digressing a bit, when I click the hyperlink I always get an
irritating warning message, It's quite redundant as these are videos
I've created myself, stored on my PC. I've been trying in vain to
discover how to get rid of it!
https://www.dropbox.com/s/utgmnyafff...ssage.jpg?dl=0

I've tried various registry hacks as well as the Trust Center settings
but still no success. So if anyone here has any solution...

This is Win 10 with Excel 365, so I'd be interested to know if others
get the same.

Yes, it's only one extra click, but it just seems so daft that I cannot
silence it!

Terry, East Grinstead, UK

Hi Terry,

Am Fri, 15 Dec 2017 10:01:28 +0000 schrieb Terry Pinnell:

BTW, digressing a bit, when I click the hyperlink I always get an
irritating warning message, It's quite redundant as these are videos
I've created myself, stored on my PC. I've been trying in vain to
discover how to get rid of it!
https://www.dropbox.com/s/utgmnyafff...ssage.jpg?dl=0

I've tried various registry hacks as well as the Trust Center settings
but still no success. So if anyone here has any solution...

This is Win 10 with Excel 365, so I'd be interested to know if others
get the same.

if you add the path of the videos to the Trusted Locations into the
Trust Center you don't get this message.You must also activate
"Subfolders of this location are also trusted"


Regards
Claus B.
--
Windows10
Office 2016

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

Claus Busch wrote:

Hi Terry,

Am Fri, 15 Dec 2017 10:01:28 +0000 schrieb Terry Pinnell:

BTW, digressing a bit, when I click the hyperlink I always get an
irritating warning message, It's quite redundant as these are videos
I've created myself, stored on my PC. I've been trying in vain to
discover how to get rid of it!
https://www.dropbox.com/s/utgmnyafff...ssage.jpg?dl=0

I've tried various registry hacks as well as the Trust Center settings
but still no success. So if anyone here has any solution...

This is Win 10 with Excel 365, so I'd be interested to know if others
get the same.

if you add the path of the videos to the Trusted Locations into the
Trust Center you don't get this message.You must also activate
"Subfolders of this location are also trusted"

Unfortunately that doesn't work here, Claus. One of first things I
tried.

https://www.dropbox.com/s/wqnx5mytrk...age-5.jpg?dl=0

Terry, East Grinstead, UK