Solved

Use of the function TransferSpreadsheet

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

617 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