Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

Access won't import vital Excel column.

I'm using Excel data to update an Access table, but the ID number column won't import.  The reference ID is needed to update the right records.  Is this a formatting issue?

The records that are being imported originally came from Access and was exported and distributed on Excel with the ID numbers on them.  The incoming sheets are the updates for the database, but the ID numbers aren't coming back in.  Any ideas?  Thanks!
0
Jay Williams
Asked:
Jay Williams
  • 18
  • 10
  • 4
  • +1
1 Solution
 
Dale FyeCommented:
Have you tried linking to the Excel spreadsheet, then joining on the II field?
0
 
Rey Obrero (Capricorn1)Commented:
have you tried importing the incoming sheets  to a temporary table, then do an update of the final table using the temporary table
0
 
PatHartmanCommented:
Is the column in the spreadsheet?
Check your import spec to make sure it is not being dropped when you link or import.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Jay WilliamsAuthor Commented:
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.
0
 
Jay WilliamsAuthor Commented:
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.
0
 
PatHartmanCommented:
Did you check the import spec?
0
 
Jay WilliamsAuthor Commented:
I'm not sure what you mean, Pat.
0
 
Jay WilliamsAuthor Commented:
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.
0
 
Rey Obrero (Capricorn1)Commented:
there is no import spec for importing excel files (.xls,.xlsx)
0
 
Jay WilliamsAuthor Commented:
0
 
PatHartmanCommented:
there is no import spec for importing excel files (.xls,.xlsx)
There is if you make one.
0
 
Rey Obrero (Capricorn1)Commented:
how?
0
 
Dale FyeCommented:
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.
0
 
Jay WilliamsAuthor Commented:
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()

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
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
0
 
Jay WilliamsAuthor Commented:
OK.  thanks.
0
 
PatHartmanCommented:
OK disbelievers - pay special attention to the checkbox.
Spreadsheet Import Wizard
0
 
Rey Obrero (Capricorn1)Commented:
that is not an import spec, it is a manual import steps.
0
 
Jay WilliamsAuthor Commented:
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.Import SheetWhat came in.
0
 
Rey Obrero (Capricorn1)Commented:
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 ?
0
 
Jay WilliamsAuthor Commented:
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().
0
 
Rey Obrero (Capricorn1)Commented:
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.?
0
 
Jay WilliamsAuthor Commented:
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.
0
 
Rey Obrero (Capricorn1)Commented:
<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?
0
 
Jay WilliamsAuthor Commented:
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

Open in new window

0
 
Jay WilliamsAuthor Commented:
Would these attachments be useful?
ProcessingDummy.accdb
TestNew.xlsm
0
 
Jay WilliamsAuthor Commented:
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.
0
 
Rey Obrero (Capricorn1)Commented:
<I opened the results sheet, edited it, and resubmitted it with the button which uses the email protocol. >
did you save before resubmitting?
0
 
Jay WilliamsAuthor Commented:
Not manually.  The button saves and sends.
0
 
Jay WilliamsAuthor Commented:
FYI, I just stepped through the rest of the procedure after manually pasting the edited data into the ProcessSheets table.  It worked.
0
 
Jay WilliamsAuthor Commented:
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!
0
 
Jay WilliamsAuthor Commented:
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.
0
 
Rey Obrero (Capricorn1)Commented:
try considering adding data verification routine to your codes.
0
 
Jay WilliamsAuthor Commented:
Thanks for keeping me looking in the right place for a solution--and hanging in with me!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 18
  • 10
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now