Importing From Access Dropping Column

[BigNate]

Hello Everyone,

I have an Excel file. The file ORIGINALLY had 20 columns. Now it has 21 columns. I have a macro that imports this Excel data into Access. It doesn't matter where I have that 21st column, Access only ever imports the 20 columns. I even pasted it right in the middle and the same problem occurs. Does anyone know how to resolve this?

When I manually import this Excel data, I have no problems.

Thanks!

 

[ranman256]

what is your import command in the macro?

if you are using a query to import, you must add the field to the target table
if the query uses a * to import all fields then you are fine,
if not, the query must add the field coming from the excel file
if you use the transferspreadsheet command in the macro, then nothing needs to be done, the target table must have all fields listed in the excel sheet.
if you are using an attached excel table, you must refresh the link to pick up the new field.

 

[BigNate]

Ranman,

Thank you for your response. I'm using the transfer spreadsheet command. I delete the table and recreate it everytime but with the code below, it always reverts back to the original import results. Do you see the issue here? I'm very confused.

Private Sub BtnImportExcel_Click()
On Error Resume Next
DoCmd.DeleteObject acTable, "ExcelData"
On Error GoTo 0
    
DoCmd.TransferSpreadsheet acImport, 9, "ExcelData", "S:\ABC\ABC\Duty_Drawback\DutyDrawbackExcelData.xlsx", True, "DataSheet!"


End Sub

 

[BigNate]

Please disregard. I was making a foolish mistake. I had two files with the same name, but one was .xlsm and the other .xls and it was calling the wrong one.

Thanks again for your time and help.

 

[alansidman]

As a note: If you are deleting the table before each import, why not avoid all that and just link your Excel sheet as a table in Access?

 

[Micron]

alansidman: If you are deleting the table before each import, why not avoid all that and just link your Excel sheet as a table in Access?
AFAIK, this was once an option, but was removed when MS lost a lawsuit in 2002 to the owner of the code that permitted a spreadsheet to be linked as a table. Could you tell me if this has come back, and if so, in what version? I'm using 2007 still, and have not tried this for some time.
Here's a link to what may be old news and no longer current?
http://www.theregister.co.uk/2005/06/07/microsoft_pays_excel_man/
Thanks.

 

[alansidman]

@Micron
Using Access 2013. Can link an Excel Sheet in a normalized manner as a table in Access. I was able to do this in 2010 and I think I did in 2007 at a client.

Alan

 

[Micron]

Well I'll be darned. I used to do it too, but lost the ability when MS lost the lawsuit and the company I worked for applied the next Windows service pack which removed the capability. I never tried again since then, except today as an experiment (which worked). Any attempt to use these linked spreadsheets caused an error after the SP was uploaded. Did you read the article for the link I posted? I'm curious to know when and how it is that this came back.

 

[alansidman]

@Micron
Read the article. This is the first I've heard of this issue. I have been using Access regularly since 2007 and this has never been an issue for me.

 

[Micron]

I did a bit more digging. For anyone who may be interested and is familiar with the gurus Doug Steele and Tony Toews, I found comments like this (circa 2006):

Unfortunately, it's not possible to add data to Excel from Access using linked tables: Microsoft lost a lawsuit a few months ago, and was forced to remove that capability.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Also found that Microsoft buried the fact that the capability was removed (but not why) in a SP release that affected Access as far back as 2002: https://support.microsoft.com/en-us/kb/904953

My guess is that many people probably never applied these sp's. I seldom did for home use, but corporations would usually not expose themselves to using unlicensed software, so at work, I lost the ability. I guess it's back though, because you can find info on how to do this in recent MS articles.