Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    311

    Unanswered: External table is not in the expected format

    Im trying to import an excel file to acces using

    Private Sub CmdImport_Click()
    Dim fileName As String
    fileName = InputBox("Please enter the New File to import", "MichaelSoft")

    DoCmd.TransferSpreadsheet acImport, 3, _
    "NewTbl", "C:\Documents and Settings\kerr\Desktop\" & fileName & ".xls", False, "A1:j500"
    End Sub


    The xl file is a spreadsheet, but has irregular
    rows at the top and then a few lines down starts looking like a table
    i get the following error


    run-time error '3274'
    External table is not in the expected format

    is there any way to work with this without reformating the xl file? i will work with about 30 a month
    and have writen vba to handle that.

  2. #2
    Join Date
    Aug 2003
    Location
    Belgrade
    Posts
    68

    Re: External table is not in the expected format

    Did you try to modify the range you are importing from? Instead of "A1:j500", specify the range where the "real" table data is located.

  3. #3
    Join Date
    Oct 2003
    Posts
    311

    Re: External table is not in the expected format

    Originally posted by mashinovodja
    Did you try to modify the range you are importing from? Instead of "A1:j500", specify the range where the "real" table data is located.

    there may be 3 blocks of data, and i have been using import into new table and then deleting the row if isnUll(rs(7)) (will be ck # ) so i dont want to go through any work formating the file, and i dont want to do file import new. Lazy guy !! but isnt that what programing is for??

  4. #4
    Join Date
    Aug 2003
    Location
    Belgrade
    Posts
    68

    Re: External table is not in the expected format

    Are those blocks of data always of the same known size and on the same known place in your spreadsheet, or they vary in place and/or size?

    If they vary, you will have to do some programming and it may not be that simple. You will have to open your Excel spreadsheet in Access, search for blocks of contiguous data, and then import those blocks one by one in Access. All done in VBA code, no simple macros or other quick tricks, of course :-))

  5. #5
    Join Date
    Oct 2003
    Posts
    311
    always varying in size


    I will figure it out sooner or later (LATER!!) and post what i came up with


    M~

Posting Permissions

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