Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access won't import vital Excel column.

Posted on 2015-01-29
34
Medium Priority
?
163 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 18
  • 10
  • 4
  • +1
34 Comments
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40578021
Have you tried linking to the Excel spreadsheet, then joining on the II field?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 39

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 39

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 120

Expert Comment

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

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40578366
how?
0
 
LVL 48

Expert Comment

by:Dale Fye
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 39

Expert Comment

by:PatHartman
ID: 40579109
OK disbelievers - pay special attention to the checkbox.
Spreadsheet Import Wizard
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

636 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