Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
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
 
Old February 15th, 2008, 04:04 PM
Authorized User
 
Join Date: Apr 2007
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default Transfer spreadsheet with spaces in sheet name

I am using the transfer spreadsheet command, but whenever the Excel
spreadsheet has spaces in the name, I don't know how to deal with that
therefore is doesn't work.

For instance:
DoCmd.TransferSpreadsheet acImport, 8, "Temp_SB_Pricing", strUsFile,
False, "Pricing!A2:U300" - This Works Fine


BUT


For instance:
DoCmd.TransferSpreadsheet acImport, 8, "Temp_SB_Pricing", strUsFile,
False, "Pricing 2008!A2:U300" - DOES NOT WORK - and I am pretty sure
it is because of the space between pricing and 2008 in the sheet name.


Since I cannot dictate the sheet name from the source I get them from,
does anyone know how to deal with this?

Thanks your all of your help,
Coby.





 
Old February 15th, 2008, 04:21 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Perhaps you can use Chr(32) in the string, like:

DoCmd.TransferSpreadsheet acImport, 8, "Temp_SB_Pricing", strUsFile,
False, "Pricing" & Chr(32) & "2008!A2:U300"

Did that help?

mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old February 15th, 2008, 04:29 PM
Authorized User
 
Join Date: Apr 2007
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I tried it and got "Syntax error (Missing Operator) in query expression"
So, I double-checked to make sure I didn't make a Typo and still get the same message.

Maybe I am doing something else wrong.
I will keep trying.

Thanks,
Coby.

 
Old February 15th, 2008, 04:34 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

How about this then:

Dim sString As String

sString = "Pricing" & Chr(32) & "2008!A2:U300"

DoCmd.TransferSpreadsheet acImport, 8, "Temp_SB_Pricing", strUsFile,
False, sString



mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old February 15th, 2008, 05:51 PM
Authorized User
 
Join Date: Apr 2007
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default

For some reason that doen't work either?
It seams like either:
1.The SheetName from the Excel spreadsheet is representing the spaces differently.
Or
2.The is some other bracketing or nesting characters that may need to be used when referring to the worksheet object?

I am guessing at this point hoping that we can come up with something here.
It sure seems like what you have suggested should surely work.

I really appreciate the time.
Coby.







Similar Threads
Thread Thread Starter Forum Replies Last Post
Open Workbook,Copy Sheet,Move Sheet, Close/Save ptrussell2009 Excel VBA 0 June 13th, 2008 02:28 PM
access function in data sheet(another sheet) jani Excel VBA 1 May 21st, 2008 07:15 PM
Transfer Spreadsheet JamesHardiman Access VBA 1 November 26th, 2003 01:35 PM
Transfer Spreadsheet JamesHardiman Pro VB Databases 0 November 26th, 2003 11:17 AM
spaces problem mateenmohd SQL Server 2000 8 August 11th, 2003 06:27 PM





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