VBA Export Specifiction - Why is this so hard? RRS feed

  • Question

  • Hi Folks -

    I am having a bear of a time trying to get an export specification to work.  I created one as I need it tab delimted.

    This works, but is commas delimted:

       DoCmd.TransferText acExportDelim, , "ExtractQuery3-NewEntity", FileName(strPath, "ExtractQuery3", "txt"), False, , 65001

    "ExtractQuery3-NewEntity" is my Query.  So, I created an export specification ("Export-ExtractQuery3")  and this logic doesn't work. What's the issue?

        DoCmd.TransferText acExportDelim, "Export-ExtractQuery3", "ExtractQuery3-NewEntity", FileName(strPath, "ExtractQuery3", "txt"), False, , 65001

    Saturday, January 11, 2020 8:54 AM

All replies

  • I suspect that to create the schema you simply selected "Text file" in the "External data" ribbon and this does not give you sufficient options to insert the required delimiter.

    Create the Schema to export as tab delimited as follows.

    1. Open your project.
    2. Note that you cannot use "Save as" to overwrite an existing Schema so best to first delete any existing ones that are not required as follows.
    3. Select "External data" ribbon
      Click "Saved Imports" icon (Yes! I know yours is an Export but select this icon).
    4. Select the "Saved Exports" tab at top of dialog.
    5. Select and delete each Schema not required (including the one you previously created and does not work.)
    6. Display the Access Navigation Pane. (F11 toggles it on and off).
    7. Click the little DropDown at the top of the Navigation Pane to display a list of options
    8. Select Table or Query (Whatever type you are exporting)
    9. Right click the name of the Table/Query to be exported.
    10. Move cursor down to Export (but don't click) and another DropDown is displayed.
    11. Move cursor across horizontally and then down to Text file and click it.
    12. You can edit the path and file name (or leave as the displayed default).
    13. The box against Export data with formatting and layout must be unchecked (should be the default)
    14. Click OK
    15. On the next screen select "Delimited"
    16. Click "Next"
    17. Select Tab option
    18. If you want field names (headers) check box against Include field names on first row.
    19. Select the required Text Qualifier (I assume "None")
    20. Click Next
    21. Edit the "Export to file" path and filename (or leave as default)
    22. Click Finish.
    23. On the next dialog check the box to "Save export steps"
    24. Edit the name (or leave as default). This is the name of the "Specification" to be used in your VBA Code.
    25. You will have an exported file (which is good because you can examine it and see if it is what you want) but the saved schema can be used for all future exports via VBA.

    Hope this helps, especially since you  have been waiting so long for a reply. Feel free to get back to me if still having problems with it. However, I should advise you that my knowledge of Access is quite limited so I might or might not be able to resolve.

    There is more information on exporting at the following link that might or might not be helpful.

    Regards, OssieMac

    Saturday, January 18, 2020 3:55 AM
  • I have been doing a little more testing and I have not been able to get the DoCmd.TransferText line working. Maybe this problem is more related to your problem then then my previous reply re creating the Specification.

    It keeps telling me that the "Specification" file does not exist. However, it does exist because I can select the "External Data" ribbon and click the "Saved Imports" / "Saved Exports" icon (BTW that icon name can toggle between "Saved Imports" and "Saved Exports" but just select correct tab when the dialog opens) and after the dialog opens I can run it from the button on the dialog.

    Anyway I have found an alternative syntax as follows where "Export-NameAndAddress" is the saved Specification Name. All of the details re the output file location are contained in the saved Specification.

    DoCmd.RunSavedImportExport "Export-NameAndAddress"

    Credit to zaxbat for the solution at the following link.

    Regards, OssieMac

    Saturday, January 18, 2020 6:07 AM