Excel Returns Blank

[Guest]

I'm trying to import data from several Excel files into one single workbook
but having a problem with blank cells. I now understand this is due to mixed
data types.

I 've updated my registry key to Zero "TypeGuessRows" What I don't
understand is the connection string which needs the following statement
adding "IMEX=1" please can someone give me an idots guide how I go about this!

Where / how do I add the statement, is it within Excel ?

Thanks in advance.

 

[Tom Ogilvy]

Read through this thread:

http://tinyurl.com/dqwmj

 

[Guest]

Thanks Tom for the post. I'm still at a lost to know how to add the
connection string:
Setting "IMEX=1" in the Extended Properties section of the

connection string.

Where do I add these statements? I'm sorry but I'm a complete amateur at
this and would be grateful for an idiot guide.

Thanks

 

[Tom Ogilvy]

There is sample code in the thread to which I posted a link. Perhaps you
should look at that. Otherwise, I guess I don't know what you are doing.

 

[Guest]

Hi Tom,

Does this code get copied into the "Visual Basic Editor" area of Excel?

Thanks

Jeff

 

[Tom Ogilvy]

No. It shows how to modify your existing code. If you are not using
existing code, then perhaps you need to use the macro recorder while you do
what you are doing manually, then modify the recorded code in accordance
with the extended properties portion of the code in the link.

 

[Tom Ogilvy]

Perhas that answer was too quick since I don't really know what you are
doing. Perhaps invest some time reading this link and see if you can adapt
it:

http://support.microsoft.com/default.aspx?scid=kb;en-us;257819

This was in the original link as well. (there is a second link there you
might want to check as well).

 

[Guest]

Hi Tom,

Thank you for your time and effect, much appreciated.

I think the area of programming is a step too far for me.

In your reply you said you didn't really know what I was trying to achieve,
basically it was this:

I have a master spreadsheet which imports data from several Excel
spreadsheets via the "Import External Data" option, the error I was getting
was NULL cells, which I now know is due to mixed data types.

I can follow the registry fix but have now idea how to add the connection
string (If this is the right terminology to use) due to the liminations of
the Jet Driver.

You state the code is added in the marco field but no marco's are used for
this import only the "Refresh" option.

I hope this makes sense.

Thanks

 

[Tom Ogilvy]

I don't think you can set the extended properties manually. I would suggest
recording the action of making the query. Then use the article referenced
to see how to mdoify the connection string (in the recorded macro) to add
the extended properties. I haven't done it, but that is what I would try.

If that doesn't work, then I guess you would have to go to ADO as in the
article.