Solved

Use of the function TransferSpreadsheet

Posted on 2008-10-26
4
389 Views
Last Modified: 2013-11-27
Hi Experts,

I've previously set up and been running an upload using a csv file and the TransferText routine. I've noticed problems in that the data is delimited by comma and in SOME fields, the person entering the data has seperated part of the data in the one field by comma also. eg- "Bill of Life, The"

I thought of saving the file as a spreadsheet and then using the TransferSpreadsheet routine instead. I'm importing this manually and it works perfectly. I've got no experience however in using the actual TransferSpreadsheet routine. The range of cells will probably differ everytime the upload process is used.

Could you please advise how to use the TransferSpreadsheet routine? Here's a copy of the TransferText line I was using..

 DoCmd.TransferText acImportDelim, "ImportSpecificationRegistered", "tblSTARUploadRegistered", "S:\Secured Folders\Audit\RAMS training\Test Database\STAR Data\Input\Registered.xls", True

Thank you.
0
Comment
Question by:jammin140900
  • 2
  • 2
4 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22809204


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,               "tblSTARUploadRegistered", "S:\Secured Folders\Audit\RAMS training\Test Database\STAR Data\Input\Registered.xls", True, "NameOfSheet!"
0
 

Author Comment

by:jammin140900
ID: 22809365

Thanks for the code. I've tried it and getting an error message that one of the fields in the destination table doesn't exist.

I imported this table manually and everything was fine, but when I use this function I get the problem mentioned above. Any ideas please?

Regards
Jammin.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 22809400
normally if the fields or column names changes and column number varies, it is better to import to a new non existing table. { temptable }

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,               "tempTable", "S:\Secured Folders\Audit\RAMS training\Test Database\STAR Data\Input\Registered.xls", True, "NameOfSheet!"
0
 

Author Closing Comment

by:jammin140900
ID: 31510156
Thank you for your help with this. Much appreciated.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question