Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Perform file/folder operations from access

Posted on 2006-11-01
4
249 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
ID: 17853106
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
ID: 17853110
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
ID: 17867467
Thanks for the grade.  Good luck with your project.  -Jim
0
 

Author Comment

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

840 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