Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Spreadsheets created from TransferSpreadsheet method

Posted on 1998-01-30
2
Medium Priority
?
272 Views
Last Modified: 2012-05-04
MS Access version 7 -- VBA code:

When I use the TransferSpreadsheet method, the created spreadsheet has field type set to 'general' rather than the types I would like it to have.

Question:  while in my button - how can I get the spreadsheet filed types to be what I need them to be (e.g. 'text'.

thx
0
Comment
Question by:msaccess
[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 Comments
 
LVL 12

Expert Comment

by:Trygve
ID: 1967017
Don't think this is possible by the TransferSpreadsheet action.

You will probably have to "talk" to Excel via DDE or automation and tell it set the columns to the desired format. This is not all that easy.
0
 
LVL 9

Accepted Solution

by:
perove earned 200 total points
ID: 1967018
I had the same problem and found theis article in MS KB
Did the trick for me hope it helps U out to

perove

*************'

ACC: #Num Appears in Linked Microsoft Excel Spreadsheet
Article ID: Q162539
Creation Date: 23-JAN-1997
Revision Date: 01-APR-1997
The information in this article applies to:

Microsoft Access versions 7.0, 97

SYMPTOMS


Moderate: Requires basic macro, coding, and interoperability skills.

When you link to a Microsoft Excel 7.0 or 97 spreadsheet from a Microsoft Access 7.0 or 97 database, and the fields in the spreadsheet contain both numbers and text, the fields that contain text appear as #Num! in the linked table in Microsoft Access.


CAUSE


Microsoft Access assigns the data type for each field based on the data in the first row it links. For example, if a field that contains mostly text values has a number in the first row, Microsoft Access assigns the Number data type and then cannot link the rest of the records. Any format that you apply to the fields in Microsoft Excel will be ignored by Microsoft Access when the spreadsheet is linked.


RESOLUTION


Before you link to the spreadsheet in Microsoft Access, make sure that the spreadsheet has the same type of data in each field (column) and the same fields in every row.

-or-

Use the procedure below to format the cells in the spreadsheet so that they will appear correctly in Microsoft Access.


Formatting the Cells in the Microsoft Excel Spreadsheet


To format the cells in the spreadsheet so that the linked table data appears correctly in Microsoft Access, do the following:


Open the spreadsheet in Microsoft Excel 7.0 or 97.

Format the cells in the spreadsheet that contain mixed data as Text. You should do this from the Format menu in Microsoft Excel.

Create a macro in Microsoft Excel that contains the following procedure:


      Sub Addspace()


      For Each cell in Selection


        cell.value = " " & cell.Value
        cell.value = Right(cell.Value, Len(cell.Value) - 1)

      Next
      End sub


Highlight the cells in the spreadsheet that contain the mixed data.

Run the macro and then save the spreadsheet.

Open your database in Microsoft Access 7.0 or 97.

Link to the spreadsheet that you created in Microsoft Excel. Note that your data is now in the correct format.

MORE INFORMATION


Steps to Reproduce Behavior



Start Microsoft Excel and create a new spreadsheet.

Type the following in cells A1 through A5:


      A1. 12345
      A2. 12345
      A3. 12345
      A4. 12345
      A5. 12345A

Save the spreadsheet as Book1, and then close Microsoft Excel.

Open the sample database Northwind.mdb in Microsoft Access.

On the File menu, point to Get External Data, and then click Link Tables.

In the Link box, click Microsoft Excel in the Files Of Type list, and then select the Book1 spreadsheet you created. Click Link.

In the Link Spreadsheet Wizard, click Next twice, accept Sheet1 for the Linked Table Name, and then click Finish.

Click OK in the Link Spreadsheet Wizard message box.

Open the linked table (Sheet1). Note the #Num! in the last record.

REFERENCES

For more information about linking data, search the Help Index for "linked tables, linking table data" or ask the Microsoft Access 97 Office Assistant.
 

--------------------------------------------------------------------------------

Keywords : IsmHowto IsmIea kbinterop
Version : 7.0 97
Platform : WINDOWS
Hardware : X86

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.
)1997 Microsoft Corporation. All rights reserved. Legal Notices.


0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

618 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