Access won't import vital Excel column.

Have you tried linking to the Excel spreadsheet, then joining on the II field?

have you tried importing the incoming sheets  to a temporary table, then do an update of the final table using the temporary table

Is the column in the spreadsheet?
Check your import spec to make sure it is not being dropped when you link or import.

Good thoughts, both.  This is part of an automated system that strips and saves email attachments named uniquely to a specified folder.  Outlook VBA then calls an Access procedure that imports and processes the same range from each .xlsm file in the folder.  The imports do go to another table temporarily to use for comparisons and updates to the actual data.  It is in this "ProcessSheets" table that the ID are not appearing--and where they need to.

Sorry I missed you, Pat. The column is right in the middle of the sheet and all the data on both sides of it is coming in.  The sheet and table structures are identical, and the actual database table field names are on the first line of the import range.  Oddly, this just started happening, and I don't know what changed.

Did you check the import spec?

I'm not sure what you mean, Pat.

I found one thing that's different, and I do have a question about it, but I think it should be on a different thread.  Stand by for incoming.

there is no import spec for importing excel files (.xls,.xlsx)

I moved the other part to https://www.experts-exchange.com/searchResults.jsp?searchTag=Access+Excel+%26+ID%3A+28606510

there is no import spec for importing excel files (.xls,.xlsx)

There is if you make one.

how?

Pat, I agree with Rey, how do you make an import spec for Excel.

You can save the steps used to do the import or export, but to the best of my knowledge, the import wizard does not have and Advanced button which allows you to create the import spec.

Here's how I'm importing:

Public Sub RequestsTurnaround()

    Dim sPath As String, xlFile As String
    sPath = "G:\XE_ECMs\IPP Sharing Development\New Requests\"
    
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)
    xlFile = Dir(sPath & "*.xlsm")

While xlFile <> ""
    Debug.Print xlFile
'Import and delete the request file
    DoCmd.SetWarnings False
    DoCmd.TransferSpreadsheet acImport, , "ProcessSheets", sPath & xlFile, True, "IPP_Request!A5:Z5000"
    'Kill sPath & xlFile
   xlFile = Dir()

Select allOpen in new window

is "ProcessSheets" an existing table?

if yes, try using a non existing table

   DoCmd.TransferSpreadsheet acImport, , "NewProcessSheets", sPath & xlFile, True, "IPP_Request!A5:Z5000"


post back the result

OK.  thanks.

OK disbelievers - pay special attention to the checkbox.

that is not an import spec, it is a manual import steps.

As you suggested, Rey, I did the import and created the table "ProcessSheets1".  Very interesting.  Only the auto fill data from the sheet header appeared; none of the operative test data imported.

can you post the result, using the OLD code

DoCmd.TransferSpreadsheet acImport, , "ProcessSheets", sPath & xlFile, True, "IPP_Request!A5:Z5000"


are the cells in  the "Import Sheet"  linked to other sheets, have formula, or lookup ?

Different, again (not seen this before)!  The number of records (with autofill values) were created, but ALL of the fields in ALL of the records were blank, except the "DateCreated" field default value Date().

i see that you are importing ".xlsm" , which means that the excel file have macros in it,
 which could mean that certain columns, cells are changed when you open the .xlsm file.?

It is macro enabled, and there are a bunch of formulas and lookups all over the sheet.  No macros run on startup and are by default disabled.

<It is macro enabled, and there are a bunch of formulas and lookups all over the sheet>
i had this problem before.
i used vba to read the "VALUES" of each cell.
how is your VBA coding?

Here's the whole ball of wax.  I think I will move clearing the ProcessSheets table to inside the loop, though.

Public Sub RequestsTurnaround()

    Dim sPath As String, xlFile As String
    sPath = "G:\XE_ECMs\IPP Sharing Development\New Requests\"
    
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)
    xlFile = Dir(sPath & "*.xlsm")

While xlFile <> ""
    Debug.Print xlFile
'Import and delete the request file
    DoCmd.SetWarnings False
    DoCmd.TransferSpreadsheet acImport, , "ProcessSheets", sPath & xlFile, True, "IPP_Request!A5:Z5000"
    DoCmd.OpenQuery "DeleteProcessSheetsBlanksQ", , acReadOnly
    DoCmd.OpenQuery "TagBatchIDQ", , acReadOnly
    'Kill sPath & xlFile
   xlFile = Dir()
'Process
    DoCmd.SetWarnings False
'Updates Archetypes with matching IPPRID
    DoCmd.OpenQuery "UpdateRequestsQ", , acReadOnly
'Updates Low IP to "Matched"
    DoCmd.OpenQuery "TagLowIPQ", , acReadOnly
'Tags requests matching by ObjectNum AND Country as "Matched"
    DoCmd.OpenQuery "MatchedRequestsQ", , acReadOnly
'Designates request NOT matching BOTH ObjectNum AND Country as "Archetype"
    DoCmd.OpenQuery "SetArchetypesQ", , acReadOnly
'Appends New Archetypes to IPPRequestsT
    DoCmd.OpenQuery "AppendNewArchetypesQ", , acReadOnly
'Create, save and send request batch results
    Dim XL As Excel.Application, wbTarget As Workbook
    Dim qdfResults As Querydef
    Dim rsResults As DAO.Recordset
    
    Set XL = New Excel.Application
    Set wbTarget = XL.Workbooks.Open("G:\XE_ECMs\IPP Sharing Development\Templates\IPP_Request_Form.xlsm")
    
    Set qdfResults = CurrentDb.QueryDefs("ProcessSheetsQ")
    Set rsResults = qdfResults.OpenRecordset()
    XL.Visible = False
    wbTarget.Sheets("IPP_Request").Range("A6").CopyFromRecordSet rsResults
    XL.Run "SendRequesterBatchPending"
    Set wbTarget = Nothing

Wend

If DCount("*", "ProcessingBatchOutQ") > 0 Then
    Call SaveOpenBatch



End If

'ClearProcessSheets
    DoCmd.RunSQL "DELETE * FROM ProcessSheets"
    DoCmd.SetWarnings True
End Sub

Public Sub SaveOpenBatch()
    
    Dim XL As Excel.Application, wbTarget As Workbook
    Dim qdfResults2 As Querydef
    Dim rsResults2 As DAO.Recordset
    
    Set XL = New Excel.Application
    Set wbTarget = XL.Workbooks.Open("G:\XE_ECMs\IPP Sharing Development\Templates\IPP_Request_Form.xlsm")
    
    Set qdfResults2 = CurrentDb.QueryDefs("ProcessingBatchOutQ")
    Set rsResults2 = qdfResults2.OpenRecordset()
    XL.Visible = False
    wbTarget.Sheets("IPP_Request").Range("A6").CopyFromRecordSet rsResults2
    XL.Run "SaveOpenBatch"
    Set wbTarget = Nothing

End Sub

Select allOpen in new window

Would these attachments be useful?
ProcessingDummy.accdb
TestNew.xlsm

First time through: I moved clearing the ProcessSheets table to inside the loop and ran the first import from the VBE; the results can back perfect, with all the test data.  I opened the results sheet, edited it, and resubmitted it with the button which uses the email protocol.  The procedures ran without error, but this time, the saved results were not updated with my edits, and the IPPRID number was missing.

Second time through: I sent the test form to the email with the button and the procedures ran.  This time the results sheet did not have the IPPRID numbers on it.

I thought the way Outlook is saving the spreadsheet attachment is not correct, so I stepped through it.  Apparently, the attachment saved correctly with my edits and IPPRID numbers, but the Access import step brought in the blank records that should have had my edits on them.  That's where the bind is.

<I opened the results sheet, edited it, and resubmitted it with the button which uses the email protocol. >
did you save before resubmitting?

Not manually.  The button saves and sends.

FYI, I just stepped through the rest of the procedure after manually pasting the edited data into the ProcessSheets table.  It worked.

Progress. I added the "acSpreadsheetTypeExcel9" argument to the TransferSpreadsheet method and all the data comes in.  I do still have output issues, but both sets of results are alike and wrong, so I'll start looking at the output queries--on Monday.  Thanks for all your help and patience, Rey.  Have a great weekend!

Well, back to the grind.  I checked the output queries and they are correct.  When I step through and save the results sheets and send them by email, the results are correct; the updates are done and the IPPRID numbers appear in the working batch.  But when I run the procedures at full speed, the edits don't update and the results come back not updated; the working batch does not have the IPPRID numbers.  I'm mystified.

Thanks for keeping me looking in the right place for a solution--and hanging in with me!