Solved

Access won't import vital Excel column.

Posted on 2015-01-29
34
132 Views
Last Modified: 2016-02-11
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
Comment
Question by:Jay Williams
  • 18
  • 10
  • 4
  • +1
34 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40578021
Have you tried linking to the Excel spreadsheet, then joining on the II field?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40578034
have you tried importing the incoming sheets  to a temporary table, then do an update of the final table using the temporary table
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40578096
Is the column in the spreadsheet?
Check your import spec to make sure it is not being dropped when you link or import.
0
 

Author Comment

by:Jay Williams
ID: 40578111
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
 

Author Comment

by:Jay Williams
ID: 40578117
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 40578142
Did you check the import spec?
0
 

Author Comment

by:Jay Williams
ID: 40578149
I'm not sure what you mean, Pat.
0
 

Author Comment

by:Jay Williams
ID: 40578157
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40578207
there is no import spec for importing excel files (.xls,.xlsx)
0
 

Author Comment

by:Jay Williams
ID: 40578244
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40578304
there is no import spec for importing excel files (.xls,.xlsx)
There is if you make one.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40578366
how?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40578569
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
 

Author Comment

by:Jay Williams
ID: 40578577
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40578595
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
 

Author Comment

by:Jay Williams
ID: 40578606
OK.  thanks.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40579109
OK disbelievers - pay special attention to the checkbox.
Spreadsheet Import Wizard
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40579521
that is not an import spec, it is a manual import steps.
0
 

Author Comment

by:Jay Williams
ID: 40579752
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40579820
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
 

Author Comment

by:Jay Williams
ID: 40579834
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40579872
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
 

Author Comment

by:Jay Williams
ID: 40579883
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40579929
<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
 

Author Comment

by:Jay Williams
ID: 40579941
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
 

Author Comment

by:Jay Williams
ID: 40579987
Would these attachments be useful?
ProcessingDummy.accdb
TestNew.xlsm
0
 

Author Comment

by:Jay Williams
ID: 40580384
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40580441
<I opened the results sheet, edited it, and resubmitted it with the button which uses the email protocol. >
did you save before resubmitting?
0
 

Author Comment

by:Jay Williams
ID: 40580447
Not manually.  The button saves and sends.
0
 

Author Comment

by:Jay Williams
ID: 40580470
FYI, I just stepped through the rest of the procedure after manually pasting the edited data into the ProcessSheets table.  It worked.
0
 

Author Comment

by:Jay Williams
ID: 40580565
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
 

Author Comment

by:Jay Williams
ID: 40583814
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 40583855
try considering adding data verification routine to your codes.
0
 

Author Closing Comment

by:Jay Williams
ID: 40584001
Thanks for keeping me looking in the right place for a solution--and hanging in with me!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now