Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Prevent Excel Import if Column Name Not Found (2002)

    Hi!

    I'm importing data from and Excel table. The user runs an excel macro prior to import into Access. All of this works fine. If the user "forgets" to run the macro (which unfortunately does occur when new employees are hired) the worksheet will not have a column that is required. I want to prevent the import of this workbook and give the user a message telling them that the workbook was not imported. The column that needs to be present in the workbook is titled [Walmart Number]. Each workbook will have a different name.

    Thanks,
    Leesha

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

    Re: Prevent Excel Import if Column Name Not Found (2002)

    Hi Leesha,

    How does the user select the workbook to be imported, and how is the import done? Is it a manual process, or have you written code for it?

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Prevent Excel Import if Column Name Not Found (2002)

    Hi Hans,

    I wish I could take credit for the code. You wrote the import code for me this summer and it works like a charm, provided the user runs the pre-imort excel macro! Here is the import code:

    'Imports all excel files residing in designated folder

    Dim strFolder As String
    Dim strFile As String

    On Error GoTo ErrHandler

    strFolder = "C:WalmartImport"
    strFile = Dir(strFolder & "*.xls")
    Do While strFile <> ""
    DoCmd.TransferSpreadsheet _
    TransferType:=acImport, _
    SpreadsheetType:=acSpreadsheetTypeExcel97, _
    TableName:="tblmportTemp", _
    FileName:=strFolder & strFile, _
    HasFieldNames:=True, _
    Range:="Import!"
    strFile = Dir
    Loop

    Thanks!

    Leesha

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

    Re: Prevent Excel Import if Column Name Not Found (2002)

    One way to do this would be to start an invisible instance of Excel using Automation, and to open each workbook in turn and check if the required column is present before importing it.

    Another way would be to import each workbook into a new table, check if the required column if present, and only import into tblmportTemp if so. You'd have to delete the new table each time.

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Prevent Excel Import if Column Name Not Found (2002)

    Thanks for pointing me in the right direction. I'll give these both a try to see which works best/fastest.

    Leesah

Posting Permissions

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