Results 1 to 7 of 7
  1. #1
    aemara is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2014
    Posts
    23

    Using docmd.transferspreadsheet function on table with a few null spaces gives unexpected results

    I'm using VBA to import a table from Excel into Access using the Docmd.transferspreadsheet function. The table looks like this:

    alpha numbers
    A 1
    B 2
    ...
    J 9

    So in total I have 10 records. When I read in this table, setting HasFieldNames = True/False works as expected.


    However, if I introduce spaces into the table (ie remove a couple of letters and numbers) then I read in this table and specify HasFieldNames = True, the table is not read in properly. All the content is read in but the numbers are no longer in line with the letters, and you have a random order within the columns themselves.
    It works fine when HasFieldNames = False.

    Does anyone know why it is acting like this and whether it can be fixed?

    Any input is appreciated.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Excel is not that smart, so it makes guesses.
    If the 1st few records in a field can be determined as numbers it will convert to numbers even tho the rest is actuall string.

    To ensure data integrity, i run a macro to put the string char (single quote) on every record in those colulmns that MUST be string.
    Then it ALWAYS imports corretly

    Code:
    Sub Cvt2Txt()
    range("A2").select
    While ActiveCell.Value <> ""
       vTxt = ActiveCell.Value
       If Left(vTxt, 1) <> "'" Then ActiveCell.Value = "'" & vTxt
       ActiveCell.Offset(1, 0).Select   'next row
    Wend
    End Sub

  3. #3
    aemara is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2014
    Posts
    23
    What I don't understand is that even if Excel interprets the rest of the records as numbers, why is the table imported just jumbled up? The spaces are imported as well

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Is it a true .xlsx file, or csv?

  5. #5
    aemara is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2014
    Posts
    23
    .xlsm because it is macro enabled; I have a macro in there to do some copy/pasting.

  6. #6
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Not the macro..., the data. Is it in an xls or csv? It doesnt come in xlsm.

  7. #7
    aemara is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2014
    Posts
    23
    in that case xls

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Unexpected results on Inner Join
    By Access_Novice in forum Access
    Replies: 2
    Last Post: 09-05-2014, 12:50 AM
  2. Replies: 1
    Last Post: 01-22-2013, 03:42 PM
  3. Unexpected results with DAO recordset
    By GraeagleBill in forum Programming
    Replies: 1
    Last Post: 10-07-2012, 07:37 PM
  4. Unexpected Results from Curdir?
    By bginhb in forum Programming
    Replies: 6
    Last Post: 08-17-2011, 03:58 PM
  5. Query showing unexpected results
    By johnmerlino in forum Queries
    Replies: 30
    Last Post: 10-25-2010, 07:08 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums