Solved

Perform file/folder operations from access

Posted on 2006-11-01
4
253 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
[X]
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
  • 3
4 Comments
 
LVL 66

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 66

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 66

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.

724 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