Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing data (XP)

    I am trying to automate an import of a text file.

    The file is a tab delimited file and when automating it it brings the file into Access in one field. How can I tell access it is a tab delimited file in the macro/VBA code.

    As a second question (May not be for the correct forum in which case I am sorry), can I run the Access macro/VBA code from VBA in Excel.

    Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing data (XP)

    Hi Lawrence,

    Start by importing the text file interactively (File | Get External Data | Import...) When you arrive at the last step of the Import Text Wizard, don't click Finish yet, but instead click Advanced... You will see an overview of the import settings. You can save these settings as an import specification by clicking Save As... Accept the name proposed by Access, or provide another name, as you wish. Memorize or write down the name. Click OK, You can now finish or cancel the import.

    Use the name of the specification in your VBA code (substitute the appropriate names):

    DoCmd.TransferText acImportDelim, "Test Import Specification", "tblImport", "Test.txt", True

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing data (XP)

    You can control Access from Excel in two ways:

    1. You can create a macro in the Access database that imports the text file, either by using the TransferText action, or by calling a VBA routine. You can then open the database from Excel and run the macro:

    Shell """C:Program FilesMicrosoft OfficeOffice10MSAccess.exe"" ""C:AccessMyDatabase.mdb"" /x mcrImport""

    Or you can name the macro AutoExec, so that it is executed automatically when the database is opened.

    2. You can control Access from Excel in VBA; this is called Automation. Moderator <!profile=WendellB>WendellB<!/profile> has a short tutorial on Automation on his website, with useful links. You can link to his website from his profile; look for Support > Tutorials.

  4. #4
    New Lounger
    Join Date
    Jan 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing data (XP)

    Thanks for your help. Exactly what I needed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •