Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax error - FSO related?
You may want to go straight to ***MY AIM*** below or you can read the history..
Back in May in a thread titled 'System calls in Excel' I asked If it is possible to get the properties of a folder/drive from within an Excel macro and then populate separate cells with the folder/drive size, number of files & number of folders? To which Ardus Petus & papuo both very kindly responded, with macro code. The methods posted were quite different from each other and produce quite different output. (for those that don't know you can get to the thread by clicking my display name in blue at top of post and the clicking on 'Recent posts by this user'). papuo's code will list the starting folder's path, size and the number of files and number of Subfolders. The 'size' would be accurate (bare in mind that the result is /1024 in the example so shows KB rather than Bytes) to what you would get if you got the properties of the folder from the OS right click menu. However it would only count the files and subfolders within that folder and not the total of each contained within that folder AND it's subfolders. It will then do the same for each of the subfolders (recurring). Ardus's code worked fine for me at home (with Office 2003 on XP) apart from the size was not quite correct as Ardus had already said in his post, however at work (which is where I need the code) we have Office 2000 on NT4 machines and the code fell over at 'BrowseFolder'. I have investigated this and believe it to be due to the fact that as far as VBE is concerned 'Microsoft Shell Controls and Automation' is 'Missing' (I have looked and Shell32 does exist in the correct folder, but I do not think I can fix this). Although I did manage to 'bodge' Ardus's code to work on a pre-populated list of folder paths. So now both sets code will run on my machine at work but Very slowly, and they would only get so far before falling over on a buffer over run. At which point with, Arduss code I had bodged, I could restart on the next folder on the list and it would get the next few folders before stopping once more. All in all it takes most of the afternoon or longer to get the information from the root folders of one network drive, longer than it would take to get the information by getting the properties for each folder by hand. I tried the usual tactics of turning off screen update and automatic calculations but these did little to help the speed. Ive started creating a macro from scratch for this but let me clarify.. ***MY AIM*** I am trying to write a macro that will list the root folders in a network drive and then provide the size, the total number of subfolders and total number of files contained within each root folder €“ the same information as right clicking on a folder and selecting €˜Properties. With the help of http://www.techbookreport.com/tutorials/fso2.html (tweaking sample code to allow for the fact that 'Microsoft Shell Controls and Automation' is 'Missing') and by referring to Arduss and papuos codes I came up with a one sub macro that lists all the root folders and there accurate size but like papuos code will only list that folders file and subfolder count not the totals. (but it is still quite fast at this point, even before speed tricks) Looking again at the macros provided I came to the conclusion I probably need to use a self-calling Sub or Function to be able to get the total file and subfolder counts so I have tried to code one and this is where I am now with a syntax error which at this moment I cannot get my head round. My code is copied below. I am aware that this is the point at which my macro is likely to get quite a bit slower and maybe suffer the same crashing as the macros provided in the first post but I would still like to understand my error. I found DoEvents whilst researching before making this post, which I shall experiment with re the crashing and I found the same or similar code in two threads regarding using Arrays to query folders for information quikly, but I shall have to look at that when I have some time and try to understand it better. In the meantime any help will be greatly appreciated. I know there are dir type Addins and such available but they dont quite do what Im after and invariably there code is locked preventing me learning from it. However they do do what they do very fast and without crashing so I know what Im after must be possible. Damon Dim fldCount As Integer Dim filCount As Integer Dim fso As Object Sub testfdrive() Dim rtflds As Object Dim strText As String Dim i As Integer Set fso = CreateObject("Scripting.FileSystemObject") Set rtflds = fso.GetFolder("f:\").subfolders i = 2 For Each f In rtflds strText = f.Path Worksheets("f").Cells(i, 1) = strText strText = f.Size Worksheets("f").Cells(i, 2) = strText fldCount = f.subfolders.Count filCount = f.Files.Count If f.subfolders.Count 0 Then FaFCount f.subfolders, fldCount, filCount, i End If Worksheets("f").Cells(i, 3) = fldCount Worksheets("f").Cells(i, 4) = filCount i = i + 1 Next Range("a1").Activate Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End Sub Sub FaFCount(Fldr As Object, fldCount As Integer, filCount As Integer, indx As Integer) Dim sflds As Object Dim sbfd As Object Set fso = CreateObject("Scripting.FileSystemObject") Set sflds = fso.GetFolder Fldr.subfolders '<----SYNTAX ERROR!!!!!!!!! For Each sbfd In sflds fldCount = fldCount + sbfd.subfolders.Count filCount = filCount + sbfd.Files.Count Next sbfd If sbfd.subfolders.Count 0 Then FaFCount sbfd.subfolders, fldCount, filCount, indx End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax error - FSO related?
Note to self: Don't post on Saturday's if want reply :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run time error related to add-ins | Excel Discussion (Misc queries) | |||
related to error function | Excel Discussion (Misc queries) | |||
runtime error: syntax error or access violation | Excel Programming | |||
runtime error: syntax error or access violation | Excel Programming | |||
Syntax Error Runtime Error '424' Object Required | Excel Programming |