Preserve data types

shift-del

Well-known Member
Joined
Aug 28, 2009
Messages
516
Office Version
  1. 365
Platform
  1. 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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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
516
Office Version
  1. 365
Platform
  1. 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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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
516
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I just got another idea and found this:
The thread is from 2017. Maybe there are even simpler ways now.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

either way, you won't avoid handwriting the code
no pain, no gain
 

shift-del

Well-known Member
Joined
Aug 28, 2009
Messages
516
Office Version
  1. 365
Platform
  1. 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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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,132,878
Messages
5,655,766
Members
418,236
Latest member
jess5789

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