[AccessD] Importing XLSX

[Bill Benson]

Jim I already suggested Automation and if you simply save the file as XLS
you will not have to read anything cell by cell
On May 4, 2016 5:38 PM, "Jim Dettman" <jimdettman at verizon.net> wrote:

> <<Barring that, perhaps I can open the spreadsheet directly and process it
> that way.>>
>
>  Yes, you could control Excel through automation and read it cell by cell.
>
>  But you can't do it with Access. The compatibility pack does not update
> the
> .DLL's that Access uses.
>
> Jim.
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Rocky Smolin
> Sent: Wednesday, May 04, 2016 04:10 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Importing XLSX
>
> 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
>
> --
> 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
>