?
Solved

Use of the function TransferSpreadsheet

Posted on 2008-10-26
4
Medium Priority
?
393 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

719 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