Get Some
General => Technology & Hardware => Topic started by: The Demon Lord on April 19, 2013, 11:55:03 am
-
Hey guys - I have a question from my Brother, he is wanting to get some VB macros for Excel to do the following:
Look at a folder structure and output the folders to Excel:
(so look in C:\stuff and output the folder names to Excel) so:
job1
job2
job3 etc
Then Look into each sub folder that is there and determine if there is a particular .pdf
So looking into C:\stuff\Job1\Folder1 and seeing if there is a .pdf file in there - if there is, set the status in Excel to something like 'opened'
Then look into a different sub-sub folder and see if there is a *.doc file in there
So looking into C:\stuff\Job1\Folder2 and seeing if there is a *.doc in there and setting the status in Excel to something like 'tested, awaiting approval'
Then look into another different sub-sub folder and see if there is a *.doc file in there
So looking into C:\stuff\Job1\Folder3 and seeing if there is a *.doc in there and setting the status in Excel to something like 'Billed'
so it would look like this in Excel:
job1 - Billed
Job2 - opened
job3 - Tested awaiting approval
Anyone here that could be keen to write this? I am sure there could be some form of fee arranged (copping a feel etc.)
-
I stopped reading at VB.
I dislike VB. Both the programming language, and the beer.
-
I stopped reading at VB.
I dislike VB. Both the programming language, and the beer.
Know ye of another programming language in which the above could be accomplished in Excel?
-
I sometimes work in VB.
Excel is terrible, and macros are worse.
He should Google how to VB, and then do it. It's very common, especially folder scanning.
I hope this helps.
-
What version of Excel?
-
What version of Excel?
2007
He got up to the point where it would look in the directory and output into excel - it was the 'if folder x contains = true then set coloum to status "blah"' that he was stuck on - and not knowing the programming language and syntax
-
Does it have to be in excel? If you are happy to have to output a txt/csv file which you then open, can be done in almost any language. Your description is also majorly unclear, in regards to what sets the text. Are you looking for a specific document in each folder, or is it *.doc in one, then *.pdf in the second etc. I imagine using dir(), and checking for a null would work.
-
Thought this thread was about Virtual Bulletin.
Son, I am disappoint.
-
Thought this thread was about demon drinking VB.
-
Don't be silly, no one does that.
-
Hey guys - I have a question from my Brother, he is wanting to get some VB macros for Excel to do the following:
Look at a folder structure and output the folders to Excel:
(so look in C:\stuff and output the folder names to Excel) so:
job1
job2
job3 etc
Then Look into each sub folder that is there and determine if there is a particular .pdf
So looking into C:\stuff\Job1\Folder1 and seeing if there is a .pdf file in there - if there is, set the status in Excel to something like 'opened'
Then look into a different sub-sub folder and see if there is a *.doc file in there
So looking into C:\stuff\Job1\Folder2 and seeing if there is a *.doc in there and setting the status in Excel to something like 'tested, awaiting approval'
Then look into another different sub-sub folder and see if there is a *.doc file in there
So looking into C:\stuff\Job1\Folder3 and seeing if there is a *.doc in there and setting the status in Excel to something like 'Billed'
so it would look like this in Excel:
job1 - Billed
Job2 - opened
job3 - Tested awaiting approval
Anyone here that could be keen to write this? I am sure there could be some form of fee arranged (copping a feel etc.)
this is basically what my day job involves doing for people.. can whip something up...
question why 3 subfolders?
why not c:\stuff\Job1\
and three documents with a defined name scheme. I.e xxx.pdf = opened Billed.doc = billed, T&A.doc - Tested etc?
give me 1 hour to eat my dinner and i will have some code posted...
-
done
zip attached
Stuff = v1
stuff 2 = v2 (has option on the stuff sheet to change the filenames you are looking for etc.... i could shorten the code down but CBF since it works.. and its robust... and it's sunday...
-
Cheers Xsannz - have sent off to my brother to see how it works
-
done
zip attached
Stuff = v1
stuff 2 = v2 (has option on the stuff sheet to change the filenames you are looking for etc.... i could shorten the code down but CBF since it works.. and its robust... and it's sunday...
needs moar tray icon
-
needs moar tray icon
+1
-
This code is not sufficiently documented, nor has it gone through the proper QA procedures.
-
This code is not sufficiently documented, nor has it gone through the proper QA procedures.
so it will actually work properly then and keep me in my job because then only i can debug it...
FFS ... it was a quick slap up if you want me to go through and comment it all so you can understand it by all means happy to do so.. but i's pretty easy to follow.
-
+1
i agree, and it needs a tray notification on hide that says i am still running...
-
[COLOR=#008000]'private Global Variables[/COLOR]
Private insetspace As Integer
Private activeSht As Worksheet
[COLOR=#008000]'Display all the files in a folder. Searches all the sub folders.
'Prints Folder Names in Column A and and the file Names in Column B[/COLOR]
[COLOR=#008000]'this Sub is called by the button embedded on the the Output worksheet and is also selectable from the Macros menu in Excel.
' If you dont want numpty users calling the modules or subs in the wrong order, change them to functions this allows you to access them through code
'but stops them from appearing in the Macros menu and being executed out of sequence... for an example see below.. this sub appears in the macro section and it calls the function below it which
' doesnt appear in the macros menu.[/COLOR]
Sub SearchME()
Dim pth As String
Dim fso As FileSystemObject
Dim baseFolder As Folder
insetspace = 2
[COLOR=#008000] 'set the path to be read from the config ""STUFF"" worksheet cell A2[/COLOR]
pth = Worksheets("Stuff").Range("A2").Value
Set fso = New FileSystemObject
[COLOR=#008000] ''check if the folder actually exists or not[/COLOR]
If (Not (fso.FolderExists(pth))) Then
'the folder path is invalid. Exiting.
MsgBox "Invalid Path"
Exit Sub
End If
[COLOR=#008000] ' sets the base level to scan from using the the path set earlier[/COLOR]
Set baseFolder = fso.GetFolder(pth)
[COLOR=#008000]'Select and Change focus to the ""Output"" Worksheet[/COLOR]
Worksheets("Output").Select
[COLOR=#008000] ' clear the contents of the sheet[/COLOR]
Selection.ClearContents
[COLOR=#008000]' Insert Header Names onto Row 1 Columns[/COLOR]
Worksheets("Output").Range("A1").Value = "Folder Name"
Worksheets("Output").Range("B1").Value = "File Name"
Worksheets("Output").Range("C1").Value = "Found"
[COLOR=#008000] 'if something fucks up exit nicley[/COLOR]
On Error GoTo ErrHandler
[COLOR=#008000]'dont update the screen and freak out the fucking user and cause epiliptic seizures from flashing[/COLOR]
Application.ScreenUpdating = False
[COLOR=#008000]'tell excel to only calculate manually so that if it pulls a massive dataset it doesnt trip over it's own shoes run out of memory and crash like all Microsoft office apps.[/COLOR]
Application.Calculation = xlCalculationManual
[COLOR=#008000] 'call the function that actually does the work and pass it the basefolder variable to scan[/COLOR]
RFileNames baseFolder
[COLOR=#008000]'this it where we go when something fucks up royally - generally caused by some BS undocumented Microsoft bug /Ahem Feature....[/COLOR]
ErrHandler:
[COLOR=#008000] 'let the screen update so the user can see the big fucking fireball crash thats about to happen with the pretty error messages[/COLOR]
Application.ScreenUpdating = True
[COLOR=#008000] 'and set excel to turn calculation back on automatically otherwise the user will complain that his formulas are not working...[/COLOR].
Application.Calculation = xlCalculationAutomatic
End Sub
[COLOR=#008000]' I use a function so some numpty cant see the code in the MACRO list and accidently call it out of sequence.[/COLOR]
Function RFileNames(baseFolder As Folder)
[COLOR=#008000] 'make silly declarations based on their type[/COLOR]
Dim folder_ As Folder
Dim file_ As File
[COLOR=#008000] 'Now we loop through the folders and subfolders[/COLOR]
For Each folder_ In baseFolder.SubFolders
[COLOR=#008000]'call recursive function.[/COLOR]
RFileNames folder_
[COLOR=#008000] 'and we go again .. .. next folder[/COLOR]
Next folder_
[COLOR=#008000] 'now lets loop through each file we find in each folder and check if it meets our criteria.... YOU COULD USE A CASE SELECT HERE.. but i am lazy and this is small enough for this.[/COLOR]
For Each file_ In baseFolder.Files
[COLOR=#008000]' check if the file we found is called one of the names from the ""STUFF WORKSHEET CONFIG CELLS" A5, A8, or A11[/COLOR]
If file_.Name = Worksheets("Stuff").Range("A5").Value Then
Worksheets("Output").Range("A" & insetspace).Value = baseFolder.Path
Worksheets("Output").Range("B" & insetspace).Value = file_.Name
Worksheets("Output").Range("C" & insetspace).Value = "FOUND"
ElseIf file_.Name = Worksheets("Stuff").Range("A8").Value Then
Worksheets("Output").Range("A" & insetspace).Value = baseFolder.Path
Worksheets("Output").Range("B" & insetspace).Value = file_.Name
Worksheets("Output").Range("C" & insetspace).Value = "FOUND"
ElseIf file_.Name = Worksheets("Stuff").Range("A11").Value Then
Worksheets("Output").Range("A" & insetspace).Value = baseFolder.Path
Worksheets("Output").Range("B" & insetspace).Value = file_.Name
Worksheets("Output").Range("C" & insetspace).Value = "FOUND"
Else
[COLOR=#008000] 'do nothing if it's not one of the above[/COLOR]
End If
[COLOR=#008000]' increment the row number by two so we get a space between each output.[/COLOR]
insetspace = insetspace + 2
[COLOR=#008000] ' and we go again.. .. next file[/COLOR]
Next file_
[COLOR=#008000]'time to exit this function ...[/COLOR]
End Function
THERE YOU GO... ALL COMMENTED FOR YOU..... sometimes i wish i coudl put those comments in production code.
-
You can in game dev :P
-
Swearing in comments is not enough. You have to put it in your code.
Dim bullshitPath As String
Dim motherFuckingBaseFolder As Folder
-
^I agree with the monkey.
Or be a real cock, and name your variables with incrementing numbers, such as v1, v2, v3, v4.
Then you get crap like, makes for much increased readability.
var v5 = v1 + v2.substring(0, v3) + " would " + v3 + " that skank unil " + v4;
-
sprintf("%s %s %s %s %s",s1,s2,s3,s4,s5);