Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old February 15th, 2008, 03:18 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Ottawa, , Canada.
Posts: 139
Thanks: 0
Thanked 0 Times in 0 Posts
Default TransferSpreadsheet

Hi,

I'm using DoCmd.TransferSpreadsheet function bellow. Now the issue that I have is that it takes whatever data is in the first sheet in the Excel file regardless of what was the activesheet when I saved/closed the file. Is there any way to get around this?

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "MigData", TxtFilename, True

  #2 (permalink)  
Old February 15th, 2008, 03:42 PM
Authorized User
 
Join Date: Apr 2007
Location: , , .
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes, there is . . . you just need to add to the end of the properties you already have to specify a certain sheet and even a certain range if need be.

Example: DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "MigData", TxtFilename, True, "MyMainSheet!A7:U65536"

NOTE: This does not work with sheets created in older versions of excel.
It will have to have been created in Excel97 or Later and must be at least version 5.0


Coby.





  #3 (permalink)  
Old February 15th, 2008, 04:23 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Ottawa, , Canada.
Posts: 139
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

That only works assuming that the sheet is the same name... but say I have an excel doc that has 3 sheets, I save/exit having had the 2nd sheet as the active sheet.

When I come to upload the file, I want to upload the 2nd sheet in excel, but I won't necessarily know what the name of the sheet will be?

Woudl there be a way to read the sheets from excel, and in access have a pull down menu allowing to specify the target sheet?



Similar Threads
Thread Thread Starter Forum Replies Last Post
TransferSpreadsheet stealthdevil Access VBA 2 March 16th, 2007 02:45 PM
TransferSpreadsheet kaleb0521 Access VBA 1 August 28th, 2006 12:59 PM
transferspreadsheet command Vince_421 Access VBA 2 May 3rd, 2006 11:29 AM
transferspreadsheet ajmil11 Access 3 January 19th, 2006 03:42 AM
TransferSpreadsheet ajmil11 Access VBA 1 January 19th, 2006 02:07 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.