[AccessD] Importing XLSX
Mark Simms
marksimms at verizon.net
Wed May 4 15:52:37 CDT 2016
Use the CSV format....XLS limited to 65k rows.
You will lose data if you convert from XLSX to XLS with over 65K rows in a
worksheet.
XLSX can hold 1 million rows.
CSV can hold 1 trillion rows.
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf
> Of Rocky Smolin
> Sent: Wednesday, May 04, 2016 4: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
More information about the AccessD
mailing list