?
Solved

TransferSpreadsheet Prefix Problem

Posted on 2006-04-28
5
Medium Priority
?
641 Views
Last Modified: 2011-10-03
Hi,

I just noticed, when I use the following to export a table to spreadsheet, there is a ' prefixing  all text fields.

DoCmd.TransferSpreadsheet acExport, , "Tablename", "D:\test.xls"

This is not in the table and will be a problem if it is in the spreadsheet.  How do I prevent this?

Thanks
Jess
0
Comment
Question by:jessmca
  • 3
  • 2
5 Comments
 
LVL 54

Accepted Solution

by:
nico5038 earned 1500 total points
ID: 16559863
0
 
LVL 8

Author Comment

by:jessmca
ID: 16562359
If that is correct, it is not possible to use TransferSpreadsheet from VBA without the single quote.  :(

If thats the case you can certainly have the points, though you would think there would be a way around this.

Thanks
Jess
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16562430
You can always link a table to your excel sheet and fill it with an append query.
Open a new table and select "Link". The select the filetype  ".xls" and navigate to your predefined D:\test.xls
Now change your export query into an append query and point to this new linked table.

Is that an option ?

Nic;o)
0
 
LVL 8

Author Comment

by:jessmca
ID: 16564163
Hi Nic;o),

No, id rather not involve excel.

I exported the queries to text format and saved a spec for each.  Then used transfertext instead to export as csv.  So long as they can open with excel is all thats required.

You deserver the points for saving me wasting time trying to work around it using transferspreadsheet.  Never would have guessed it wasnt possible.  Wonder what the point of the single quote is?!?!?!?

Thanks
Jess
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16564421
Hmm, did some further research and found in the Excel help this explanation:
=============================================================================
An apostrophe ('), a caret (^), or a quotation mark (") appears in the formula bar but not in the cells.
These characters are formatting codes, which may be displayed in documents created in Lotus 1-2-3 or if you are working with keys that are used for navigation in Lotus 1-2-3. In Lotus 1-2-3 formats, an apostrophe (') indicates left-aligned text, a quotation mark (") indicates right-aligned text, and a caret (^) indicates centered text.

To remove the codes, click Options on the Tools menu, click the Transition tab, and then clear the Transition navigation keys check box.
=============================================================================

So I tried to create the spreadsheet with another type and with version 3 there are no single quotes:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "Tablename", "D:\test.xls"

Let me know or it works for you too.

Nic;o)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

840 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