[AccessD] Importing XLSX

[Rocky Smolin]

Lambert:

Thanks for your reply.  I'm pretty sure I have the compatibility pack
installed because I can open xlsx files with Excel 2003 but I downloaded and
installed anyway.  However, I still got the 'not in the expected format'
message when using TransferSpreadsheet. 

I have to talk to the client to see if they can provide a .CSV file - that
would work around the problem altogether.

Barring that, perhaps I can open the spreadsheet directly and process it
that way.

Best,

Rocky


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Heenan, Lambert
Sent: Wednesday, May 04, 2016 11:37 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Importing XLSX

It's been a while, so I don't exactly remember, but I think that if you
install the MS Office Compatibility Pack, then Access will be able to read
.xlsx files. The SW is available here...

https://www.microsoft.com/en-us/download/details.aspx?id=3


Lambert

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Bill Benson
Sent: Wednesday, May 04, 2016 2:06 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Importing XLSX

Automate Excel through Access. If Excel 2007 and higher is your most
recently installed Excel version, that will be used to open the .xlsx file,
then saveas in code paying particular attention to both extension and the
fileformat intrinsic constant for 2003 files, which to Excel is xlExcel8 but
if you use CreateObject instead of setting a reference to Excel, use 56.
Then use Transfer spreadsheet.

If you have most recently installed Excel 2003, then that is what will open
the file, and it will be in Compatibility mode. Likewise do a SaveAs, but I
am not sure there was a FileFormat property back then. If there was, it
likely was not 56, it might have been some number in the 4000's.
On May 4, 2016 11:32 AM, "Rocky Smolin" <rockysmolin at bchacc.com> wrote:

Dear List:

I'm still using 2003 for development - should probably up to 2010.
However...

I have an app which requires processing data from XL spreadsheets.  My usual
method is to use TransferSpreadsheet into a front end table and process the
records from there.

However, TransferSpreadsheet doesn't seem to like xlsx format. If I save the
file as xls, no problem.

Is there a way to use TransferSpreadsheet on an xlsx file in A2003?

MTIA

Rocky Smolin
Beach Access Software
760-683-5777
www.bchacc.com <http://www.bchacc.com/>
www.e-z-mrp.com <http://www.e-z-mrp.com/>
Skype: rocky.smolin

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com