Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    I have records in a database, where I need to use the fields [PROPNUMR], [STREETNAME] and [AREA] to create folders on a network drive.

    How can I automate this procedure with vba, as there will be 300 / 400 entries to do in one go and then on a regular basis, need to create the odd folder from vba automatically on a click event.
    Best Regards,

    Luke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can use MkDir to create a folder:

    Dim strFolderPath As String
    strFolderPath = ...
    MkDir strFolderPath

    If you want to create a folder for each record in a table, you can open a (DAO or ADODB) recordset on the table and loop through the records.

  3. #3
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='783954' date='10-Jul-2009 11:32']You can use MkDir to create a folder:

    Dim strFolderPath As String
    strFolderPath = ...
    MkDir strFolderPath

    If you want to create a folder for each record in a table, you can open a (DAO or ADODB) recordset on the table and loop through the records.[/quote]

    Hans,

    How do you loop through?
    Best Regards,

    Luke

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Here is an example using DAO; you need to set a reference to the Micorosoft DAO 3.6 Object Library in Tools | Options... in the Visual Basic Library.

    Code:
    Sub LoopExample()
      Dim dbs As DAO.Database
      Dim rst As DAO.Recordset
     
      On Error GoTo ErrHandler
    
      Set dbs = CurrentDb
      Set rst = dbs.OpenRecordset("tblSomething", dbOpenDynaset)
      Do While Not rst.EOF
    	' code to do something with the record goes here
    	...
    	rst.MoveNext
      Loop
    
    ExitHandler:
      On Error Resume Next
      rst.Close
      Set rst = Nothing
      Set dbs = Nothing
      Exit Sub
    
    ErrHandler:
      MsgBox Err.Description, vbExclamation
      Resume ExitHandler
    End Sub

  5. #5
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    When I run the following code I get errors, but the error message box is blank.


    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strFolderPath As String

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("qryCreateFolder", dbOpenDynaset)
    Do While Not rst.EOF

    strFolderPath = "\\SERVER\Admin\Properties\" & PROPNUMR & " " & STREETNAME & "," & AREA
    MkDir strFolderPath
    rst.MoveNext
    Loop

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    msgbox Err.Description, vbExclamation
    Resume ErrHandler

    There are two error message box's that appear, the final one that is not blank is attached
    Attached Images Attached Images
    Best Regards,

    Luke

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I apologize, the line

    Resume ErrHandler

    should have been

    Resume ExitHandler

    I have corrected my previous reply.

    The line

    strFolderPath = "\\SERVER\Admin\Properties\" & PROPNUMR & " " & STREETNAME & "," & AREA

    should be changed to

    strFolderPath = "\\SERVER\Admin\Properties\" & rst!PROPNUMR & " " & rst!STREETNAME & "," & rst!AREA

    The code will only work if the parent folder \\SERVER\Admin\Properties already exists.

  7. #7
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Okay, got this to work well now.

    I have fields for each record that refer to file locations for images and pdf's. What I want to do it move this files from the existing location that is the file location currently stored in the record, to the new folder reference for each property:

    strFolderPath = "\\SERVER\Admin\Properties\" & rst!PROPNUMR & " " & rst!STREETNAME & "," & rst!AREA

    Then once this has happened update the field for the new file path.

    I have for example a field called PropHip that I want to do it on.

    Any suggestions?
    Best Regards,

    Luke

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The code could look like this:

    Code:
    Sub LoopExample2()
      Dim dbs As DAO.Database
      Dim rst As DAO.Recordset
      Dim strFolderPath As String
      Dim strFileName As String
      Dim strOldPath As String
      Dim strNewPath As String
      Dim intPos As String
    
      On Error GoTo ErrHandler
    
      Set dbs = CurrentDb
      Set rst = dbs.OpenRecordset("...", dbOpenDynaset)
      Do While Not rst.EOF
    	strFolderPath = "\\SERVER\Admin\Properties\" & _
    	  rst!PROPNUMR & " " & rst!STREETNAME & "," & rst!AREA
    	' Get current path+filename
    	strOldPath = rst!PropHip
    	' Extract filename
    	intPos = InStrRev(strOldPath, "\")
    	strFileName = Mid(strOldPath, intPos + 1)
    	' Assemble new path+filename
    	strNewPath = strFolderPath & "\" & strFileName
    	' Move file
    	Name strOldPath As strNewPath
    	' Update the record
    	rst.Edit
    	rst!PropHip = strNewPath
    	rst.Update
    	rst.MoveNext
      Loop
    
    ExitHandler:
      On Error Resume Next
      rst.Close
      Set rst = Nothing
      Set dbs = Nothing
      Exit Sub
    
    ErrHandler:
      MsgBox Err.Description, vbExclamation
      Resume ExitHandler
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •