Preserve data types

shift-del

Well-known Member
Joined
Aug 28, 2009
Messages
511
Office Version
365
Platform
Windows
Hi Forum

I have a workbook with a few queries in which I specified every column with the correct data type. The queries are loaded into worksheets.

I want to take these tables and use them in other workbooks (merge with other queries). But here is the problem: If I import the tables PQ forgets the data type settings because it just sees the data in the table and not the underlying query.

Is there a way to preserve the data types or to access the data types in the underlying queries or maybe something else?
I just don't want to set the same data types again and again.
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,830
just copy last line (Change Type) from first workbook query to your second workbook (after Navigation)

btw. line Change Type should be added automatically after Navigation. If your types are different do above
 

shift-del

Well-known Member
Joined
Aug 28, 2009
Messages
511
Office Version
365
Platform
Windows
Thanks for your reply. But that is a manual step I'm trying to avoid. Setting the same data types again and again.,
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,830
what about second line in my post? if this is not what you want I am afraid you can't do that

imho better is copy this line than set many types again
 

shift-del

Well-known Member
Joined
Aug 28, 2009
Messages
511
Office Version
365
Platform
Windows
I just got another idea and found this:
The thread is from 2017. Maybe there are even simpler ways now.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,830
sure, solutions from Marcel are always worth attention (y) :cool:

but be aware these tables (from link) are in the same workbook (file) 😇
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,830
either way, you won't avoid handwriting the code
no pain, no gain
 

shift-del

Well-known Member
Joined
Aug 28, 2009
Messages
511
Office Version
365
Platform
Windows
I tested it successfully in a sample file - actually three sample files. With the changes/additions suggested by Imke Feldmann.

Now for the real thing.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,830
sure, if it will work for you post a solution for different workbooks
of course without manual action (no editing any queries)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,563
Messages
5,487,583
Members
407,605
Latest member
PACULA

This Week's Hot Topics

Top