We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Perform file/folder operations from access

VoodooFrog
VoodooFrog asked
on
Medium Priority
287 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.  
 

Comment
Watch Question

SQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Thanks for the grade.  Good luck with your project.  -Jim

Author

Commented:
thanks for your help, it looks like it will be exactly what I need.  
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.