UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Code Error Runs Manually, Any Version    
 
   
ws1o
post May 20 2020, 12:21 PM
Post#1



Posts: 119
Joined: 30-October 15
From: Central CT USA


I have a VBA routine to link an Excel file, and when it gets to this line it fails with the error 3274 "External Table Is Not In The Expected Format":

CODE
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12Xml, "Sheet 1 LINK", RFFile, True

I've changed the code to every acSpreadSheetTypeExcel variation, and I've tried changing to the actual numeric values for each Excel file type instead of the constants, but I get the same error.

However, if I then press F5 to continue it runs just fine. It also runs fine from the Immediate pane. Why won't it run from the code by itself?

I have a reference set to Excel, and all of my other code works.

(The VBA code line preceding it is simply putting the filename into the string variable RFFile)

I have compiled, decompiled, run compact and repair, etc.

Go to the top of the page
 
theDBguy
post May 20 2020, 12:59 PM
Post#2


UA Moderator
Posts: 78,445
Joined: 19-June 07
From: SunnySandyEggo


Hi. In case it helps, what is the file's name?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ws1o
post May 20 2020, 01:10 PM
Post#3



Posts: 119
Joined: 30-October 15
From: Central CT USA


AUTO_PIMS_Billing_5_19_2020.xls

(it's in a network folder, in my code I have the entire UNC path)

I've tried it as an XLS file and an XLSX file, and it does the same thing either way
This post has been edited by ws1o: May 20 2020, 01:11 PM
Go to the top of the page
 
theDBguy
post May 20 2020, 01:24 PM
Post#4


UA Moderator
Posts: 78,445
Joined: 19-June 07
From: SunnySandyEggo


Hi. I think Excel12Xml won't work for that file. As to which one to use, can we ask which version of Excel was used to create that file? As a test, try opening the spreadsheet manually and then convert it to a .xslx and then try your code again (with the 12Xml type).

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ws1o
post May 20 2020, 04:09 PM
Post#5



Posts: 119
Joined: 30-October 15
From: Central CT USA


Similar to what you suggested, I ended up having to create an Excel object in code, then use it to open and resave the file as a new version XLSX file, and now it seems to be working.

MS Access error messages are notoriously vague and misleading, so I couldn't tell exactly what was causing that 3274 error. Frustrating!

But after all, figuring out this stuff is why they pay us the big bucks!

pullhair.gif

rotflmao.gif
Go to the top of the page
 
theDBguy
post May 20 2020, 04:21 PM
Post#6


UA Moderator
Posts: 78,445
Joined: 19-June 07
From: SunnySandyEggo


Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    4th July 2020 - 10:35 AM