Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    rdougherty is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91

    Import files, partially automated, unknown filenames but with keywords.

    Hello,

    I have a fair amount of Access experience, with a little VBA mixed in (but not much). Here is what I am hoping to do with imports, so that users will be required to perform minimal clicks to get .csv files processed.

    • Form opens upon opening database (I know how to do this)


    either

    • user select folder location of files via browse button/dialog. (prefer to select folder only)

    database uses this value chosen by the user to grab files in that folder which meet criteria of certain keywords. Ex. "39920_vendor_20161230.csv" and "39920_voucher_20161230.csv".

    or
    • two browse dialog boxes to find one of each type of file, which is an extra step but perhaps more precise.


    Then
    • I need VBA to instruct the importation of these files. I know I'd need to define the values in each of the browse dialog boxes as a variable, and then use that in the vba as object to import, but I do not have a lot of VBA experience and it has been a while since I've done any...




    Can someone please inform me of how to create the browse boxes and store those values, and also point me to VBA examples of importing data using a varying file name that is defined by the previously mentioned user selected values from the boxes?

    Any help is appreciated!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    user clicks SELECT FILE button to select file to import
    filepath is stored in textbox on the form until user click IMPORT button
    code will determine what kind of file (vendor / voucher)
    save file to generic filename that is already attached as table
    run append query to collect data

    first ,create generic files of each, they get overwritting everytime.
    these 2 files are attached as tables.
    when user selects a file, it copies it to the generic name (here its: c:\data\vendor.csv, etc)

    build 2 queries to append the external table to the main data.


    user picks the file
    Code:
    sub btnSelectFile_click()
       txtFile = UserPick1File("c:\folder\")
    end sub

    user click IMPORT DATA button to import

    Code:
    sub btnImport_click()
    dim sTarget, sQry, sName
    const kVEND = "vendor"
    const kVOU = "voucher"
    
       if IsNull(txtFile) then 
           msgbox "No File to import"
       else
           select case true
              case instr(txtFile,kVEND )>0
                    sQry = "qaAddVendor"
                    sName = kVEND
    
              case instr(txtFile,kVOU)>0
                    sQry = "qaAddVoucher"
                    sName = kVOU
    
              case else
                    msgbox "Invalid import file"
            exit sub
        end select 
            
           filecopy txtFile, "c:\data\" & sName & ".csv"
           docmd.setwarnings false
           docmd.openquery sQry
           docmd.setwarnings true
       
           msgbox sName  & "imported" 
       endif
    end sub


    put this code into a MODULE for user to pick the fule

    Code:
    Public Function UserPick1File(Optional pvPath)
    Dim strTable As String
    Dim strFilePath As String
    Dim sDialog As String, sDecr  As String, sExt As String
    
    
    '===================
    'YOU MUST ADD REFERENCE : Microsoft Office 11.0 Object Library, in vbe menu, TOOLS, REFERENCES
    '===================
    
    With Application.FileDialog(msoFileDialogFilePicker)   
        .AllowMultiSelect = False
        .Title = "Locate a file to Import"
        .ButtonName = "Import"
        .Filters.Clear
        .Filters.Add "CSV Files", "*.csv"
         '.Filters.Add "Excel Files", "*.xls;*.xlsx"
        .Filters.Add "All Files", "*.*"
        .InitialFileName = pvPath
        .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
        
            If .Show = 0 Then
               'There is a problem
               Exit Function
            End If
        
        'Save the first file selected
        UserPick1File = Trim(.SelectedItems(1))
    End With
    End Function

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I need VBA to instruct the importation of these files.
    Would you explain more about the "importation" of these files?


    Does the data from each of the CSV files go to different tables?
    Does the "importation" require any processing on a line by line basis?

    If the data from the CSV files gets put into individual tables, you could set up an Input Spec for each of the two types of CSV files (I do this).
    An example
    Code:
    DoCmd.TransferText acImportDelim, "EmpImportSpecification", "tblAddsEdits", strFileName, True
    where
    "EmpImportSpecification" is the Import Specification for a specific CSV file
    "tblAddsEdits" is the table the data is imported into
    strFileName is the variable that holds the CSV file name


    Do you have a dB already created?

  4. #4
    rdougherty is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    Thank you both for your replies. I have not yet had a chance to attempt implementation of your suggestions, but they appear to lead me in the correct direction.

    To answer Steve's questions...

    I simply need to import a vendor and voucher file and name them each something generic. Once this is accomplished, they will either overwrite existing tables or populate emptied ones (via append qry). I do not need anything to be evaluated row by row as the import takes place. I just need a nearly automated way to have a non-access dev import these two files. After this occurs, I have queries set up to parse out the data in the manner needed to transform it into a format we are currently using manual steps in Excel to complete, leaving possibility for human error (and more time).

    I do like the idea of using import specs to define parameters for each file's importation. Does the "EmpImportSpecification" param use the saved imports I create when manually importing the files, and do I merely replace this with the corresponding name? For instance, there is one column that needs to be interpreted as text that access wants to drop off leading zeros (zip code).

    Thank you both, and I will post more questions as I work on this!

  5. #5
    rdougherty is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    Quote Originally Posted by ranman256 View Post

    Code:
    '===================
    'YOU MUST ADD REFERENCE : Microsoft Office 11.0 Object Library, in vbe menu, TOOLS, REFERENCES
    '===================
    Do I need to install something special to gain access to this library of references within my version of Access? It is Office 2010 64bit. Thank you,

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Does the "EmpImportSpecification" param use the saved imports I create when manually importing the files,
    When you manually import a CSV file, there is a button, "Advanced", that allow you to create an import spec and save it with whatever name you want. You can specify what the field types are when creating the spec. You can then use the saved import spec when importing a CSV file. When specifying the field type in the spec, the field type must match the field type in the table.

    In my code, I execute a delete query, then import the CSV file data to a table. After that, I have code that processes the data.

    See HELP for the syntax for the "DoCmd.TransferText" command.

  7. #7
    rdougherty is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    ranman256 et al,

    my requirements have changed slightly... (after thinking about it more and realizing my original idea will not work flawlessly). I no longer wish to have the dialog box search for any file in the user-selected folder containing the keywords "vendor" and "voucher", but rather, have the user specify which file is the vendor file, and which is the voucher file.

    A requirement I failed to articulate previously, is that when the user selects the files, I'd like a new file to export to that location later, once the files are run through a series of queries. I would like the code on this form to not only define textfile1 and textfile2 names & locations, but also parse out of that location, the folder path of those files so that that folder path can be used later as an export path for the eventual new spreadsheet.

    Can you instruct me on how to adapt what you posted above for importing files so that it does not process one of the two (vendor or voucher) files at a time, but instead, allows for the user to first pick two files on the form dialog boxes, storing both values, and then processing them with the same "Import Files" button. I do not necessarily need the code to verify that the user selected the correct file by way of evaluating that it contains the appropriate keywords ("vendor","Voucher"). (choosing the wrong file would not irrevocably break any process, and would be immediately noticeable)

    I am also at a loss for finding the library references you mentioned earlier. can you assist with that too? I do not know if my version of Access has that library...?

    Many thanks,

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by rdougherty View Post
    I am also at a loss for finding the library references you mentioned earlier. can you assist with that too? I do not know if my version of Access has that library...?
    Open the IDE editor (ctl G), click on TOOLS/ REFERENCES.
    "Microsoft Office 11.0 Object Library" might be for Access 2007.
    In Access 2010, the reference is "Microsoft Office 14.0 Object Library"
    Don't know what your version will be......

    For the attached example db to work, you must have references set to
    "Microsoft Office xx.0 Object Library" and
    "Microsoft Scripting Runtime".
    See above on how to set the references....


    There are a few constants to set in the VBA code.
    In the database window, double click on "Module1".
    Look for
    Code:
    '*** change the table names to the correct tables
    
    '*** change the ImportSpec names to the correct names
    The table names and import specs must exist!!

    Scroll down to the "SELECT CASE" code. You will see (2 places)
    Code:
    '****** comment out the MSGBOX lines and uncomment the DoCmd lines  *****
    The code will not import until you make these changes.

    Before the above changes are made:
    If you click on the button ""Import", it will allow you to select files and see how the code works without doing the import.
    To select multiple files, hold down the control key while clicking on the file name(s).


    After changes:
    CSV files are (should be) imported...
    Attached Files Attached Files

  9. #9
    rdougherty is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    NEVERMIND>>> I see that much of this may be included in the sample DB posted above. Sorry for the errant post
    Last edited by rdougherty; 01-23-2017 at 03:23 PM. Reason: correction

  10. #10
    rdougherty is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    Okay, so after reviewing the sample DB, I do have a request to help me get this more customized, Please.

    I do not feel comfortable with the user picking both files in one dialog. I would much rather have them use one button to Pick "Vendor" file and one to pick "Voucher" file. I would like to be able to also display the path for each in its own textbox on the form so that the user can visually verify the path is correct after picking. (closing form needs to clear these values for next time)

    Import procedure should handle the file previously identified (by the corresponding dialog) as Vendor, THEN process Voucher file in similar fashion. I would therefore not need the looping portion of the file import steps...

    Could you advise and/or revise?

    Much appreciated!!!!!!!!!!!

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    What do you think about this version?
    Attached Files Attached Files

  12. #12
    rdougherty is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    thank you very much! that worked perfectly. I altered a few things for my purposes, and it works (so far) flawlessly! I'll circle back if I can't get the post-processing exported file figured out...

  13. #13
    rdougherty is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    So I have a query that needs to be exported after these files are processed. However, I need to tease out of the txtFileName field you structured, what the file path for the export is.

    Do you have any suggestions on how to accomplish this?

    It will be one query, and then the two tables we imported, being exported again to a new Workbook in three sheets.

    So, "qry_X", "tbl_Vendor", and "tbl_Voucher", all going to same place.

    Thanks,

  14. #14
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Do you want
    1) to be able to pick a export folder or
    2) to use the folder you imported from?

    If 1, see the last post at http://www.tek-tips.com/viewthread.cfm?qid=1450379

    If 2, you can save the txtFileName (has the path)
    ...a) to a hidden control on the form
    ...b) to a global variable
    ...c) to a TempVar

  15. #15
    rdougherty is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    I would like to use a temp variable, and send the new file to the same folder where the original files reside.

    My thought was to run an SQL statement finding the "first" record's txtfilename and then parse out of that what the path is to the folder. I have not used the split function before. Is that the ideal way to go about this?

    can you please give me an example of this in action?

    Thanks again,

    EDIT 2: (I mimicked what you did in the other code and figured it out.) The only thing left to do is decide where to insert this into your existing ImportFiles command... if you can confirm for me the ideal placement of this code, I'd appreciate it!

    here is what is working so far:
    Code:
    Public Sub Export()
    On Error GoTo ErrHandler
    
    
    Dim d As DAO.Database
    Dim strFileName As String
    Dim vPath As String
    Dim r As DAO.Recordset
    Dim sSQL As String
    
    
    Set d = CurrentDb
    
    
    sSQL = "SELECT tblImportFileNames.txtFileName FROM tblImportFileNames ORDER BY txtFileName;"
        Set r = d.OpenRecordset(sSQL)
    
    
    strFileName = r!txtFileName
    
    
    vPath = Left(strFileName, InStrRev(strFileName, "\"))
    'MsgBox vPath
    
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qry_VenderVoucher_MatchCheck", vPath & "test", True, "Vendor-Voucher Match"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "tbl_Vendor", vPath & "test", True, "Vendor File"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "tbl_Voucher", vPath & "test", True, "Voucher Level1"
    MsgBox "Finished exporting"
    
    
    ExitHere:
        Exit Sub
    
    
    ErrHandler:
        MsgBox Err.Description & "  " & Err.Number
        Resume ExitHere
    End Sub

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA Importing .txt Files WITH FileNames
    By jdmeagher in forum Programming
    Replies: 6
    Last Post: 12-08-2015, 03:41 PM
  2. Replies: 10
    Last Post: 06-16-2014, 08:37 AM
  3. Automated import from different Excel files
    By lugnutmonkey in forum Import/Export Data
    Replies: 4
    Last Post: 02-05-2013, 10:03 AM
  4. Import all files
    By raytackettsells in forum Import/Export Data
    Replies: 4
    Last Post: 08-10-2012, 11:59 AM
  5. Automated import
    By salisbut in forum Import/Export Data
    Replies: 2
    Last Post: 07-16-2010, 12:56 PM

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