[AccessD] Importing XLSX

Stuart McLachlan stuart at lexacorp.com.pg
Wed May 4 17:08:05 CDT 2016


CSV files are evil!  Use a Tab delimited format.  :)

-- 
Stuart


On 4 May 2016 at 16:52, Mark Simms wrote:

> 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
> 
> 
> -- 
> 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