Solved

Perform file/folder operations from access

Posted on 2006-11-01
4
247 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

911 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

20 Experts available now in Live!

Get 1:1 Help Now