Only imports 251 characters

**boomshank** · 11-09-2009, 09:54 AM

I am having serious problems with one of my spreadsheets.

The spreadsheet (A) imports data from another spreadsheet (B) onto a new tab, it then uses the data on the tab to populate a form.

Spreadsheet B has a cell that contains approx 1500 characters of text which should be printed on the back of the form.

Using my PC the text is printed on the back absolutely fine no problems.

Using someone elses PC only imports 251 characters of text from the cell in spreadsheet B.

I dont understand how this is happening, Both PC's are using Excel 2003 sp3.

The code that is being used is

Sheets.Add
    ActiveSheet.Name = "import data"
    With ActiveSheet.QueryTables.Add(Connection:=Array( _
        "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=\\addresses\addressData\" _
        , _
        "\Master Print Shop Work Load.xls;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet O" _
        , _
        "LEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Lo" _
        , _
        "cking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLE" _
        , _
        "DB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact " _
        , "Without Replica Repair=False;Jet OLEDB:SFP=False"), Destination:=Range("A1" _
        ))
        .CommandType = xlCmdTable
        .CommandText = Array("Summary$")
        .Name = "Master Print Shop Work Load"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = _
        "\\addresses\addressData\Master Print Shop Work Load.xls"
         .Refresh BackgroundQuery:=False
    End With
End Sub

Any Ideas?

**Whizbang** · 11-09-2009, 11:00 AM

The following link gives a decent description of what is happening:
http://www.*****-blog.com/archives/2...ed-data-types/

From what I am able to understand, the query tries to guess the type of data in the column. The problem you seem to be having is the the majority of cells in that column have less than 255 characters. Thereby, the query thinks the column is "Text", which has a limit of 255 characters.

To get it to read the column as Memo, you need to have the majority of cells contain more than 255 characters. It looks like it tests anywhere between 1 and 16 rows, so a quick fix would be to insert some dummy information in the first 16 rows. But there is a possibility that your registry is set to 0, which means to test all rows.

**** Edit***

It may be helpful to know that if you do manage to get the column to query as a memo field, you cannot use "Count" in the query on that field.

**boomshank** · 11-10-2009, 06:18 AM

How come one machine is able to import all of the data without any issues? Would there be something in the registry that tells it not to check any rows?

i would not be able to put any dummy information into the spreadsheet.

**Whizbang** · 11-12-2009, 04:55 PM

It may be that your registry is set to 0, and the other machine is set to 16 or less. The fix may be to change the registry.

I misread the last link I provided. I thought it was saying that 0 tests all rows, and takes the majority. I now believe I was wrong. I now believe that if set to 0, it tests all rows and defines the field type as the longest row found. Meaning that if even one of the rows has more than 255 characters, the whole column is read as memo.

See this link for more details: http://support.microsoft.com/kb/281517