Unable To Read Certain fields in Linked Excel Sheet to Access

I am using Acc 2003, but Acc 2007 should work the same (I think).

I have linked an excel spreadsheet into my Access database.   Almost all of 
the fields are available and can be read.

There are a few fields that I can see in Access, but its in Excel.  These 
fields were using a VLookUp function in excel to get the value from another 
excel worksheet.

Also the linked excel sheet has fields that I believe should be text (in 
Access), but its was set to be a number.   I am unable to change this.

Is there a way to resolve this issue for me ???

I also assume that if I was to read each cell in the excel sheet and convert 
it into text files using VBA code, that should resolve the issue.

Does someone have any suggestions for me ???


Thank You,

G
0
Utf
5/6/2010 9:05:01 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

1 Replies
801 Views

Similar Articles

[PageSpeed] 55

Hi Gary,
That's not my experience. I just created a workbook in Excel2007 that 
included a column with a vlookup function, and it worked like expected: the 
data was visible in the Access-attached table.
Rather than going through the pain of exporting to text, consider 
*importing* the data rather than linking to it.
Access determines the datatype of a column by scanning only the first few (I 
think 8) rows. If those are numeric, and further down there is text, indeed 
you will get the wrong data type. Perhaps you can sort your data differently, 
or include a fake top row that is there only during linking and is later 
removed.

Tom van Stiphout
Microsoft Access MVP


"Gary" wrote:

> I am using Acc 2003, but Acc 2007 should work the same (I think).
> 
> I have linked an excel spreadsheet into my Access database.   Almost all of 
> the fields are available and can be read.
> 
> There are a few fields that I can see in Access, but its in Excel.  These 
> fields were using a VLookUp function in excel to get the value from another 
> excel worksheet.
> 
> Also the linked excel sheet has fields that I believe should be text (in 
> Access), but its was set to be a number.   I am unable to change this.
> 
> Is there a way to resolve this issue for me ???
> 
> I also assume that if I was to read each cell in the excel sheet and convert 
> it into text files using VBA code, that should resolve the issue.
> 
> Does someone have any suggestions for me ???
> 
> 
> Thank You,
> 
> G
0
Utf
5/8/2010 2:10:01 PM
Reply:

Similar Artilces:

Read Hebrew right-to-left
When I get a message which contains Hebrew text the Hebrew characters are corectly displayed but they appear incorretly left-to-right instead -rith-to-left. Where do I fix this? View | Encoding | ... Nathan wrote: > When I get a message which contains Hebrew text the Hebrew characters are > corectly displayed but they appear incorretly left-to-right > instead -rith-to-left. > Where do I fix this? ...

Word Wrapping in Excel
I am having trouble with Word Wrapping in Excel. It does not adjust th row height properly. I have tried Format, Rows, AutoFit and it stil doesn't work. Help, pleas -- Mandy1 ----------------------------------------------------------------------- Mandy11's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1300 View this thread: http://www.excelforum.com/showthread.php?threadid=27632 Hi do you experience this with merged cells. If yes->nothing you can really do about this without using vBA -- Regards Frank Kabel Frankfurt, Germany Mandy11 wrote: > I...

Unable to open Outlook2003. Error: MAPI unable loading msncon.dll
I am trying to set up Outlook 2003 on home computer but keep getting the following message when trying to open application. MAPI was unable to load the information service msncon.dll. Since I am not open to open, I cannot get to the help feature. Please advise Have you downloaded the latest MSN Connector from their site? Or, if not using it, have you uninstalled it? Try opening Outlook in Safe mode (start->run->outlook.exe /safe) and go into the Advanced options and uncheck it from add-in manager or COM add-ins. --� Milly Staples [MVP - Outlook] Post all replies to the group to ...

Excel in Office for Mac 2008 features
I've had Excel crash numerous times the first day using it. THe Auto Save feature does not seem to be working. Each time it crashes it brings back the old saved spreadsheet with none of the changes. Auto Save is set to 10 minutes and sometimes it had been hours and still reverted back to the old saved sheet and not the updated autosaved one. In article <ee8a70d.-1@webcrossing.caR9absDaxw>, Foosh@officeformac.com wrote: > I've had Excel crash numerous times the first day using it. THe Auto Save > feature does not seem to be working. Each time it crashes it brings back th...

Office 2007 Excel
Every time i open Excel 2007 i get the following; "The file you are trying to open,'PDFMaker.OLDxla',is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?" I pressed CTRL+Shift+I and receive the folowing id 101786 however I could not find anything relating to the above. As a note you have to press NO two times before you can get into excel. Hope I got this in the correct discution group. If you're not using the pdfmaker.* addin, how...

Unable to Edit/Add records on Form in a New ADP
I just started playing around with rebuilding a MDB file as an ADP. I built a form and subform from scratch, but I'm unable to edit or add records. SELECT Id, LoadDate, TrailerDOTNumber, SealNumber, SealDate, SealId, LoadLocation, LoadStatus, DispatchStatus, DispatchLocationId, DispatchUserId FROM TrailerActivityHeaders ORDER BY LoadDate Recordset Type: Updatable Snapshot I'm getting the 'This recordset is not updatable' Ideas? Never mind... (Does anyone have a spare bandage btw? I cut my head banging it on the wall due to my stupidity...) "D...

Horizontal line chart
Hi, I have prepared a bundle of column that has one XY scatter plot serie It works great, updates with VBA, no problem. Now I was asked to add one additional point in each serie to the char with some calculations. Although looked to be be very easy job, it' not, as the file get corrupted while modyfing the data source for thi chart using the MS chart interface. I've tried to put the modificatio into the command line and it works for the chart. Unfortunately it doe not record while macro recoding ('Macro recording not possible' error that is essential to update automatically t...

Unable to open calendar
Folks, i cannot open or view my calendar. When I start Oultook a Pop Up appears saying "Could not read the Calendar" On clicking Ok, the pop up vanishes and nothing happens. If i then go over to my calendar, it says "Cannot display the folder" I have run Oultook Diagnostics and nothing shows up as irregular. I ran the repair function (off my setuo CD) and nothing happened. I also re-installed Outlook and rebooted my comp and nada. When staring in safe mode i also get he same errors. I am using Off 2007, Win 7 TIA, P "phamiltonsmith" <...

Excel
1. Look at a worksheet ; the range A1:A10 is filled with text data. 2. Enter into cell C1, = Countif(A1:A10,"<="&A1:A10) ; it works. 3. Then, the range B1:B10 is filled with text data. 4. Enter into cell C2, = Countif(A1:A10&B1:B10,"<="&A1:A10&B1:B10) ; it does not work. 5. Please offer an explanation that it does work and then it does not work. 6. Regards. I'm having a bit of a hard time understanding what you're trying to do... =COUNTIF(A1:A10,"<="&A1:A10) is functionally equivalent to =COUNTIF(A1:A10,"...

Multiplying in Excel
Why does excel and my calculator come up with different answer when multiplying numbers with decimal points? Excel seems to be off by a couple hundreths of a point. On Thu, 3 Feb 2005 07:53:03 -0800, "albebach" <albebach@discussions.microsoft.com> wrote: >Why does excel and my calculator come up with different answer when >multiplying numbers with decimal points? Excel seems to be off by a couple >hundreths of a point. Hard to tell why you get the results you do when you post so little information. The most likely answers have to do either with you not realiz...

Need to read PAR PORT data bits URGENT
I have never read a parallel port in MFC yet. I need to read the input data on the port tonight for a dog and pony tomorrow. I tried the CParallelPort class but it didn't build and seems more than I really needed.. I am just reading the state of the bits as there is a rotary switch connected to them.. ANy links or methods.. AM I missing the obvious? Thanks again all. Nappy First, using the parallel port for anything is intrinsically dangerous. The standard parallel port driver does not support reading from it. (Besides, modern machines don't have parallel ports). You wil...

in excel formula the comma (,) is no longer accepted but the semi.
when entering formula in Excel the comma (,) is no longer accepted but the semicolon (;) now has to be used. Example: Previous : =if(a2="Yes",a3+a4,b3+b4) Now : =if(a2="Yes";a3+a4;b3+b4) How do I change it back to using commas. I don't know how this has happened, I just arrived at work one day and the changes has occured. Not a 100% sure how well XL honors them, but check your Windows 'Regional Settings.' -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <5...

unable to download emails and unable to stop synchronization
I am going to try one more time to get this fixed. My Outlook 2003 has been unable to download emails. While it is trying, a small box in the tray section shows up and it states that it is trying to synchronize folders. After a time out time Outlook gives me errors. This is the error message. It has these numbers 0X800CCC15 , or 0X8004210A, or 0X8004210B, which these error messages state that either I did not have enough timeout or look and see if server name is wrong. All of you have given me these suggestions. no joy. Thank you very much however. I went ahead and finally found a sect...

unable to scroll the page
on microsoft office frontpage 2003 - when i up loaded my web pages to my web site www.millertimeauction.com , there is no sroll bar for up & down of page - any help what browser? it scrolls for me in IE8 scroll bar will only appear if it needs to -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression Web "FrontPage Error" <FrontPageError@discussions.microsoft.com> wrote in message news:EC19C7CE-1C5A-4872-A47B-2C04BD7E96A7@microsoft.com... > on microsoft office frontpage 2003 - when i up loaded my web pages to my > web > s...

How do I convert Supercalc to Excel
I have data on disks that are in SuperCalc that I would like to convert to Excel, but I don't know how to do that. Excel can't open SuperCalc files. You'd have to get a copy of SuperCalc and save the files from there in to a format that Excel can read (e.g., text, CSV, SYLK, etc). -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Bean Counter" <Bean Counter@discussions.microsoft.com> wrote in message news:30443F32-B3D7-4996-9CAC-608C0F573D9C@microsoft.com... >I have data on disks that are in SuperCalc th...

Unable to start POP3, NNTP, IMAP4...
Dear all, I'm evaluating to install Exchange 2003. After the installation I find the said protocol haven't been started. I tried to start them in "Servers --> Server Name --> Protocols" but no help. Please comment.... Thanks!! Background: Windows 2003, Exchange 2003, Active Directory DC with all roles of FSMO as it's the first DC in the forest. Thank you very much!! -- Are you boring? Let's come to talk together... ^^ news://news.hk4u.com/hk4u.2hit6 Those services are disabled by default in Exchange 2003. Go to Administrative Tools, Services and enable...

Populating Excel from CSV
Hello all, I need to know how to populate an Excel template with data from a external CSV file. Let's say I have this nice pretty colourful templat that is far more pleasing to the eye than the usual chunk of importe CSV data, what are some methods to populate it with data from a CS file?? Any ideas or suggestions or redirections would be much appreciated. Thanks in advance : -- AJMorgan59 ----------------------------------------------------------------------- AJMorgan591's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2777 View this thread: http://www.ex...

Unable to conect
Hi, I have set up my email account to work with aol using the guidlines for outlook 2002 but it is still unable to connect. Any suggestions? Thanks. John <John@discussions.microsoft.com> wrote: > I have set up my email account to work with aol using the guidlines > for outlook 2002 but it is still unable to connect. Any suggestions? Since you pay AOL for their service, my suggestion is to ask AOL. -- Brian Tillman ...

Maximum Open Rows read only?
Hi, Is there a way to se "Maximum Open Rows" so I can set CacheSize of a recordset? Now it is 1 and Acess says it is Read Only. Thanks. ...

Addding notes and rating fields to a report
I just started experimenting with creating some reports using vb.net 2003 and the SRS. So far getting most of the data on the report has been pretty easy but I cant seem to get certain data on there. I would like to have columns available for the notes and also rating as of yet I have not been able to find them. Any ideas? ------=_NextPart_0001_666CDF65 Content-Type: text/plain Content-Transfer-Encoding: 7bit <xxdcmast@discussions.microsoft.com> wrote: > I just started experimenting with creating some reports using vb.net 2003 and > the SRS. > > So far getting mos...

Unable to click on charts & unable to create any new charts Excel
Hi Just started using Microsoft Excel 2007 and I seem to have done something that means the chart option is unavailable. The chart section is now grey and all charts I have created are 'locked' I cannot click on them anywhere. I'm working on a project and need it finished by Monday. Do you mean that if you click on a chart it does not get a border to show it is activated and there are no range-finder borders around the data that makes the chart? This is a bit odd. Have you tried a new workbook to see if the problem is with just this one? My advice with all odd things is...

MSXML6 C++ unable to locate node
Hi, I am new to using the MSXML functions in C++. I found this on MSDN as an example of what I wanted to do, http://msdn2.microsoft.com/en-us/library/ms765465.aspx. I have tried a number of XPATH examples to try and retrieve the "><Code>12455</Code>", but have been unable to do so. The MSDN example works fine. So, I am wondering if it's a namespace issue? Any help would be appreciated. Thanks. Jeff I have changed the code as follows: int _tmain(int argc, _TCHAR* argv[]) { CString sResponse = "<?xml version=\"1.0\" encoding=\"utf...

Disabling Decimals in 'Quantity' field in 'Unit' Form
I am a new CRM customer and trying to set up a annual journal subcription system. As a part of setting up a product catalog, I created a unit, which, obliously has a 'Quantity' field. Since journals cannot have a decimal structure, I was under impression that it was going to be easy to change this field to a whole number only. Instead, I got 1.00000. I tried to follow the instructions and went to System Customization but Unit form is not listed there. Any help would be appreciated. Actually, you handle this with the product defintion. When you specify the PriceListItem values,...

I am unable to send e-mails -unable to download files from IMAP.
Please help. I am so frustrated with this In news:4564BC25-92D9-4094-A598-A23848856E5F@microsoft.com, ajmtextiles <ajmtextiles@discussions.microsoft.com> typed: > Please help. > > I am so frustrated with this I can understand why you might be frustrated, but you've provided no useful information for anyone to go on, so I doubt anyone can help you. When you post in here, always include your version, SP level, and mode (if applicable) of Outlook - you can find this information in Help | About. Also include the type of mail account(s) you use and any other pertinent de...

hyperlink excel 2007 backslash Internet Explorer 7
In excel I can create a hyperlink without any problem, byt when I pick when I wish to open that web page a '/' gets appended to the URL ex: in excel http://someplace:7080/xx When it gets to IE it is http://someplace:7080/xx/ the / has been added to the end, there the url will not work. This is all internal to access programs that are IE based. ...