Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
File count excluding those which are hidden
Hi all, I am using excel 2007 and I got an excel file call "Test File"
in folder "My Document". When I open "Test File" I can see another file appear in same folder with same name the only difference is that it got "~$" in the beginning of its name like "~$Test File". I know these are hidden files and only appear when you open file and I also know that if you dont want to see them then unselect "Show hidden file and folders" option under "Tools+FolderOption+View". These files hidden attribute is alwasy selected by default. Ok now i come to my problem. I got macro (see below) which count files in folder "My Document". Private Sub CommandButton1_Click() Dim foldername As String Dim FSO As Object Dim fldr As Object Dim file As Object Dim cnt As Long foldername = "C:\My Document" Set FSO = CreateObject("Scripting.FilesystemObject") Set fldr = FSO.GetFolder(foldername) For Each file In fldr.Files If file.Type Like "*Microsoft Office Excel*" Then cnt = cnt + 1 End If Next file Set file = Nothing Set fldr = Nothing Set FSO = Nothing Range("A1").Value = cnt End Sub Above macro count the files in folder "My Document" and put the result in cell "A1". The problem is that if file in "My Document" folder is open then macro brings the result "2" in cell "A1" as it counts the hidden file as well. But when file is not open then result comes "1". I want some adjustment in above macro that it should only count those files of which attribute in not hidden. Some thing like (see below). I tried everything in my knowledge but no success so far. Please can any friend solve my problem. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
File count excluding those which are hidden
Hi
You have to look at the Attributes property, where 0 represent a normal file, 2 is a hidden file etc. Try this (not tested): If File.Type LIke "*Microsoft Office Excel*" And file.Attributes=0 then Hopes this helps .... Per "K" skrev i meddelelsen ... Hi all, I am using excel 2007 and I got an excel file call "Test File" in folder "My Document". When I open "Test File" I can see another file appear in same folder with same name the only difference is that it got "~$" in the beginning of its name like "~$Test File". I know these are hidden files and only appear when you open file and I also know that if you dont want to see them then unselect "Show hidden file and folders" option under "Tools+FolderOption+View". These files hidden attribute is alwasy selected by default. Ok now i come to my problem. I got macro (see below) which count files in folder "My Document". Private Sub CommandButton1_Click() Dim foldername As String Dim FSO As Object Dim fldr As Object Dim file As Object Dim cnt As Long foldername = "C:\My Document" Set FSO = CreateObject("Scripting.FilesystemObject") Set fldr = FSO.GetFolder(foldername) For Each file In fldr.Files If file.Type Like "*Microsoft Office Excel*" Then cnt = cnt + 1 End If Next file Set file = Nothing Set fldr = Nothing Set FSO = Nothing Range("A1").Value = cnt End Sub Above macro count the files in folder "My Document" and put the result in cell "A1". The problem is that if file in "My Document" folder is open then macro brings the result "2" in cell "A1" as it counts the hidden file as well. But when file is not open then result comes "1". I want some adjustment in above macro that it should only count those files of which attribute in not hidden. Some thing like (see below). I tried everything in my knowledge but no success so far. Please can any friend solve my problem. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
File count excluding those which are hidden
if (file.Attributes And vbHidden) = 0 then 'it's not hidden IOW, the file's attributes does not contain the 2/vbHidden bit Regards, Peter T "K" wrote in message ... Hi all, I am using excel 2007 and I got an excel file call "Test File" in folder "My Document". When I open "Test File" I can see another file appear in same folder with same name the only difference is that it got "~$" in the beginning of its name like "~$Test File". I know these are hidden files and only appear when you open file and I also know that if you dont want to see them then unselect "Show hidden file and folders" option under "Tools+FolderOption+View". These files hidden attribute is alwasy selected by default. Ok now i come to my problem. I got macro (see below) which count files in folder "My Document". Private Sub CommandButton1_Click() Dim foldername As String Dim FSO As Object Dim fldr As Object Dim file As Object Dim cnt As Long foldername = "C:\My Document" Set FSO = CreateObject("Scripting.FilesystemObject") Set fldr = FSO.GetFolder(foldername) For Each file In fldr.Files If file.Type Like "*Microsoft Office Excel*" Then cnt = cnt + 1 End If Next file Set file = Nothing Set fldr = Nothing Set FSO = Nothing Range("A1").Value = cnt End Sub Above macro count the files in folder "My Document" and put the result in cell "A1". The problem is that if file in "My Document" folder is open then macro brings the result "2" in cell "A1" as it counts the hidden file as well. But when file is not open then result comes "1". I want some adjustment in above macro that it should only count those files of which attribute in not hidden. Some thing like (see below). I tried everything in my knowledge but no success so far. Please can any friend solve my problem. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
File count excluding those which are hidden
Thanks lot Peter and Per Jessen. Thats brilliant. Cant believe it was
that simple |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtoal excluding hidden columns | Excel Worksheet Functions | |||
Countif excluding hidden rows | Excel Worksheet Functions | |||
Excluding Hidden Rows from calculations | Excel Worksheet Functions | |||
Sum cells, excluding hidden columns | Excel Programming | |||
Sum of a column excluding hidden rows | Excel Worksheet Functions |