Results 1 to 8 of 8
  1. #1
    dakpluto is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    18

    Exporting by unique values, and name/folder by cell contents

    ok, this might be a tough one, and I need some help.






    OK, I have a database of about 120,000 elements that I need split up based on the contents of a column (for purposes say the table is named Accounts and the column is called Routes). The column in question is in the following format:


    xxx-xxxxxxxx


    The x are all integers. I need to export the rows in an excel sheet by the unique values in this column. (There is about 650 unique values.) In addition they need to be saved in the following format:


    C:\example\(the three numbers on the left, before the dash)\(the first five numbers after the dash)\(the 8 numbers after the dash).xlsx


    I'm sure this is possible in VBA, but I'm not familiar enough with VBA to write it out myself, and I really don't want to have to do 600+ query and 600+ saved exports.


    Any help on this would be greatly appreciated.

  2. #2
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    There are a few different ways to go about it, but I'd say you should first make a Group By query that just gets the values from that field so that you only get the unique values.

    From there you can open the query as a recordset and then run another query on each value to just get the results from your Accounts and export them to an Excel. You will also have to use the FSO resources to manipulate your file system. You can use the Mid function to parse out each folder you need to create and the filename. You will also need to create a placeholder query that you can do the Excel export from. So just make a query called qryTemp and save it. Doesn't matter what you initially put in it, we'll be changing the SQL in code.
    Code:
    dim db as database
    dim rs as recordset
    dim FSO as object
    dim rootFolder as string
    dim subFolder as string
    dim fileName as string
    
    set FSO as CreateObject("Scripting.FileSystemObject")
    set db = currentdb
    set rs = db.openrecordset("SELECT Routes FROM Accounts GROUP BY Routes", dbOpenDynaset, dbSeeChanges)
    
    do while not rs.EOF
      rootFolder = mid(rs!Routes, 0, 3) & "\"
      subFolder = mid(rs!Routes, 5, 5) & "\"
      fileName = mid(rs!Routes, 5, 8)
      if not FSO.FolderExists("c:\Example\" & rootFolder & subFolder) then FSO.CreateFolder("c:\Example\" & rootFolder & subFolder)
      if FSO.FileExists("c:\Example\" & rootFolder & subFolder & fileName) then FSO.DeleteFile("c:\Example\" & rootFolder & subFolder & fileName, True)
      db.QueryDefs("qryTemp").SQL = "SELECT * FROM Accounts WHERE Routes = '" & rs!Routes & "'"
      docmd.OutputTo acOutputQuery, "qryTemp", acFormatXLS, "c:\Example\" & rootFolder & subFolder & fileName, False
      rs.movenext
    loop
    set db = nothing
    set rs = nothing

  3. #3
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    Did this work dakpluto?

  4. #4
    dakpluto is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    18
    Quote Originally Posted by Xipooo View Post
    Code:
    dim db as database
    dim rs as recordset
    dim FSO as object
    dim rootFolder as string
    dim subFolder as string
    dim fileName as string
    
    set FSO = CreateObject("Scripting.FileSystemObject")
    set db = currentdb
    set rs = db.openrecordset("SELECT Routes FROM Accounts GROUP BY Routes", dbOpenDynaset, dbSeeChanges)
    
    do while not rs.EOF
      rootFolder = mid(rs!Routes, 0, 3) & "\"
      subFolder = mid(rs!Routes, 5, 5) & "\"
      fileName = mid(rs!Routes, 5, 8)
      if not FSO.FolderExists("c:\Example\" & rootFolder & subFolder) then FSO.CreateFolder("c:\Example\" & rootFolder & subFolder)
      if FSO.FileExists("c:\Example\" & rootFolder & subFolder & fileName) then FSO.DeleteFile("c:\Example\" & rootFolder & subFolder & fileName, True)
      db.QueryDefs("qryTemp").SQL = "SELECT * FROM Accounts WHERE Routes = '" & rs!Routes & "'"
      docmd.OutputTo acOutputQuery, "qryTemp", acFormatXLS, "c:\Example\" & rootFolder & subFolder & fileName, False
      rs.movenext
    loop
    set db = nothing
    set rs = nothing
    Getting a syntax error, expected = on this one:

    if FSO.FileExists("c:\Example\" & rootFolder & subFolder & fileName) then FSO.DeleteFile("c:\Example\" & rootFolder & subFolder & fileName, True)

    I also corrected:
    set FSO as CreateObject("Scripting.FileSystemObject")
    to the correct:
    set FSO = CreateObject("Scripting.FileSystemObject")

  5. #5
    dakpluto is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    18
    OK, fixed the above, I just simply removed the , TRUE and it compiled.

    My new issue, I'm getting a RunTime error 76 Path not found on the CreateFolder method.

  6. #6
    dakpluto is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    18
    Worked out the issues! Problem is CreateFolder method needs to run and do checks for every folder level, so I just created the rootFolders manually since there wasn't that many to do. Works like a charm now.

    my working code:

    Code:
    Public Function manySheets()
        Dim db As Database
        Dim rs As Recordset
        Dim FSO As Object
        Dim rootFolder As String
        Dim subFolder As String
        Dim fileName As String
        
        Set FSO = CreateObject("Scripting.FileSystemObject")
        Set db = CurrentDb
        Set rs = db.OpenRecordset("SELECT Routes FROM Accounts GROUP BY Routes", dbOpenDynaset, dbSeeChanges)
        
        Do While Not rs.EOF
          rootFolder = Mid(rs!Routes, 1, 3)
          subFolder = Mid(rs!Routes, 5, 5)
          fileName = Mid(rs!Routes, 5, 8)
          If Not FSO.FolderExists("C:\Example\" & rootFolder & "\" & subFolder) Then
            FSO.CreateFolder ("C:\Example\" & rootFolder & "\" & subFolder)
        End If
          If FSO.FileExists("C:\Example\" & rootFolder & "\" & subFolder & "\" & fileName) Then
            FSO.DeleteFile ("C:\Example\" & rootFolder & "\" & subFolder & "\" & fileName)
        End If
          db.QueryDefs("qryTemp").SQL = "SELECT * FROM Accounts WHERE Routes = '" & rs!Routes & "'"
          DoCmd.OutputTo acOutputQuery, "qryTemp", acFormatXLS, "C:\Example\" & rootFolder & "\" & subFolder & "\" & fileName & ".xls", False
          rs.MoveNext
        Loop
        Set db = Nothing
        Set rs = Nothing
    
    
    End Function

  7. #7
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    Glad to hear it worked out for you.

    Sorry about the untested code. It happens when we don't have a genuine development environment to code out of. We have to do it from memory and mine is pretty labored as it is.

  8. #8
    dakpluto is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    18
    Quote Originally Posted by Xipooo View Post
    Glad to hear it worked out for you.

    Sorry about the untested code. It happens when we don't have a genuine development environment to code out of. We have to do it from memory and mine is pretty labored as it is.
    Haha, no problem. the little untested things never bother me. Hell, I learn more from those kinds of things than anything else sometimes.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 01-20-2014, 11:50 AM
  2. Query contents are deleted after exporting the query results
    By alfcee in forum Import/Export Data
    Replies: 6
    Last Post: 11-13-2012, 09:35 AM
  3. Replies: 5
    Last Post: 05-22-2012, 08:06 AM
  4. Unique values
    By helpaccess in forum Queries
    Replies: 3
    Last Post: 09-19-2011, 03:46 PM
  5. List all values in one cell?
    By Remster in forum Queries
    Replies: 5
    Last Post: 12-17-2010, 04:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums