View Poll Results: How do you rate the solution

Voters
0. You may not vote on this poll
  • Very Low

    0 0%
  • Low

    0 0%
  • Good

    0 0%
  • High

    0 0%
  • Excellent

    0 0%
Results 1 to 5 of 5
  1. #1
    Nikhil is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2017
    Posts
    2

    Extracting CSV files from access table based on unique field using VBA

    Hi, I am having an access table with various fields and thousand of records. ist field in table is mobile number which is having duplicate records However corresponding other fields are unique. What I want that basis all similar mobile number in Ist field should be exported to new CSV file with corresponding records. Sample file and out put is depicted below. Kindly help me with VBA code in access. Having trouble in decoding it. Any help would be appreciated.

    Access Table:

    Field1 Field 2 Field3 Field 4
    AAA VGB GHT GBH
    CCC FGT BDE NHG
    AAA GGT BNF NFR
    AAA BGF MKJ GFT
    CCC VFD NHJ NGF

    Output of this should be 2 CSV files with name of unique mobile number. Out out is shown below



    CSV File 1 (AAA);

    Field1 Field 2 Field3 Field 4
    AAA VGB GHT GBH
    AAA GGT BNF NFR
    AAA BGF MKJ GFT

    CSV File 2 (CCC)
    Field1 Field 2 Field3 Field 4
    CCC FGT BDE NHG
    CCC VFD NHJ NGF

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Have a form with a button on it to export. Add a hidden textbox for the mobile number. Create a query for the export in the format you want it, have as a criteria the mobile =Forms!formname!textboxname.

    Then in the OnClick event for the button:
    Code:
    Private Sub Command11_Click()
        Dim rst As Recordset, strSQL As String, FileName As String
        
        FileName = "....path....\mobile_"
        strSQL = "SELECT DISTINCT mobile FROM table1;"
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
        rst.MoveFirst
        
        Do Until rst.EOF
            Me!TextBox = rst!mobile
            DoCmd.TransferText acExportDelim, "specname", "queryname", FileName & rst!mobile & ".csv", True
            rst.MoveNext
        Loop
        
    End Sub

  3. #3
    Nikhil is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2017
    Posts
    2
    Hi,
    Without using forms, Is it possible to do it using VBA macro by writing in module window and then running that macro directly . I am using access tables and queries

    Thks
    Nikhil

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Why aren't you using forms? You can open it hidden if you don't want anyone to see it. Otherwise you are going to have to create the query in VBA for each separate mobile.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I agree with aytee111, it would be much easier using a form. Not only exporting the data, but starting the execution of the code.


    Since you don't want to use a form, your options are to use code to create a text file and write each record to the file, then close the text (csv) file.
    Or you can create another table with the identical structure of the main table. For each mobile number, you would append the records to the duplicate table, then export the data using "Docmd.TransferText" as in aytee111's first example.

    And both options require more code.

    Here is the code for the duplicate table option. (this is a no form option)
    Create a duplicate table to hold the records for the mobile numbers. The data gets deleted each time the mobile number changes.
    The code saves the CSV files in a folder named "CSV", which is a subfolder of the currentproject path. If the folder doesn't exist, it is created.
    There are a lot of code examples that allow you to select the forlder to save the CSV files..... you'll have to add this option.

    Add the following code to a standard module:
    Code:
    Option Compare Database
    Option Explicit
    
    Public Sub ExportByMobile()
        Dim d As DAO.Database
        Dim s As DAO.Recordset
        Dim sSQL As String
        Dim sPath As String
        Dim tmp As String
        Dim FileName As String
    
        Set d = CurrentDb
    
        'save path
        sPath = CurrentProject.Path & "\CSV"
    
        tmp = Dir(sPath, vbDirectory)
        'if dir doesn't exist, create it
        If tmp = "" Then
            MkDir sPath
        End If
    
        'select all unique mobile numbers
        sSQL = "SELECT DISTINCT Field1"
        sSQL = sSQL & " FROM MainTable"
        sSQL = sSQL & " ORDER BY Field1;"  'Field1 is the mobile number
    
        Set s = d.OpenRecordset(sSQL)
        'check for records
        If Not s.BOF And Not s.EOF Then
            s.MoveLast
            s.MoveFirst
    
            Do While Not s.EOF
                'delete any existing records in tblMobile
                d.Execute "DELETE * FROM tblMobile;"
    
                'append records
                sSQL = "INSERT INTO tblMobile ( Field1, Field2, Field3, Field4 )"
                sSQL = sSQL & " SELECT Field1, Field2,"
                sSQL = sSQL & " Field3, Field4"
                sSQL = sSQL & " FROM MainTable"
                sSQL = sSQL & " WHERE Field1= '" & s!field1 & "';"
    '            Debug.Print sSQL
                d.Execute sSQL, dbFailOnError
    
                DoCmd.TransferText acExportDelim, , "tblMobile", sPath & "\mobile_" & s!field1 & ".csv", True
    
                s.MoveNext
            Loop
        End If
    
        s.Close
        Set s = Nothing
        Set d = Nothing
    
        MsgBox "Done! CSV files saved in " & vbNewLine & vbNewLine & sPath
    End Sub
    Since you didn't provide the real table & field names, you will need to edit the code (in BLUE) change them to your table/field names.


    Code without using a form is quite a bit longer, isn't it???

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

Similar Threads

  1. extracting pdf files question
    By slimjen in forum Programming
    Replies: 3
    Last Post: 03-11-2015, 08:45 AM
  2. Replies: 7
    Last Post: 08-20-2014, 03:00 AM
  3. Export Access into multiple excel files based on field value
    By turntabl1st in forum Import/Export Data
    Replies: 7
    Last Post: 11-08-2012, 12:43 PM
  4. Replies: 5
    Last Post: 07-06-2012, 03:22 PM
  5. Replies: 1
    Last Post: 08-18-2010, 02:36 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