2

I have a workbook that needs to connect to two other workbooks to pull data and summarize it.

I have set up a data connection, but on one of the sheets, when the data is imported, some of the table headings are missing (The cell where the heading would be is empty, although the layout and format of the data seems intact).

The two workbooks are housed on a shared network folder, and are updated remotely by other teams. Because the workbooks are updated regularly, I can't run this macro right in the workbooks

The intention of the sheet is to collect data from both sources, and summarize it on a per-agent basis, with some items like bonus qualifiers, attendance, productivity, etc., and then be able to print the individual sheet as a PDF and send to the agent.

My first data connection (Breakdown by business line) initially was missing the 2nd column header from the original workbook. The source table was a pivot table with a slicer that could hide some columns. No matter how I manipulated the source sheet, only the 2nd column was missing data. I corrected this by removing the data connection and creating a new one with "First Row Contains Column Headers" un-checked

My second data connection (Attendance & performance), I am missing headers for columns 3, 4, 5, and 9-13 (1, 2, 6, 7, 8 all display). The source table for this is a pivot table, but there is no slicer for the columns (they are static).

I've tried both checking and un-checking the "table headers" box. If I check it, I have NO column headers, but un-checked gives the result stated above.

What I'd like to know is: is there a reason for this, or something I can do to change this? Since the headers on this second file are static, I realistically can operate without them since I know what they will be when I create the VBA code, but for future-proofing I wanted to be able to adjust to any changes on the fly without having to edit the code.

As a new user I don't have the reputation to post images, but I've linked to the images here:

This is a sample of my source table:

This is what I get when I connect with "Data Connection"

I expected that when I imported from a table, all data would transfer over, but I seem to be missing only the table headers, for some reason.

Hammer Bag
  • 31
  • 4

1 Answers1

1

In case anyone else comes across this, I think I found the solution. There's an ODBC Limitation when a column has mixed data modes. Because my headings were text but all of the data was numerical (a double data type), the query returns NULL for the text fields and ignores them on my table.

The IMEX setting fixes this. Setting IMEX=1 in the Extended Properties allows for mixed data types. Example:

Extended Properties="HDR=Yes;IMEX=1;"

For me, this didn't fix my issue on its own. When I deleted my table, changed the connection property, and then created a new table from existing connection, everything imported as it should.

Hopefully someone else finds this useful one day.

Hammer Bag
  • 31
  • 4