Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    6

    Unanswered: DoCmd.TransferSpreadsheet

    I am using this command in an Access db to import a range from an excel spreadsheet. I have an old version of the db and a new one which contains a few new fields in the import range and the table it is being imported into. For some reason, when I use the old db (CODE DID NOT CHANGE IN ANY WAY), it imports only the rows in the range that have values. When I use the newer db, it imports all of the rows in the range, whether they have data or not. In both cases, the table has the same setup (unless there is a switch I set somewhere). Here is an exmaple:

    Old Excel sheet

    Col 1 Col 2 Col 3 Col 4
    1 NEW 4 temp
    2 NEW 5 Jane
    3
    4

    When I use the below command, I only end up with two records in my table, despite having a value in Col 1

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "ImportTable", strFileName, True, "ImportArea"


    New Excel sheet

    Col 1 Col 2 Col 3 Col 4
    1 NEW 4 temp
    2 NEW 5 Jane
    3
    4

    When I use the same command from the newer db, I get 4 rows with the last two containing nothing but the Col 1 value. I don't understand why it doesn't work the same for both? My desired result is to import only the rows with data beyond column 1.

    Anyone have any ideas? The only thing that changed between the two is the recor dlayout of the import table and the columns in the excel sheet ( i added a few).

    THanks for any assistance!

    Jennifer

  2. #2
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    How is "ImportArea" set? This seems to control the range.

  3. #3
    Join Date
    Apr 2004
    Posts
    6
    Originally posted by poliarci
    How is "ImportArea" set? This seems to control the range.
    ImportArea is the range name frmo the spreadsheet. I contains the entire range which has about 275 lines, but I have no way of knowing what lines are filled in so I imported the entire range, but it used to only bring in the records (lines) that had moew then just the first column filled in.

    At any rate, I just put another step in my mdb that would run a delete query after the import to get rid of the 'blank' records. I would still like to understand why it works two different ways for me though.

    Thanks,

    Jennifer

  4. #4
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    Definitely wierd behavior. My only guess is perhaps there are spaces there. Perhaps you can verify the fields are empty? Or try exporting the data from Excel to a prn type or comma delimited may help figure that one out.

    Hmm, how about a different xfer method? xfertext and using a specification (that you could create & save) may give you the control you are after.
    Last edited by poliarci; 04-02-04 at 18:30.

Posting Permissions

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