Solved

Transferspreadsheet 31519 error

Posted on 2014-02-27
12
592 Views
Last Modified: 2014-04-25
I am trying to import an Excel .xlsx file into Access using VB code and receive a 31519 error ("You can not import this file") followed by error 424 ("Object required")

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "table name", GetFile, True

GetFile = the Excel file name

What am I missing?

thanks for your help
je
0
Comment
Question by:aeolianje
12 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 39892600
Per the AcSpreadSheetType Enumeration the  acSpreadsheetTypeExcel9 is the Microsoft Excel 2000 format. The .xlsx is a 2007 or above extension.

Then what version of Access and what version of the DB are you doing this in?

And is the GetFile the full path like "C:\Full\Path\File.xslx" or just  "File.xslx"?
0
 

Author Comment

by:aeolianje
ID: 39892736
Access 2010
The GetFile code works in another app...

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tablename", GetFile, True

I tried acSpreadsheetTypeExcel9 to see if that made a difference -- but still get the same error with 9 and 12 in the new app -- same code.  So it's odd that it woks in one place and not another.

I have a similar function that imports .csv text file and have no issues.

thanks again for the help
je
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39892872
What is the source for the SS?

One I ran into was that my CRM would export the query results as a file with an .xls extension. It would open fine in in Excel, but a straight import into Access would fail.

It took forever to figure it out. Then I opened the file with a text editor instead and it turned out to be an XML file with an Excel header string. Excel did the conversion -- but Access couldn't understand it.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:aeolianje
ID: 39892955
I am tring to IMPORT -- not export... ?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39892985
I understand you want to import.

I'm saying what is the Excel SS source? Is it a website or something similar? Are you sure it is a true Excel file that has been edited by Excel?
0
 

Author Comment

by:aeolianje
ID: 39893113
Yes... Sorry I misunderstood your question

I am able to open it in Excel.  It has a .xlsx extension.

What else should I be looking for? Thanks
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39893180
Even though it has an xlsx until you actually edit and save the file it may still be XML file under the covers. Right click the file and do an Open With and select Notepad and see what it looks like in the guts.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39897140
As Jim P mentioned, and I think you indicated, it may show up as xlsx but have XML "under the covers".  If you preceed your Transferspreadsheet with a segment of code that actually opens the Excel workbook using automation and then does a SaveAs with an xlsx file, you may find that you are able to read it properly with TransferSpreadsheet.

Give it a try by doing it manually.
0
 
LVL 84
ID: 39898407
<no points please>

I too have had issues with files that pretend to be xls or xlsx files, especially those that come down from internet portals. One of my clients downloads a file every day from their vendor portal, and uses a utility I created to import that file into their database. They ALWAYS review the file before importing, so I don't automatically SaveAs in my code, but instead have instructed them to do so.

About once a month I get an email saying the utility won't work. About an hour after that, I get an email saying that they forgot to do the SaveAs portion of the process ...
0
 

Author Comment

by:aeolianje
ID: 40013050
I am able to import the xls file manually - but get the 31519 error when trying to use the transferspreadsheet vba.

I managed to get around it by saving the .xls as a .csv file -- and the transfertext option works.

Thanks for all your input.

Do you know of any automated way to save .xls files to .cvs?

je
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 300 total points
ID: 40013267
Because I know the file I'm importing from is going to be an HTML/XML file I came up with this code to convert it to a true XLS file.

Public Function OpenHtmNSaveXL(htmFile As String, xlFile As String)
Dim xlObj As Object
Set xlObj = CreateObject("excel.application")
    xlObj.DisplayAlerts = False
    xlObj.Workbooks.Open htmFile
    xlObj.ActiveWorkbook.SaveAs xlFile, xlWorkbookNormal
    xlObj.Quit
    Set xlObj = Nothing

End Function

Open in new window


It is just changing the SaveAs xlFile to SaveAs xlcsv. But I'm not sure it will open an HTML/XML file correctly.
0
 

Author Closing Comment

by:aeolianje
ID: 40023459
thanks for your help!
je
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

803 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