Plan to automate filing - would it work and if so what code could I use?

[alf810]

Some background: I scan tons of paperwork and lately our company has had double the workload due to a merger. It's an absolute nightmare. Anyway, I have a background a long time ago with setting up advanced macros in excel's vba and just came up with an idea of how to make the job a bit easier (hopefully).

I would file each packet of work into the scanner and (from the scanner) name the file by the persons first two letters of their surname followed by their account number they have with us. For example: John Smith who has an account #555 would be named sm555 before getting scanned into a folder on my computer.

This is where the macro part kicks in. Could I run a macro from Excel that will look at that file and understand roughly where to put it? For example, our file system is set up like this for surnames:
Aa-Al, Am-Az, Ba-Bl, Bm-Bz, etc...

So you can see why I listed John Smith as sm, it's so the VBA code can look at the sm and know to go to the folder Sm-Sz. It would then check to see if there is a folder with the number 555 (it's VERY important that it only looks for the exact number, don't want to accidentally file into a person whose account is 20555, for example)

If it finds that number it then moves that file into that account folder. If it doesn't find the number (meaning it's a new patient and needs a new folder created) then i'd like it to be sent to a folder I have in my documents, which I would go through and manually do myself.

Also the numbers in the folders always have # in front of them and the folders are in a format to be surname, followed by full name if that helps. So John Smith's folder would literally be called Smith, John #555

I'd really appreciate any help - there is still a lot more to my job than just this (for one taking out staples, making sure there are no backsides, filing single sheet files which is very time consuming and can't use this method, plus we have paperless faxed over scans I do, plus alphabetizing the cupboard, etc... so I'm not just trying to look for a way to automate my job, I'm just burned-out and also believe if indeed technology can automate it then why not? Again I appreciate it

 

[alf810]

Re: Does Excel VBA have a hard time handling network drives?

Hmm, not really sure how to help. It kind of sounds like maybe the computer you're using at work doesn't have a good connection to the network and keeps losing the folder. If you put the folder path in windows explorer and press 'enter' does it navigate to that folder ok?

Hey Locke, thanks for replying. I'm sorry if the previous post didn't make much sense (in the beginning, at least), I was going to start a new thread, since this one is too long, but accidentally replied here instead. Since you're here (and anyone else who wants to help), I changed the around a little and now it only complains about the Folder, otherwise it looks like it can find all of the network folders just fine and can read the names & accounts perfectly fine too.

Here's the exact error:

Run-time error ‘91’:

Object variable or With block variable not set

So I clicked debug

It yellow highlights on “for each folder in surnamegroupfolder.subfolders”

But when I hover over folder, I notice it is looking at \\netappbk\Network-Scanning\dev27927\NA-NL but the file I used as a test was EA176103, so it should be looking in the EA-EL, not NA-NL folder. It seems like every time I change something, no matter what the "folder" variable is always looking at the NA-NL folder.

Here's the new code, just ignore my previous posts:

Option Explicit

Sub MoveFiles()

Dim Folder As Object
Dim File As Object

Const FolderPath As String = "\\netappbk\Network-Scanning\UsersDirectory\BrianSaint\test"


Set Folder = CreateObject("Scripting.FileSystemObject").GetFolder(FolderPath)

For Each File In Folder.Files
Call MoveCustomerDocument(File.Path)
Next File

End Sub






Sub MoveCustomerDocument(DocumentPath As String)




Const CustomerDocumentsDirectoryPath As String = "\\netappbk\Network-Scanning\dev27927"
Const NewCustomerDocumentsDirectoryPath As String = "\\netappbk\Network-Scanning\UsersDirectory\BrianSaint\NEW DOCUMENTS"

Dim FSO As Object 'Scripting.FileSystemObject
Dim CustomerDocumentsDirectory As Object 'Scripting.Folder
Dim CustomerDirectory As Object 'Scripting.Folder

Dim DestinationDirectoryPath As String

Dim DocumentName As String
Dim SurnamePrefix As String
Dim AccountNumber As String

Set FSO = CreateObject("Scripting.FileSystemObject")

'There could be room for error checking in this section in case an improper file name is passed into the function i.e. "arc123" instead of "ar123"
SurnamePrefix = Left$(FSO.GetBaseName(DocumentPath), 2)
AccountNumber = Mid$(FSO.GetBaseName(DocumentPath), 3)
Set CustomerDocumentsDirectory = FSO.GetFolder(CustomerDocumentsDirectoryPath)

Set CustomerDocumentsDirectory = FindCustomerFolder(SurnamePrefix, AccountNumber, CustomerDocumentsDirectory)

If CustomerDirectory Is Nothing Then
DestinationDirectoryPath = NewCustomerDocumentsDirectoryPath
Else
DestinationDirectoryPath = CustomerDocumentsDirectoryPath
End If

FSO.MoveFile DocumentPath, FSO.BuildPath(DestinationDirectoryPath, FSO.GetFileName(DocumentPath))



End Sub

Function FindCustomerFolder(SurnamePrefix As String, AccountNumber As String, CustomerDocumentsDirectory As Object) As Object

Dim Folder As Object 'Scripting.Folder
Dim SurnameGroupFolder As Object 'Scripting.Folder

'Loops through each Surname-Group Folder Alphabetically
For Each Folder In CustomerDocumentsDirectory.SubFolders 'Subfolders are the GroupFolders followed by the Actual Customer's Directory

'Stop once we've found a folder that is "greater than" the surname (Case Insensitive)
If StrComp(Left$(Folder.Name, 2), SurnamePrefix, vbTextCompare) = 1 Then
Exit For
End If

Set SurnameGroupFolder = Folder
Next Folder

'Search through the SurnameGroupFolder for a folder that matches the account number
For Each Folder In SurnameGroupFolder.SubFolders
If Folder.Name Like "*[#]" & AccountNumber Then
Set FindCustomerFolder = Folder
Exit Function
End If
Next Folder

End Function