Kamis, 18 Maret 2021

Re: [MSAccessProfessionals] Transfer Spreadsheet code

Hi Jim

acSpreadsheetTypeExcel12Xml should work, as it has the value 10, which is the value you say works.

Kind regards,
Graham

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Bill Mosca
Sent: Friday, 19 March 2021 05:21
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Transfer Spreadsheet code

 

Jim

 

The spreadsheet version is what determines the parameter that you need to know as long as the Access version can handle it. MS just keeps making Excel-to-Access imports more complicated with every new version. That keeps the developer in job security, but it stinks for your clients who have to have their database VBA code tweaked every year or so.

 

One choice would be this: If the source is not a consistent version, you can always link to the sheet and then use an append query to load the data into your table.

 

If I have the option, I ask the user of the source to save the sheet as a CSV (comma delimited) file. That way it is always plain text and can be easily imported.

 

Regards,

Bill Mosca

 

From: MSAccessProfessionals@groups.io [mailto:MSAccessProfessionals@groups.io] On Behalf Of luvmymelody via groups.io
Sent: Wednesday, March 17, 2021 3:00 PM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Transfer Spreadsheet code

 

This is my results

acSpreadsheetTypeExcel12Xlsx  returns error run time error 3170 Could not find installable ISAM

acSpreadsheetTypeExcel12Xml  returns error run time error 3274 External table is not in the expected format

acSpreadsheetTypeExcel12 returns error run time error 3274 External table is not in the expected format

acImport, 10 worked

 

I just need to use the value instead of the string

 

Jim Wagner


 

 

On Wednesday, March 17, 2021, 2:29:12 PM MST, Jim Wagner <josephwagner2@outlook.com> wrote:

 

 

Bill,

 

I am going to try it today

 

Jim


From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Bill Mosca <wrmosca@myself.com>
Sent: Wednesday, March 17, 2021 11:59 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Transfer Spreadsheet code

 

Hi Jim

I think acSpreadsheetTypeExcel12xlsx is the one you want to use. Too bad MS has messed up the help files beyond all possible use for today's versions.

--

Regards,

Bill Mosca, Founder - MS_Access_Professionals

Microsoft Office Access MVP 2010-2016

My nothing-to-do-with-Access blog


On Tue, Mar 16, 2021 at 04:00 PM, luvmymelody wrote:

here is the link i found the info on

 

Jim Wagner


 

 

On Tuesday, March 16, 2021, 3:50:35 PM MST, Jim Wagner <josephwagner2@outlook.com> wrote:

 

 

Hello all,

I have been using the following line of code to transfer spreadsheets into the database. 
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, "Student Positions", "W:\DATA MANAGEMENT SERVICES\MASTER PEOPLESOFT TABLES\Student Positions.xls", True, ""

We are now needing to use xlsx format instead of xls. In the beginning access did not transfer xlsx so it was easy to use the older format. but we are using new software to download our data and xls is not available. I found the following to change to the new format but I wanted to confirm that i am using the correct one

acSpreadsheetTypeExcel12Xml

10

Microsoft Excel 2010/2013/2016 XML format (.xlsx, .xlsm, .xlsb)

So my code would now be 
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12xlsx, "Student Positions", "W:\DATA MANAGEMENT SERVICES\MASTER PEOPLESOFT TABLES\Student Positions.xlsx", True, ""

or do i use the following because we have office 365 Excel 2019

acSpreadsheetTypeExcel12

9

Microsoft Excel 2010 format


thank you so much for the help
Jim Wagner

 

 

Tidak ada komentar:

Posting Komentar