Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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

Reply With Quote
  #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.





Reply With Quote
  #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?

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 11:20 PM.


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