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

    here is the entirety of what I've done, which includes the import, the subsequent processing of those imported tables, and then the eventual export of a query and two tables into a spreadsheet.

    Problem: Shouldn't the import lines of code (acTransferText) be overwriting the tables it creates each time? It does not appear to be doing so... I can insert that in somewhere else, but it was my impression that this should be overwriting, not appending. What setting should be changed?

    Code:
    Public Sub ImportProcessExportCSVFiles()    On Error GoTo ErrHandler
        Const conVEND = "Vendor"
        Const conVOCHR = "Voucher"
    
    
        '*** change the table names to the correct tables
        Const contblVEND = "tbl_Temp_Vendor"
        Const contblVOCHR = "tbl_Temp_Voucher"
    
    
        '*** change the ImportSpec names to the correct names
        Const conVEND_ImpSpec = "Vendor_Import_Spec"
        Const conVOCHR_ImpSpec = "Voucher_Import_Spec"
    
    
        Dim d As DAO.Database
        Dim r As DAO.Recordset
        Dim fd As FileDialog
        Dim sSQL As String
        Dim strFilter As String
        Dim RC As Integer
        Dim varFile As Variant
        Dim strFileName As String
        Dim lngFlags As Long
        Dim k As Integer  'counter
        Dim vPath As String
    
    
        k = 0
        Set d = CurrentDb
    
    
        ' --------------  import the CSV files --------------
        '
        'open record set (query)
        sSQL = "SELECT tblImportFileNames.txtFileName FROM tblImportFileNames ORDER BY txtFileName;"
        Set r = d.OpenRecordset(sSQL)
    
    
        'ensure that there are file names in the record set
        If Not r.BOF And Not r.EOF Then
            r.MoveLast
            r.MoveFirst
    
    
            'loop through the filenames in the table
            Do While Not r.EOF
                k = k + 1  'counter
                strFileName = r!txtFileName    'get path/filename from record set
                Select Case True
                    Case InStr(strFileName, conVEND) > 0
                        '****** comment out the MSGBOX lines and uncomment the DoCmd lines  *****
                        'MsgBox r!txtFileName
                          DoCmd.TransferText acImportDelim, conVEND_ImpSpec, contblVEND, strFileName, True
                    Case InStr(strFileName, conVOCHR) > 0
                        'MsgBox r!txtFileName
                          DoCmd.TransferText acImportDelim, conVOCHR_ImpSpec, contblVOCHR, strFileName, True
                    Case Else
                        MsgBox "Invalid import file"
                        k = k - 1  'counter
                End Select
                r.MoveNext
            Loop
    
    
            MsgBox "Done" & "   -  " & k & " files imported"
            
            DoCmd.SetWarnings False
            DoCmd.OpenQuery ("qry_del_tbl_vendor_all_rows")
            DoCmd.OpenQuery ("qry_del_tbl_voucher_all_rows")
            DoCmd.OpenQuery ("qry_append_tbl_vendor")
            DoCmd.OpenQuery ("qry_append_tbl_voucher")
            DoCmd.SetWarnings True
            
            'MsgBox "queries run successfully"
            
            vPath = Left(strFileName, InStrRev(strFileName, "\"))
    
    
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qry_VenderVoucher_MatchCheck", vPath & "TEST_LoadingQC", True, "Vendor-Voucher Match"
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "tbl_Vendor", vPath & "TEST_LoadingQC", True, "Vendor File"
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "tbl_Voucher", vPath & "TEST_LoadingQC", True, "Voucher Level1"
            MsgBox "Finished exporting SLQC spreadsheet to " & vPath
        Else
            MsgBox "No files found to import!!"
        End If
    
    
    
    
    ExitHere:
        'clean up
        On Error Resume Next
        r.Close
        Set r = Nothing
        Set d = Nothing
        Exit Sub
    
    
    ErrHandler:
        MsgBox Err.Description & "  " & Err.Number
        Resume ExitHere
    
    
    End Sub

  2. #17
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Pretty good on the changes.

    In the "Public Sub Export()" code (Post #15), just a couple of things to be aware of.
    In this section of code
    Code:
    ExitHere:
         Exit Sub
    you should close the record set and set two objects to nothing.
    Code:
    ExitHere:
        r.Close
        Set r = Nothing
        Set d = Nothing
        Exit Sub

    In the "DoCmd.TransferSpreadsheet" lines, I would add the extension to the file name argument: .... vPath & "TEST_LoadingQC.xlsx"


    Also in the "DoCmd.TransferSpreadsheet" lines, the Range argument is optional for importing, but MUST not be used when exporting.
    From HELP:
    Range Optional Variant A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.



    Problem: Shouldn't the import lines of code (acTransferText) be overwriting the tables it creates each time? It does not appear to be doing so
    I didn't know if the existing data should be deleted before importing, so I guessed wrong and didn't delete the data before importing.
    I added code to also delete the records in the Vendor and Voucher tables when you click on the Delete button or close the form.



    I merged the changes into the attached dB
    Attached Files Attached Files

  3. #18
    rdougherty is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    Thank you for the additional advice/warnings.

    you should close the record set and set two objects to nothing.
    I believe this was already implemented, but I will double-check it is good to go.

    In the "DoCmd.TransferSpreadsheet" lines, I would add the extension to the file name argument: .... vPath & "TEST_LoadingQC.xlsx"
    This previously caused the file to elicit the message "This file does not appear to match the file extension..." from Excel when opening. I determined that it is because Access is assuming I want an ".xlsb" extention (binary). So, I hve simply allowed it to use that extension (by not forcing an extention, it is using .xlsb). It shouldn't matter to the requester whether it is .xlsx or xlsb

    Also in the "DoCmd.TransferSpreadsheet" lines, the Range argument is optional for importing, but MUST not be used when exporting.
    This does not appear to be the case. After reading someone post on another site, that one cannot name the individual sheets of an exported spreadsheet, and that the default name of the object being exported must be accepted, I found another post somewhere indicating the opposite - that putting the desired name in quotes at the end (is this the range argument?) allows this type of renaming. It does appear to work in the test exports I've done thus far...

    Thanks Again!

  4. #19
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Thanks, good to know!
    I always specify the extension (.xlsx) . (am I anal???? Welllll... yes! )
    And I have never use the range argument, so I was going by what HELP says.

    Good luck with your project...

  5. #20
    rdougherty is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    one more small question (i hope). how do you make the file picker dialog default to the most recent folder the user has picked? For instance, I'd like to remove the annoying behavior of having to navigate to the folder to pick the 2nd file, after picking the first. can it just remember what we chose?

    EDIT: I also need to look at chaning the process to overwrite the temp tables, rather than emptying and repopulating them each time... is there an easy way to tweak the existing code to accomplish?
    EDIT2: Nevermind that last edit... that is not a requirement at this time. But please answer first question if someone knows a good way to do this.
    Last edited by rdougherty; 02-03-2017 at 02:17 PM.

  6. #21
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    There are many ways.
    One method would be to have a table with one field to store the last folder path. The code would get the path before the picker opens.
    (see attached)


    Edit question: If you are talking about deleting the temp tables and re-creating them, that is a bad idea - it leads to dB bloat (and possibly dB corruption).
    The proper method is to delete the records, then repopulate (append) new records.
    Attached Files Attached Files

  7. #22
    rdougherty is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    I am having the DB empty tables and compact upon closing each time (anything wrong with that from a design perspective?) <-- note, the user will always want to start fresh each time, and may bloat the db from repeated deletes or is that not the case?

    On the issue of assigning a default path equal the last one chose, a more precise way to word my request would be "after the user has picked the first file, I would like to prevent them from needing to navigate to that same folder, where they will ALWAYS find their second file. This will be a different location each occasion, and will NEVER be the project path (location of DB)"

    So, with that in mind, couldn't I use the following to find the path of the first file, and then change InitialPath to vPath after the first file is chosen?

    Code:
    vPath = Left(strFileName, InStrRev(strFileName, "\"))

  8. #23
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I am having the DB empty tables and compact upon closing each time (anything wrong with that from a design perspective?)
    In A2003 and earlier versions, the "Compact on Close" was a known cause of corruption. As I remember, it didn't happen often, but once is once to often, so I have never used that "feature".

    and may bloat the db from repeated deletes or is that not the case?
    It is not the deleting of records that causes bloat, it is the adding of records. When you delete records, Access does not recover the space the records were occupying. Depending on activity, I usually (manually) compact my dBs about monthly.


    So, with that in mind, couldn't I use the following to find the path of the first file, and then change InitialPath to vPath after the first file is chosen?
    Depends on your process. If you pick a file, then process it and the routine ends, the path will be lost.
    Or you could pick both files, but then you have to store the file names to use later.
    So, on one hand you pick a file, process it then pick the 2nd file and process, on the other hand you have 4 fingers and a thumb.....
    No, that is not what I meant!
    Or you pick both files, then process them. Either way, you will be saving a name or two. It depends on how you want the process to happen:save the path or save the file names selected.

  9. #24
    rdougherty is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    Quote Originally Posted by ssanfu View Post
    In A2003 and earlier versions, the "Compact on Close" was a known cause of corruption. As I remember, it didn't happen often, but once is once to often, so I have never used that "feature".
    Noted, and "feature" deactivated.

    It is not the deleting of records that causes bloat, it is the adding of records. When you delete records, Access does not recover the space the records were occupying. Depending on activity, I usually (manually) compact my dBs about monthly.
    Thanks for explanation.


    Depends on your process. If you pick a file, then process it and the routine ends, the path will be lost.
    Or you could pick both files, but then you have to store the file names to use later.
    So, on one hand you pick a file, process it then pick the 2nd file and process, on the other hand you have 4 fingers and a thumb.....
    No, that is not what I meant!
    Or you pick both files, then process them. Either way, you will be saving a name or two. It depends on how you want the process to happen:save the path or save the file names selected.
    Please instruct me on where to insert the vPath re-definition step into this existing code (so graciously provided earlier ) No need to alter the db file, just point at the correct place to insert, please.

    Code:
    Public Sub GetVenVouFName(pVendorVoucher As String)
    
        Dim fd As FileDialog
        Dim sSQL As String
        Dim strFilter As String
        Dim varFile As Variant
        Dim strFileName As String
        Dim lngFlags As Long
        Dim InitalPath As String
    
    
        '***** here you can set a default path (as a string) **********************
        InitalPath = "M:\ANALYST_IPC\..TI File Repository"     'example ->   "C:\accmdb"
        '    Debug.Print InitalPath
        '********************************************************
    
    
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    
        With fd
            .Title = "Hello! Pick " & pVendorVoucher & " file!"    '1) To set the caption of the dialog box, set the Title property
            .InitialFileName = InitalPath       '2) Set the oddly named InitialFileName property to determine the initial folder selected
            '        .InitialFileName = CurrentProject.Path        '2) Set the oddly named InitialFileName property to determine the initial folder selected
            .InitialView = msoFileDialogViewDetails        '3) Set the InitialView property to control how your files appear on screen (as a list, icons, etc.)
            .Filters.Clear        '4) To set the filters (you can have as many as you like) first clear any existing ones, then add them one by one
            .AllowMultiSelect = False
            .Filters.Add "CSV Files", "*.csv"
            .FilterIndex = 1        ' if there's more than one filter, you can control which one is selected by default
            .ButtonName = "Import CSV files"        '5) Set the ButtonName property to control the text on the button
            If .Show = -1 Then
                strFileName = .SelectedItems(1)
    
    
                'add file selected to the table.
                If InStr(strFileName, pVendorVoucher) > 0 Then
                    sSQL = "INSERT INTO tblImportFileNames (txtFileName)"
                    sSQL = sSQL & " VALUES( '" & strFileName & "');"
                    '  Debug.Print sSQL
                    CurrentDb.Execute sSQL, dbFailOnError
                Else
                    MsgBox "Not a " & pVendorVoucher & "  file. Please select a " & pVendorVoucher & "  file."
                End If
            Else
                MsgBox "You chose cancel"     'didn't choose anything (clicked on CANCEL)
                Exit Sub
            End If
        End With
    
    
    ExitHere:
        'clean up
        On Error Resume Next
        Exit Sub
    
    
    ErrHandler:
        MsgBox Err.Description & "  " & Err.Number
        Resume ExitHere
    
    
    End Sub

  10. #25
    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
    On the issue of assigning a default path equal the last one chose, a more precise way to word my request would be "after the user has picked the first file, I would like to prevent them from needing to navigate to that same folder, where they will ALWAYS find their second file. This will be a different location each occasion, and will NEVER be the project path (location of DB)"
    This is taken care of in the dB I posted in Post #21 (ImportCSVDemo4.zip).
    Looking at the code you will see (in BLUE):
    Code:
    Public Sub GetVenVouFName(pVendorVoucher As String)
    
        Dim fd As FileDialog
        Dim sSQL As String
        Dim strFilter As String
        Dim varFile As Variant
        Dim strFileName As String
        Dim lngFlags As Long
        Dim InitalPath As String
    
        '***** here you can set a default path (as a string) **********************
        '    InitalPath = CurrentProject.Path      'example ->   "C:\accmdb"
        '    Debug.Print InitalPath
        '********************************************************
        InitalPath = GetLastPath()
    
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    <snip>
    this retrieves the last saved path from a table. So the first time you search for a path, if there is not a saved path in the table, it defaults to the current project path. After you select a file, the path is saved to the table. The next time you go to pick a file, it uses the saved path in the table.

    The code that gets the saved path is also in the dB, but here it is again:
    Code:
    Public Function GetLastPath() As String
    '----------------------------------------------
    ' get the default (last saved) path to the CSV files
    '----------------------------------------------
        Dim r As DAO.Recordset
    
        Set r = CurrentDb.OpenRecordset("SELECT tblLastPath.LastPath FROM tblLastPath; ")
        If r.BOF And r.EOF Then
            GetLastPath = CurrentProject.Path & "\"
            'no path saved so save the CurrentProject Path as the default
            CurrentDb.Execute "INSERT INTO tblLastPath ( LastPath ) VALUES ( '" & GetLastPath & "');", dbFailOnError
        Else
            GetLastPath = r("LastPath")
        End If
    
        r.Close
        Set r = Nothing
    End Function

  11. #26
    rdougherty is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    thanks for the pointers...

    FYI, I did end up going another route to get to a similar destination. This has the behavior of claring the tbllastpath each time one closes the form, and then after one file is picked, storing the path in said table. Then the second file sees there is something already there and uses it. If nothing is there, it defaults to a standard folder where all of the subfolders are housed.

    I think the blue bits are all of the additions/changes - thanks again for your help learning VBA in this very roundabout way! (it is how I learn best actually)
    Code:
    Public Sub GetVenVouFName(pVendorVoucher As String)
    
        Dim fd As FileDialog
        Dim ssql As String
        Dim strFilter As String
        Dim varFile As Variant
        Dim strFileName As String
        Dim lngFlags As Long
        Dim InitialPath As String
        Dim r As DAO.Recordset
        Dim LastPath As String
        Dim vPath As String
            
        '***** here you can set a default path (as a string) **********************
        Set r = CurrentDb.OpenRecordset("SELECT tblLastPath.LastPath FROM tblLastPath; ")
        If r.RecordCount > 0 Then
                InitialPath = "tblLastPath.LastPath"
            Else
                InitialPath = "M:\ANALYST_IPC\..TI File Repository\"     'default path if none chosen by first file import button.
        End If
        '    Debug.Print initial Path
        '********************************************************
    
    
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    
        With fd
            .Title = "Hello! Pick " & pVendorVoucher & " file!"    '1) To set the caption of the dialog box, set the Title property
            .InitialFileName = Left(InitialPath, InStrRev(InitialPath, "\"))       '2) Set the oddly named InitialFileName property to determine the initial folder selected
            .InitialView = msoFileDialogViewDetails        '3) Set the InitialView property to control how your files appear on screen (as a list, icons, etc.)
            .Filters.Clear        '4) To set the filters (you can have as many as you like) first clear any existing ones, then add them one by one
            .AllowMultiSelect = False
            .Filters.Add "CSV Files", "*.csv"
            .FilterIndex = 1        ' if there's more than one filter, you can control which one is selected by default
            .ButtonName = "Import CSV files"        '5) Set the ButtonName property to control the text on the button
            If .Show = -1 Then
                strFileName = .SelectedItems(1)
                vPath = Left(strFileName, InStrRev(strFileName, "\"))
                'add file selected to the table.
                If InStr(strFileName, pVendorVoucher) > 0 Then
                    ssql = "INSERT INTO tblImportFileNames (txtFileName)"
                    ssql = ssql & " VALUES( '" & strFileName & "');"
                    '  Debug.Print sSQL
                    CurrentDb.Execute ssql, dbFailOnError
                    
                    'saves the last path used
                    CurrentDb.Execute "INSERT INTO tblLastPath ( LastPath ) VALUES ( '" & vPath & "');", dbFailOnError
                Else
                    MsgBox "Not a " & pVendorVoucher & "  file. Please select a " & pVendorVoucher & "  file."
                End If
            Else
                MsgBox "You chose cancel"     'didn't choose anything (clicked on CANCEL)
                Exit Sub
            End If
        End With
    
    
    ExitHere:
        'clean up
        r.Close
        Set r = Nothing
        On Error Resume Next
        Exit Sub
    
    
    ErrHandler:
        MsgBox Err.Description & "  " & Err.Number
        Resume ExitHere
    
    
    End Sub

  12. #27
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If it is working to your satisfaction (and you are more familiar with VBA - a bonus ), then all is good.

    Good luck with your project.....


    Ready to mark this solved?

Page 2 of 2 FirstFirst 12
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