Have Access 2007 create a subfolder with different Id as foldername

[olseni]

Hi all
I hope someone can help me. I need to make some reports out of access, and store them on a network share under a specific report ID. This means that once I have the Report Id I want to create the folder with the ID name and later in the flow I want to dump different extracts to that folder.
Is this possible ? and if yes, can you give me advice as to how I do it ?

Have tried the Run Command OutputTo, but that doesnt seem to have the flexibility of creating a folder with different names....

Thanks in advance
Best regards
Ingrid

[MikeTheBike]

Hi all
I hope someone can help me. I need to make some reports out of access, and store them on a network share under a specific report ID. This means that once I have the Report Id I want to create the folder with the ID name and later in the flow I want to dump different extracts to that folder.
Is this possible ? and if yes, can you give me advice as to how I do it ?

Have tried the Run Command OutputTo, but that doesnt seem to have the flexibility of creating a folder with different names....

Thanks in advance
Best regards
Ingrid

Hi

There two way I know of creating new directories (there may be more?)

1) Use the MkDir strPath command (just type MkDir in the Access help search box)

eg If Dir(strPath, vbDirectory) = "" Then MkDir strPath

2) Use the FileSystemObjec t
This Object has comprehensive properties/methods for almost everything to do with the computer file system (like it says on the tin!).

You can either set a reference to the 'Microsoft Scripting Runtime' object library (this will give you access to Properties/Methods List as you write code - early binding - best if you are not familiars with the object(s))

Dim fso as FileSystemObjec t
Dim fsoFolder as Folder
Set fso = new FileSystemObjec t


OR you can create an object in code ie

Dim fso As Object
Dim fsoFolder as Object
Set fso = CreateObject("S cripting.FileSy stemObject")


Then you can use staments like

If Not fso.FolderExist s(strPath) Then Set fsoFolder = fso.CreateFolde r(strPath)

where strPath is a variable with the required directory path

HTH


MTB

[olseni]

Hi MiketheBike
Thanks a lot for your answer. However I am not really a coder so I hope you can help me a bit more. I tried searching Access Help with MdDir but nothing came up....
So if I go for option 1, which seams far the easiest, how would I tell Access what to name the folder ? I need to do this on the fly, ie when a textstring is created in my Access application ie R-07-234A....
Sorry if my questions are very basic, but I usually only work in the Access functionality, so going into VBA is a bit difficult for me.... :-(
Thanks
Best regards
Ingrid

[MikeTheBike]

Hi MiketheBike
Thanks a lot for your answer. However I am not really a coder so I hope you can help me a bit more. I tried searching Access Help with MdDir but nothing came up....
So if I go for option 1, which seams far the easiest, how would I tell Access what to name the folder ? I need to do this on the fly, ie when a textstring is created in my Access application ie R-07-234A....
Sorry if my questions are very basic, but I usually only work in the Access functionality, so going into VBA is a bit difficult for me.... :-(
Thanks
Best regards
Ingrid

Hi again

Well it's actually MkDir (not MdDir !).

You should jump in and get your feet wet and try thing, it's the only way to learn!

The following code will constict a new Directory (folder) stucture unter the folder with the MDB file that is running the code.

It consists of a sub procedure that takes the path you want to constuct, "splits" it in to an array containing all the individual folder names and then puts them back together one by one and, if it does not exist, creates each one in turn. This approach is necessary as MkDir will only "Make" a directory one level at a time.

Sub TEST()
    Dim NewDirectory As String
    
    NewDirectory = CurrentProject.Path & "\New Folder\New Sub Folder"
    
    CreateDirectory NewDirectory
End Sub

Sub CreateDirectory(ByVal strPath As String)
    Dim Folders() As String
    Dim ThisFolder As String
    Dim i As Integer
    
    Folders = Split(strPath, "\")
    
    For i = 1 To UBound(Folders)
        If i = 0 Then
            ThisFolder = Folders(i)
        Else
            ThisFolder = ThisFolder & "\" & Folders(i)
        End If
        If Dir(ThisFolder, vbDirectory) = "" Then MkDir ThisFolder
    Next i
End Sub

Hope that makes some sense, but copy the code, paste it in to a code module (not a form), put the cursor in the TEST sub and press F5, and see what happens.

If you need more info on 'Split' etc. try Access Help first, then try the forum again.


Best of luck!


MTB

[olseni]

Hi MTB
Thanks a lot, I will try your code.

And sorry abiut the MdDir typo, I did actually search on MkDir and nothing comes out (just tried it again), but maybe there are different help files levels and I only have the standard one that came with Office and not a developer one (if such a thing exists).

Anyway, thanks, I will try and see if I can get this thing going....
Best regards
Ingrid