When I open my data source (xlsx), I cannot see all my data column

[David Griesel]

I have created a form letter (.docx) into which I want to merge data from an
Excel (.xlsx) data source.

1. I have connected to the Excel spreadsheet using a DDE connection. My
problem is that I do not see all of my merge fields columns. I can only see
the first 60 columns of the 1340 columns of information that I have.
2. I have also tried to connect to the Excel spreadsheet using a ODBC
connection. It then asks me to “Select Table†and will not allow me to select
my data source. My Workbook does not appear on the dropdown list provided and
I cannot seem to change/edit the list provided.

 

[Peter Jamieson]

1. None of the connection methods available will see more than 255
columns of an Excel data source. DDE seems to see a variable number
depending on (possibly) the amount of data. But it never sees more than
255. As far as I can tell, despite the fact that Excel now supports more
than 255 columns and therefore the "Office System" should also do so,
Microsoft considers this to be a "product limitation" which they are not
going to change.

2. To get 255 columns, your best bet is to use OLE DB (which is the
default method). You may however see the problems described at
http://www.pmsn.me.uk/t0003.htm.

3. ODBC does not appear to work well with the new 2007 .xlsx formats.

4. As far as I know, there is no type of data source that will cope with
1340 columns reliably. However, you may find that you can copy/paste
into Word, or export into a delimited text file, and use that as a data
source, as long as you ensure that delimiter characters and multiline
texts are properly "wrapped up".

5. Personally, I would consider finding another way to do what you are
doing, e.g. by Automating Word/Excel via VBA or otherwise.

Peter Jamieson

http://tips.pjmsn.me.uk