Solved

Perform file/folder operations from access

Posted on 2006-11-01
4
246 Views
Last Modified: 2008-03-10
We have a database that keeps track of all of the projects that we have worked on and the details regarding those jobs.  Recently we have had an increase in the number of associated files with the porjects and the upkeep of the files (outside of the database) has become cumbersome.  We are looking at a solution of automatically generating some of the necessary files for a project and putting them into a specific folder.  Other times there will already be the associated files made and we just want to create a copy inside the project folder.

As an example:  

Project A has: a word doc, 3 pictures, and 2 excel files.  
The word doc already exists in a folder called: "Opening letters"
The 3 pictures already exist in a folder called: "Product Pictures"
the 2 excel need to be generated by the database
The names of the word doc and pictures are standardized and will be:

CustomerName_OpeningLetter.doc
ProductA_Picture1.jpg
ProductA_Picture2.jpg
ProductA_Picture3.jpg
The excel files will need to be named as they are exported.

What I will need to do is

1)  Look to see if the folder “ProjectA” exists, if not then create it
2)  Look to see if the opening letter exists, if it does then copy it to the folder “ProjectA”
3)  Look to see if any pictures exist, if there are any then I need to find all of them and move them to the folder “ProjectA”
4)  Export the 2 access reports as excel files and name them appropriately, saving in the project folder

The folder “ProjectA” will be created on a network drive as follows \\Office\Projects\ProjectA.    

I am fairly proficient at VBA, but I do not know the specific functions that I would use to accomplish this.  Any code samples would be appreciated.  
 

0
Comment
Question by:VoodooFrog
  • 3
4 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
Comment Utility
In any code module go to Tools:References... and check the checkbox next to 'Microsoft Scripting Runtime' library.

Then, copy-paste the below code into a separate module, and knock yourself out...

Function fn_validate_drive(ByVal sDrive As String)

Dim fso As FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")

fn_validate_drive = fso.DriveExists(sDrive)

Set fso = Nothing

End Function

Function fn_validate_directory(ByVal strPath As String, ByVal bCreate) As Boolean

'Attempt to find a directory.
'  Returns TRUE if found, FALSE if not found.
'  Returns TRUE if user passed bCreate = True and code created the directory.

Dim fso As FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")

Select Case fso.FolderExists(fso.GetParentFolderName(strPath))
    Case True
        fn_validate_directory = True
        Exit Function
    Case False
        If bCreate Then
            fso.CreateFolder (fso.GetParentFolderName(strPath))
            fn_validate_directory = True
        Else
            fn_validate_directory = False
        End If
End Select

Set fso = Nothing

End Function
Function fn_validate_file(ByVal sFile As String) As Boolean

Dim fso As FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")

fn_validate_file = fso.FileExists(sFile)

Set fso = Nothing

End Function
Function fn_get_drive_name(ByVal sString As String) As String

'Gets a drive name from a file

Dim fso As FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")

fn_get_drive_name = fso.GetDriveName(sString)

Set fso = Nothing

End Function

Function fn_get_file_name(ByVal sPath As String) As String

'Searches from right to left in strString and returns string to the right of "\"

Dim fso As FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")

fn_get_file_name = fso.GetFileName(sPath)

Set fso = Nothing

End Function

Function fn_get_path_name(ByVal sPath As String) As String

'Gets a file path from a complete string  (drive is included)
'i.e. c:\vb\icons\accessories\office01.ico returns c:\vb\icons\accessories

Dim fso As FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")

fn_get_path_name = fso.GetParentFolderName(sPath)

Set fso = Nothing

End Function
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
this one too

Function fn_validate_file(ByVal sFile As String) As Boolean

Dim fso As FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")

fn_validate_file = fso.FileExists(sFile)

Set fso = Nothing

End Function
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Thanks for the grade.  Good luck with your project.  -Jim
0
 

Author Comment

by:VoodooFrog
Comment Utility
thanks for your help, it looks like it will be exactly what I need.  
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now