Results 1 to 14 of 14
  1. #1
    AlexJ is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    19

    Question Browsing and Importing .csv to existing Access 2013 table with VBA

    I am attempting to put together an easy to use form that can accomplish three things:

    1. Import three different .csv files into three existing tables using "browse for file" functionality
    2. Create a new table from these imported tables that contains a combination of the three tables' data in the order I need it
    3. Export this final table to .xlsx, preferably using a "browse for save location" option but it could really just save next to the initial files


    I am capable of using macros within Access to accomplish the final table creation and data updating and linking everything to buttons on a form but I don't have the VBA experience to accomplish the importing/exporting with file browsing capabilities.


    Some important things to note:

    • Table1 (sequence) is a sequence of unique points numbers I export from CAD that are in the order I need the final .xlsx to be
    • Table2 (survey data) is survey data in PNEZD format plus a unique value that allows the linking of this table to the data table. The unique point numbers from Table1 will link with the identical numbers in Table2
    • Table3 (data table) contains several fields worth of attributes that will link to the unique value in Table2


    I have searched around quite a bit looking for someone with a similar question but haven't found anything I could implement directly. Obviously I am open to other creative solutions to solve this and flexible in the methods used. I am an intermediate Access user with little VBA experience. Any help on this would be greatly appreciated.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    AlexJ is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    19
    Thanks orange that video was extremely helpful!

    I mimicked his process as I followed along to help me understand it, then I watched a second time while building my own form to accomplish what I need but I have one question:

    How can I pull the result of my sub into the [FileName] portion of my function?

    I have so far:
    - Sub btnBrowseAllPoints_Click() that selects the .csv using a OpenFileDialog
    - Function ImportAllPoints() that should import the table after it has been selected using the above sub using a DoCmd.TransferText

    See the sub and function below:

    Code:
    Public Function ImportAllPoints()
        
        DoCmd.TransferText acImportDelim, , "tblAllPointsImport", [FileName], False
            
    End Function
    Code:
    Private Sub btnBrowseAllPoints_Click()
        Dim filter As New DialogFilter
        Dim result As OpenFileDialogResult
        
        filter.Description = "Delim. Files"
        filter.Extensions = "*.csv, *.txt"
        
        Set result = FileUtilities.OpenFileDialog("Select a file to import", False, filter)
        
        If result.Successful Then
            Me.txtFileAllPoints = result.FileName
        Else
            Me.txtErrorMessage = result.ErrorMessage
            Me.txtErrorMessage = Null
        End If
    End Sub
    There are also two Class Modules being used here:

    Code:
    Option Compare Database
    Option Explicit
    Public Description As String
    Public Extensions As String
    Code:
    Option Compare Database
    Option Explicit
    Public Successful As Boolean
    Public ErrorMessage As String
    Public FileName As String
    Very close to solving this question. Any advice would be greatly appreciated.

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    In your browse code you get a result (or not). If you do then you store the name of the file that the user selected in a text box on your form. This is now where the name of the file to import is located. You have two options - either store the name in FileName or else use the textbox instead. Also, before running the TransferText I presume you are checking for a value, in the event the user presses cancel during the browse.

  5. #5
    AlexJ is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    19
    aytee111,

    So I have three browse subs (one for each .csv) and I then have three import functions (one for each table in my database). I'm just not sure what syntax to use to get each import function will pull its result from the browse sub. The ".result" of the browse sub and the "me.txtFileAllPoints" is all very confusing to me. Keep in mind I did not come up with this from scratch so my knowledge is limited. I didn't include it above but the Function OpenFileDialog from the Module FileUtilties looks like this:

    Code:
    Public Function OpenFileDialog(Title As String, AllowMultiSelect As Boolean, DialogFilter As DialogFilter) As OpenFileDialogResult
        Dim diag As FileDialog
        Dim item As Variant
        Dim result As New OpenFileDialogResult
        
        Set diag = Application.FileDialog(msoFileDialogFilePicker)
        
        diag.AllowMultiSelect = AllowMultiSelectw
        diag.Title = Title
        diag.Filters.Clear
        diag.Filters.Add DialogFilter.Description, DialogFilter.Extensions
        
        If diag.Show Then
            For Each item In diag.SelectedItems
                result.FileName = CStr(item)
                result.Successful = True
            Next
        Else
            result.Successful = False
            result.ErrorMessage = "No file selected!"
        End If
        
        Set OpenFileDialog = result
        
    End Function

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You don't need three browse subs, they will all be the same. Also, you can have one import function.

    Something like this (I don't what your import tables names are, I just put a number on the end, 1 or 2 or 3):

    Code:
    Private Sub btnBrowseAllPoints_Click()
        
        Dim x As Integer
        
        For x = 1 To 3
    
            'file dialog/browse
            If IsNull(result.FileName) Or result.FileName = "" Then Exit For
            
            DoCmd.TransferText acImportDelim, , "tblAllPointsImport_" & x, result.FileName, False
    
        Next
    
    End Sub

  7. #7
    AlexJ is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    19
    I have the three browse subs where each one is tied to a different button on my form that results in their corresponding text box being populated with the filepath that was selected. The three import functions just pull the aforementioned files into their respective tables. The tables are tblAllPointsImport, tblTallyImport, and tblSequenceImport. I suppose splitting everything up into separate functions and subs is my way of understanding it all. I apologize for being so dense but I'm having a hard time here.

    Using all the code I have posted above, is it possible to complete what I need and pull the FileNames into my DoCmd.TransferText? Or is this project a lost cause? It is easier to understand smaller edits to my original code rather than proposing new ones. I'd like to make a few small amendments to get the import to my tables to work. The browse for each file buttons work fine and everything I am doing after the imports are complete with my queries and macros are also working fine. It's this hurdle I can't get over. I really appreciate your patience aytee11.

  8. #8
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I presume that the three buttons on the form will do the browse and the import too? In that case, in the sub btnBrowseAllPoints, add the transfer:
    If result.Successful = True Then
    DoCmd.TransferText.......(using result.FileName)
    ....

    (No need for apologies, hope I am understanding you!)

  9. #9
    AlexJ is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    19
    Yeah alright let's just work on it being one button that runs the browse, then upon successfully populating the text box with the file path it then imports that .CSV using the DoCmd.TransferText. The import function is basically one line of code anyway so it doesn't really need to be isolated does it? This accomplishes that and using the result.FileName here populated the table in my database I wanted it to. Cancelling it and clearing the filepath will still need to be worked in I suppose. Do you see any problems with doing it like I have below?

    Code:
    Private Sub btnBrowseAllPoints_Click()
        Dim filter As New DialogFilter
        Dim result As OpenFileDialogResult
        
        filter.Description = "Delim. Files"
        filter.Extensions = "*.csv, *.txt"
        
        Set result = FileUtilities.OpenFileDialog("Select a file to import", False, filter)
        
        If result.Successful Then
            Me.txtFileAllPoints = result.FileName
            DoCmd.TransferText acImportDelim, , "tblAllPointsImport", result.FileName, False
        Else
            Me.txtErrorMessage = result.ErrorMessage
            Me.txtErrorMessage = Null
        End If
    End Sub

  10. #10
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    That looks good. Not sure what you mean by cancelling/clearing, it looks like you are handling it when the user presses cancel. The code after the "Else" first puts the error message into the textbox then it clears it, that part doesn't make sense. Maybe you meant the second line to be with the successful.

  11. #11
    AlexJ is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    19
    Whoops you're correct. When you press cancel after launching the dialog box it doesn't cause any problems. So now a user can use my form to browse to and select a file, then import it to a table (assuming the file type is correct). I have a few macros and queries for opening the newly created tables and emptying them if you need to start over. After that I have some queries that combine and organize the data into a new table and validate it before it is finally exported as an .xlsx. I have many more things to learn but for the most part my questions have been answered so I will mark this thread as solved. Thanks again aytee111!

  12. #12
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You're welcome, and well done for getting the bulk of it done by yourself, you were almost there. Keep going along the VBA route, such as emptying out the tables with a DoCmd.RunSQL.

  13. #13
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In this part of the code, do you really want to set an error message, then set it to NULL???
    Code:
        Else
            Me.txtErrorMessage = result.ErrorMessage
            Me.txtErrorMessage = Null
        End If
    Am I missing something??
    Maybe have:
    Code:
    Private Sub btnBrowseAllPoints_Click()
        Dim filter As New DialogFilter
        Dim result As OpenFileDialogResult
        
        filter.Description = "Delim. Files"
        filter.Extensions = "*.csv, *.txt"
        
        Set result = FileUtilities.OpenFileDialog("Select a file to import", False, filter)
        
        If result.Successful Then
            Me.txtErrorMessage = Null
            Me.txtFileAllPoints = result.FileName
            DoCmd.TransferText acImportDelim, , "tblAllPointsImport", result.FileName, False
        Else
            Me.txtErrorMessage = result.ErrorMessage
        End If
    End Sub

  14. #14
    AlexJ is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    19
    ssanfu,

    Good catch! That would make more sense, this causes an error message to pop in on my errors text box on my form upon closing the browser without selecting a file. This was the original intent of that piece of code.

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

Similar Threads

  1. Replies: 5
    Last Post: 10-28-2016, 08:03 AM
  2. Replies: 0
    Last Post: 06-23-2016, 05:09 PM
  3. Importing records into an existing table
    By Jamescdawson in forum Import/Export Data
    Replies: 8
    Last Post: 05-18-2015, 04:05 PM
  4. Replies: 2
    Last Post: 01-28-2014, 06:14 AM
  5. Importing Excel data to an existing table
    By tonyrhills in forum Import/Export Data
    Replies: 3
    Last Post: 12-23-2011, 09:19 AM

Tags for this Thread

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