Solved

Use of the function TransferSpreadsheet

Posted on 2008-10-26
4
388 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

770 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