# An Excel/VB Problem



## crcook84 (Aug 12, 2010)

In a side job I have, I have found Excel to be a useful took in organizing data. Up to now, I've manually entered the data. But, a year or two ago, I came up with the idea of having a macro to search for files. The idea worked good because there are individuals who have already created scripts. Unfortunately, those scripts are limited to specific file types or the scripts that aren't limited aren't as explained as how to use them. Can someone help me figure out some of these scripts?:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=247

http://www.excelguru.ca/node/106

http://www.bigresource.com/Tracker/Track-vb-tuERiDvbQ0/

What I mainly want out of the script is to be able to search folders and subfolders for file types. I would prefer to be able to search for any file type. But, if I have to be specific, .jpg files.


----------



## Ziggy1 (Jun 18, 2002)

I will post what I use tomorrow, the VBA Express one looks familiar to what I had to scrap when moving to Excel 2007. The one I use is great, I use it all the time.

Explain what you want to do with the searched results.


----------



## crcook84 (Aug 12, 2010)

I run a business (well, my dad owned his own business and, to save me from needing a business license, allowed me to start a sub-organization that I manage) for scanning slides. I started it after scanning around 3,000 slides for my neighbor. In case you're wondering why I started it with places like Costco and Walgreens around, I do it for retired missionaries who have a hard time getting around and not that much money. What's more, I put a more personal touch on what I do.

My main goal with the script is to count the slides/pictures once I scan them into my computer (in the .jpg format). Originally, I was wanting to try this script to see how well it would work. But now, I see it as a means by which I could keep myself organized.

By the way, seeing as how you mentioned Excel 2007, the document I am using is a .xls type (I like that because it's more compatible). But, I am willing to use .xlsx if it's more flexible for your script.


----------



## Ziggy1 (Jun 18, 2002)

Ok, I can't post a workbook at the moment, but paste this into a Module, you need to alter the Path noted in the code to the folder you want to check

path = "C:\Users\Public\Pictures\Sample Pictures"

There are different ways to manage how you define the path, but you need to clarify how you would like to "Pass" the Path location to the macro.

This code will search the directory and return all the filenames found with ".jpg", and also the full path, I can't claim 100% of the coding as I scooped it from the net years ago, but I have modified it to suit my needs ( and yours in this case).

** note my comment about the other code not working has to do with Excel 2007 itself, not the version of your excel book, I did not test the VBA Express, but it looks like something I had to replace in the past due to upgrade to 2007.


```
Public Function SearchForFiles()

Range("A1").Select

Application.DisplayAlerts = False
Application.ScreenUpdating = False


Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim path As String

Dim FullPath, OrigName As String

Dim wb As Workbook
Dim WS As Worksheet
Dim xRow As Integer
  xRow = 2
  
  Set wb = ThisWorkbook
  Set WS = wb.Sheets("sheet1")
  
  
    WS.Columns(1).Clear
    WS.Columns(2).Clear
    WS.Cells(1, 4).Clear
    
     Cells(1, 1) = "Filename"
      Cells(1, 2) = "Full Path"


    'change to the path of your folder
  path = "C:\Users\Public\Pictures\Sample Pictures"
  
    If path = "" Then
  
        Exit Function
  
    End If
  
  
 
  'Loop through the folder & build file list
  strFile = Dir(path & "\" & "*.jpg")
 
  While strFile <> ""
  
            intFile = intFile + 1
             ReDim Preserve strFileList(1 To intFile)
            strFileList(intFile) = strFile
        
      strFile = Dir()
  Wend
 
  'see if any files were found
  If intFile = 0 Then
  MsgBox "no Jpegs found"
    Exit Function
  End If
 
 
 Cells(1, 4) = intFile & "  Files found"
 
 
  'cycle through the list of files
     For intFile = 1 To UBound(strFileList)
     
       
            OrigName = strFileList(intFile) 'stores File name
            FullPath = path & "\" & strFileList(intFile) ' stores Path and filename
                  
                          Cells(xRow, 1) = OrigName
                          Cells(xRow, 2) = FullPath
 xRow = xRow + 1

        
    Next intFile


Application.DisplayAlerts = True
Application.ScreenUpdating = True


End Function
```


----------



## crcook84 (Aug 12, 2010)

Thanks for that. I tried it in the document. But, I can't seem to find which cell it works in. I tried entering into a cell what I thought was the function:
=SearchForFiles()
But, that didn't work. So, what was the function command for the script to work properly?


----------



## Ziggy1 (Jun 18, 2002)

make sure the reference to the sheet name exists, perhaps you have a different name

Set WS = wb.Sheets("sheet1")

you only need to Call.. *SearchForFiles*

and technically it is not a function, just change it to a sub if you want. I scraped it from one of my excel files.... then it will also appear in the macro list.


----------



## crcook84 (Aug 12, 2010)

I tweaked it using your recommendations. I even Googled subroutines and how to call them. It still won't work. So, either I'm incompetent or my workbook file isn't working right. So, I'll just wait until you get a chance to post your workbook file with the module added.


----------



## Ziggy1 (Jun 18, 2002)

click on the developer TAB and then Macros, and you should see the name in the macro window


----------



## crcook84 (Aug 12, 2010)

That did it. I got it to work. And, after seeing what it does, thanks for your help but that's not what I was expecting. I got a list of all the jpegs in the directory along with their paths. What I was wanting was a module that would count all of a specific type of file in a directory and any sub-directories in that folder and give me that number count. My apologies if I wasn't clear enough for that.


----------



## Ziggy1 (Jun 18, 2002)

ok, no problem, I'll add a loop to check all directories ( tonight), but really what value is it to use Excel?

The code does list a count of files found, but I need to expand to subdirectories.... see cell D1, I just forgot to add a autofit so you might not see it right away.


----------



## crcook84 (Aug 12, 2010)

Ziggy1 said:


> what value is it to use Excel?


Originally, it was just an experiment to see if I could automate getting a file count of all the images in a directory without having to use the Windows XPs search function every time. I figured that if I could create a script for giving me the date automatically every time I printed up an invoice, then why not find a way to retrieve a file count of all the scanned images in a directory? It was those links at the top that told me that it was possible. I just didn't know to what extent.


----------



## Ziggy1 (Jun 18, 2002)

ok, try this out it. It still lists all the files and directories. I left it in because it might be useful to someone else... you can comment out the section that shows it... but it will also display the directories and the count of files in each of the sub directories.

The code is not as clean as i would like it... but it seems to do the job... to fully integrate with your process might need some changes, to the references... sheet path etc... I'm not clear on how you establish the "Path" is it a fixed directory or do you want to some how pick it...

Note, you need to call the sub.... _*List_Sub_Directories*_


----------



## crcook84 (Aug 12, 2010)

Ziggy1 said:


> ok, try this out it. It still lists all the files and directories. I left it in because it might be useful to someone else... you can comment out the section that shows it... but it will also display the directories and the count of files in each of the sub directories.
> 
> The code is not as clean as i would like it... but it seems to do the job... to fully integrate with your process might need some changes, to the references... sheet path etc... I'm not clear on how you establish the "Path" is it a fixed directory or do you want to some how pick it...
> 
> Note, you need to call the sub.... _*List_Sub_Directories*_


I took a look at the code and your instructions for using it...and I can definitely say, with all certainty, that I am completely confused. But, I think that that's only because I'm not a frequent Excel user (my specialty is computer and network administration). But, instead of you trying to explain to me how to integrate it into my worksheet, I'll post my worksheet so that you can get a chance to see what I am trying to do.

You should see categories in the first column with numbers in the second column. Basically, data accumulation and calculation. The first three categories are the ones I am trying to change:
# of Pictures
# of Film Images
# of Print Images

A client would give me a load of items to scan and digitize and I would organize them according to what they are. Where the worksheet file comes in is I'd have a running total of everything I scanned for the client in that batch and add that data to an invoice. How I wanted to streamline that was to automate the number calculation process so that I'd have that the instant I opened the file. That would save time in getting the project finished. Obviously, it's a marginal amount. But, I was interested in the challenge.

I have added the module into my worksheet (note added not integrated) to establish that I am trying.


----------



## Ziggy1 (Jun 18, 2002)

ok but what I sent you should work by clicking the button? can you tell me that and if the results in column C/D make sense?


----------



## crcook84 (Aug 12, 2010)

Ziggy1 said:


> ok but what I sent you should work by clicking the button? can you tell me that and if the results in column C/D make sense?


Using the information in the module code as is, the button works and the columns are understandable.


----------



## Ziggy1 (Jun 18, 2002)

I think I also need clarification on how to determine the different types....

# of Pictures
# of Film Images
# of Print Images

is this determined by the folder name, meaning you place in 3 different folders for the different types. You are not making this clear to me... I mean Pictures and Images could essentially be the same file extension?


----------



## crcook84 (Aug 12, 2010)

Ziggy1 said:


> I think I also need clarification on how to determine the different types....
> 
> # of Pictures
> # of Film Images
> ...


Yes, those lines correspond to folders. All of the files *will* be .jpg. I divided it all up according to the type of material I was scanning (ex: negatives, pictures prints, printed text, etc.) due to the fact that my current client didn't bother to organize it. Admittedly, this batch may be the only time I do it like this because of that reason. But, once I have the code in the worksheet for how to do it, I can then apply it to all subsequent batches.


----------



## Ziggy1 (Jun 18, 2002)

so if I understand you there should only ever be 3 sub directories? Just clarify if they are 3 "Fixed" folders, or 3 sub folders relative to a main folder... you specify the main and want to return the results of the 3 subs? Just trying to understand the level to which you keep you files organized.

eg is this a standard set of folders that you have your scanner defaulted to..

C:\Users\Public\Pictures\Images
C:\Users\Public\Pictures\Film Images
C:\Users\Public\Pictures\Print Images

or

is that you have a "main" folder, perhaps named as the client or a client number, and within that main folder will contain 3 sub folders...

eg

[Main] C:\Users\Public\Pictures\Client1

[Sub] C:\Users\Public\Pictures\Client1\Images
[Sub] C:\Users\Public\Pictures\Client1\Film Images
[Sub] C:\Users\Public\Pictures\Client1\Print Images

[Main] C:\Users\Public\Pictures\Client2

[Sub] C:\Users\Public\Pictures\Client2\Images
[Sub] C:\Users\Public\Pictures\Client2\Film Images
[Sub] C:\Users\Public\Pictures\Client2\Print Images

etc

I need to know what is constant and what varies, based on your 3 types I expect that there should only ever be 3 sub folders.


----------



## crcook84 (Aug 12, 2010)

"Just how deep does the rabbit hole go?"

There's not really a consistency as to how I organize my scanning. A best case scenario is this:
G:\Media Transfers\Snell, Betty\Batch 1
Every .jpg file contained in that folder (including sub-directories) would get counted. Then, once the scanning is done, I can put that folder aside and start counting a new batch. Heck, I'd be willing to name all the folders Batch and number them after I finish the scanning. That way, I wouldn't have to adjust the code after each batch.

The worst case scenario (thus far) is this:
G:\Media Transfers\Cook, Wayne\Film Material\Batch 1
G:\Media Transfers\Cook, Wayne\Pictures\Batch 1
G:\Media Transfers\Cook, Wayne\Print Material\Batch 1
The scanned material will get divided into each of the different folders according to what the material is that is being scanned. It'll then get negotiated later on how much I'll charge for the different material.

Believe me, I'd rather have a single folder by which to do the file counting. Unfortunately, the client is a family member and I have been asked to cater to their whims. As such, I have to wait until it's all done before I can negotiate a price.


----------



## Ziggy1 (Jun 18, 2002)

take a look at this copy, you can enter the path you want to check. The only thing that might be a problem is if there are sub folders inside of sub folders... I noticed it doesn't return those so I would have to modify further if that scenario exists.

Again at this point i am still trying to get it to return what you are expecting... in my mind it is counting all the files. I display all the file names and folders so I can audit the results ( and so can you).


----------



## crcook84 (Aug 12, 2010)

I tried it. I used it on a directory with jpegs and a directory within a directory. It read the directory with jpegs; but, not the directory within a directory. I think I might have figured out something that I was doing wrong the other times, though. I kept on forgetting to use the command button.

On a side note, I've been thinking about this. Aside from wanting to accomplish this, there was a question I had in mind: how hard, or complex, would it be? Well, after seeing where this has taken us, I now understand the complexities behind my request. If you'd like to continue this to see how far you can take it (especially in light of the fact that you seemed to have gotten some personal gratification from your modifications at some point), I'm willing to stay with you on this. Otherwise, I'm willing to settle with what I've got. It may not be perfect. But, this whole thing has gotten way more complex than what I was expecting. Thank you for your help. It is most appreciated.


----------



## Ziggy1 (Jun 18, 2002)

crcook84 said:


> But, this whole thing has gotten way more complex than what I was expecting. Thank you for your help. It is most appreciated.


no problem I don't mind, it's not really complicated... we just have to both understand what is required and what is possible...

I'm here to help because it helps me keep in practice for a variety of situations, your requirement helps me because while at the moment I don't have a need to search sub directories (I usually work with single directories) it might come in handy... but all the coding that comes with it is useful for other reasons.

I'll take a look at getting it to check all sub directories.


----------



## Ziggy1 (Jun 18, 2002)

ok, I went a little nuts in my determination so I went back to "Square one", I looked at one of your links and picked it apart. Originally I thought my existing code I use could be modified, but it seems limited with the Dir() function, so this example (with "_FileSystemObject_") was easier to work with .

*Note before you can run it go into the VBA editor and click on the TOOLS menu and click "references" then scroll through tthe list and check off " Microsoft Scripting Runtime".*

The code will search all sub directories and list your totals.... the thing is that even though i coded the totals, I doubt they will make sense as the code will not know which folder contains which type of scan... unless they list in order 1,2,3 ? Try it you will see... then let me know what criteria you might need to make the determination...unless manual calculating is the only way?


----------



## Ziggy1 (Jun 18, 2002)

just a slight fix, I noticed it errored on the last total line when I ran it on another computer/folder.

so fix this line


```
'
Change this line....
'Cells(RowC + 9, 2) = Format(Application.WorksheetFunction.Sum(Range("b" & RowC + 5 & ":" & "B" & Cells(RowC + 7, 2))), "Currency")
'
'
```


```
'
to this line.....
Cells(RowC + 9, 2) = Format(Application.WorksheetFunction.Sum(Range(Cells(RowC + 5, 2), Cells(RowC + 7, 2))), "currency")
'
```


----------



## crcook84 (Aug 12, 2010)

The code works flawlessly. Every file in every individual directory got counted. Although, I think the final calculations at the end have become a bit obsolete. Here's how I would tweak it:

Change

Cells(RowC + 2, 2) = Application.WorksheetFunction.Sum(Range("b1:" & "B" & RowC))
Cells(RowC + 2, 1) = "Total files: "

Cells(RowC + 4, 1) = "Cost per scanned image"
Cells(RowC + 4, 2) = 0.25

Cells(RowC + 5, 1) = "Total Cost for Pictures"
Cells(RowC + 5, 2) = Format(Cells(RowC + 4, 2) * Cells(1, 2), "Currency")

Cells(RowC + 6, 1) = "Total Cost for Film Images"
Cells(RowC + 6, 2) = Format(Cells(RowC + 4, 2) * Cells(2, 2), "currency")

Cells(RowC + 7, 1) = "Total Cost for Print Images"
Cells(RowC + 7, 2) = Format(Cells(RowC + 4, 2) * Cells(3, 2), "currency")

Cells(RowC + 9, 2) = Format(Application.WorksheetFunction.Sum(Range(Cells(RowC + 5, 2), Cells(RowC + 7, 2))), "currency")
Cells(RowC + 9, 1) = " Total"


To this

Cells(RowC + 2, 2) = Application.WorksheetFunction.Sum(Range("b1:" & "B" & RowC))
Cells(RowC + 2, 1) = "Total files scanned"

Cells(RowC + 4, 1) = "Cost per scanned image"
Cells(RowC + 4, 2) = 0.25

Cells(RowC + 6, 2) = Format(Application.WorksheetFunction.Product(Range(Cells(RowC + 2, 3), Cells(RowC + 4, 2))), "currency")
Cells(RowC + 6, 1) = "Total cost"


A currency symbol might be good to place at the .25 part so that it can be easy to understand on the fly. I tried placing it. But, I couldn't figure out how to do it without getting an error.


----------



## Ziggy1 (Jun 18, 2002)

Great we are getting somewhere.

Change this to get dollar symbol

Cells(RowC + 4, 2) = Format(0.25, "Currency")


----------



## crcook84 (Aug 12, 2010)

Well, I think that pretty much finishes it. The code would, definitely, be beneficial for simple orders. As for the worst case scenario (doing multiple file counts for the different material being scanned), I don't think my dad really cares that much about possible expense fluctuation. (He says he does, but he's in his own world most of the time that we just ignore it.) If there's anything else you're interested in adding, I'm willing to test it.


----------



## Ziggy1 (Jun 18, 2002)

ok good to hear! I was happy to work it out, I've added it to my collection  I don't really know if I need to add anything else other than if you need me to modify anything.

I'll give you some tips to help you understand the code

When in the code window...

***It&#8217;s nice if you have 2 screens or (a larger screen) if you position the Excel Sheet beside the code window

On the View menu open the &#8220;immediate Window&#8221;

What I do is put cursor in the starting &#8220;Sub&#8221; (List_Sub_Directories) then Press F8 key and you should see the first row light up Yellow, keep pressing F8 and you step line by line through the code. All the names that are defined with Dim are variables, as the yellow line passes over them you can point to them to see the value, you will notice on the loops it may skip over lines because the condition is not met.

Also i told you to open the immediate window, here you can print the result to screen..

Eg put this in and hit enter after the yellow line passes it...

? strFolder


----------



## crcook84 (Aug 12, 2010)

Thanks. I'll make a note of that.


----------

