Results 1 to 6 of 6
  1. #1
    raytackettsells is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    27

    Transfer text files

    You helped me out dearly and I am using the transfer all .xls files code you referred me to. I'd like to do this with .txt files. Is there a way to just simply change this code to transfer ALL text files in a specific folder?

    Private Sub Command22_Click()

    Dim strPathFile As String, strFile As String, strPath As String
    Dim strTable As String, strBrowseMsg As String


    Dim blnHasFieldNames As Boolean

    blnHasFieldNames = False
    strBrowseMsg = "Select the folder that contains the EXCEL files:"
    strPath = "C:\Users\IA User 1\Desktop\PRODUCTIVITY"
    If strPath = "" Then
    MsgBox "No folder was selected.", vbOK, "No Selection"
    Exit Sub
    End If

    strTable = "INVOICES"
    strFile = Dir(strPath & "\*.xls")
    Do While Len(strFile) > 0
    strPathFile = strPath & "\" & strFile
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    strTable, strPathFile, blnHasFieldNames
    Kill strPathFile
    strFile = Dir()
    Loop
    End Sub

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,875
    Create a Macro that uses the TransferText command to import one of your text files (you will probably need to do it manually once to create an Import Specification to use).
    Then, convert the Macro to Visual Basic code (using the Utility found on the Macro ribbon). They, you should be able to swap out your TransferSpreadsheet action with the TransferText action.

    Also be sure to update your strFile variable so that it references the extension you are using for your text files, i.e.
    Code:
    strFile = Dir(strPath & "\*.txt")
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  3. #3
    raytackettsells is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    27
    Thank you. I did all that and it is shown below. I received an error. It highlighted the bold areas below and said I could not import this file.

    Private Sub Command0_Click()
    Dim strPathFile As String, strFile As String, strPath As String
    Dim strTable As String, strBrowseMsg As String
    Dim blnHasFieldNames As Boolean

    blnHasFieldNames = False
    strBrowseMsg = "Select the folder that contains the EXCEL files:"
    strPath = "C:\Users\IA User 1\Desktop\PRODUCTIVITY"
    If strPath = "" Then
    MsgBox "No folder was selected.", vbOK, "No Selection"
    Exit Sub
    End If

    strTable = "FTP"
    strFile = Dir(strPath & "\*.txt")
    Do While Len(strFile) > 0
    strPathFile = strPath & "\" & strFile
    DoCmd.TransferText acImportDelim, _
    strTable, strPathFile, blnHasFieldNames

    Kill strPathFile
    strFile = Dir()
    Loop
    End Sub

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,875
    Did you create an Import Specification file? If so, it doesn't look like you put it in.
    If you run the Macro to import your single text file, does it work?
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  5. #5
    raytackettsells is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    27
    I am not sure what the specification is. I'd just like to create it fixed with. It did import the single file but it was delimited as when I tried to import as fixed it asked for the specification name.

  6. #6
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,875
    Go through the Import Wizard once manually yourself. This is where you tell it how the file is delimited and the length/data type of each field.
    Just before you click "Finish" at the very end, click on the Advance Button, then "Save As" and give this Import Specfication (that is what you just created!) a name.
    This is the Import Specification name you want to use in your Import Macro.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

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

Similar Threads

  1. Dyanmic location for transfer text
    By shanmugamgsn in forum Access
    Replies: 5
    Last Post: 07-17-2012, 11:30 PM
  2. Importing Text files
    By RayMilhon in forum Programming
    Replies: 4
    Last Post: 10-10-2011, 10:54 PM
  3. Replies: 4
    Last Post: 06-14-2011, 07:19 PM
  4. Transfer Text to Unbound Form
    By DWS in forum Forms
    Replies: 3
    Last Post: 08-25-2009, 08:04 AM
  5. Transfer text
    By nshaikh in forum Import/Export Data
    Replies: 0
    Last Post: 09-11-2008, 03:27 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 - Senior Forums