255 Character Limit when copying data

tombrown

Board Regular
Joined
Jun 2, 2006
Messages
50
Hi,

I have developed a solution to my report project using SQL queries (thanks to Fazza) - see http://www.mrexcel.com/board2/viewtopic.php?t=283369, but I am now left with the problem that any cell with more than 255 characters is truncated when I use the query to puall the data together.

Is there a way of avoiding this, while still maintaining the SQL approach to moving data around? (i.e. I know I can get round it by copying and pasting ranges, but that wont work for me)

My alternative is to limit all data to 255 characters and enforce using
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
Untested:
Use ado to establish the connection and SQL to put the data into a recordset. Then loop through the recordset putting the data into cells. I am not sure if you will still hit the truncation using this method or not, but it might be worth a shot.
 

tombrown

Board Regular
Joined
Jun 2, 2006
Messages
50
Thansk Oorang.

When you state that I should try using ADO to establish the connection I assume I should change part of the following string I use at the moment :


Code:
Connection:=Array(Array("ODBC;DSN=Excel Files;DBQ=D:\xyz.xls;DefaultDir=D:\A;DriverId=790;MaxB"), Array("ufferSize=2048;PageTimeout=5;")

What do I need to change to make this an ADO connection?
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
Hi Tom

I believe that if you add your column with the comments in excel and then use ADO and sql you are stuck with the 255 characters.

However, you could improve your process by creating the columns that house the big strings via ADOX. After the columns are created you can edit the excel as usual. The advantage of creating the extra columns with ADOX (with columns.append) is that you can specify the type like in a database field. In your case, for the comments column, you specify it as adLongVarWChar (equivalent to MEMO). This way you'll have no problem with the 255 chars limit and it's transparent to the ADO.

If you have problems implementing it post back and I can show you what I have (it was not exactly your problem but you can go from there).

Hope this helps
PGC
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
hi all,

I've entered this thread via another http://www.mrexcel.com/board2/viewtopic.php?p=1370427 and Tom asked for further postings in this thread.

A couple of things. I concur with Oorang and PGC, the ADO approach will still have the same 255 character limit. I didn't actually check this but will do so when I have 5 minutes spare today. I do not know about ADOX at all.

I'm posting here, Tom - in reply to the other thread where you said not to post there anymore - to refer you to a recent thread where there was an ADO example using late binding. That is, it will work without the reference being set from Excel VBA. As you probably know, a late binding approach is less than ideal.

That other thread is http://www.mrexcel.com/board2/viewtopic.php?t=274938 The sample code in that thread omits loading of the field headers, they are not included in the recordset, but they are available. If you want some code for that please post again & I can find a sample from somewhere. There is a collection of fieldnames available when the connection is open, it is just a matter of looping through them. (A look at the object browser will find them if you have the reference set, or a quick google.)

HTH, Fazza
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
Hi Fazza

I concur with Oorang and PGC, the ADO approach will still have the same 255 character limit.

I think I've explained mysef wrong. That's not what I meant. ADO/SQL is OK.

What I was trying to say is that the problem is not the ADO, you can use the ADO with SQL the usual way, it works OK. The problem is in the data source: excel worksheets, by default, behave like datatables with the 255 limit. ADO simply reads what is available.

One solution is to use ADOX to create in the excel specific fields in the worksheets that are like MEMO fiels and do not have that limit.

This is just done at the beginning. You then use ADO as usual and you don't have that limit.

In case someone needs it, I can try to modify what I have and create a simple example just to validate the concept.

Best regards
PGC
 

tombrown

Board Regular
Joined
Jun 2, 2006
Messages
50
Thansk all for your replies - I'm goign to work on this over the next few days.

Thanks again
 

Forum statistics

Threads
1,181,717
Messages
5,931,651
Members
436,796
Latest member
parthibanmuniswamy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top