Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-27-2020, 11:13 PM
Marcia's Avatar
Marcia Marcia is offline Excel 2019 PQ shows error if source data is a range Windows 7 32bit Excel 2019 PQ shows error if source data is a range Office 2013
Expert
Excel 2019 PQ shows error if source data is a range
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default Excel 2019 PQ shows error if source data is a range

I had been using a downloaded PQ in Excel 2013, Windows 7. I got the Windows and Excel upgraded from 7 to 10 and 2013 to 2019 respectively. To my dismay, the built in date picker is no longer available but I can live with that. My real headache is the PQ sends an error message regarding the data source which is a regular worksheet. I must convert it to table, something that I cannot do because the user form works with range not table.


Is there a workaround to force PQ to accept a range data source?
Thank you.
Reply With Quote
  #2  
Old 11-28-2020, 12:09 AM
Purfleet Purfleet is offline Excel 2019 PQ shows error if source data is a range Windows 10 Excel 2019 PQ shows error if source data is a range Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

PQ needs an excel table to my knowledge (which is limited about PQ). there are articles on the web about using named ranges, but as a quick win could you just recreate (copy & paste linked data?) the data on another sheet and use that one for your the PQ and then extend the table manually as you add data to the non excel table?

Not great but might help until you can find a better fix
Reply With Quote
  #3  
Old 11-28-2020, 04:50 AM
Marcia's Avatar
Marcia Marcia is offline Excel 2019 PQ shows error if source data is a range Windows 7 32bit Excel 2019 PQ shows error if source data is a range Office 2013
Expert
Excel 2019 PQ shows error if source data is a range
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Thank you Purfleet. Will try the paste link but I must remember to adjust the area copied everytime a record is added. There was no problem with a range data source in Excel 2013.
Reply With Quote
  #4  
Old 11-28-2020, 11:47 AM
p45cal's Avatar
p45cal p45cal is offline Excel 2019 PQ shows error if source data is a range Windows 10 Excel 2019 PQ shows error if source data is a range Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

PQ will work with a named range.
However, it's probably more robust to adjust the userform code to handle a table - tables are usually easier to write code for.
Reply With Quote
  #5  
Old 11-28-2020, 02:46 PM
Marcia's Avatar
Marcia Marcia is offline Excel 2019 PQ shows error if source data is a range Windows 7 32bit Excel 2019 PQ shows error if source data is a range Office 2013
Expert
Excel 2019 PQ shows error if source data is a range
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Quote:
Originally Posted by p45cal View Post
However, it's probably more robust to adjust the userform code to handle a table - tables are usually easier to write code for.
The thing is I just customized a downloaded form. My code writing skill is next to nill, although I tried a search on how to disable the autofilter in a named range.
Reply With Quote
  #6  
Old 12-16-2020, 02:21 PM
Marcia's Avatar
Marcia Marcia is offline Excel 2019 PQ shows error if source data is a range Windows 7 32bit Excel 2019 PQ shows error if source data is a range Office 2013
Expert
Excel 2019 PQ shows error if source data is a range
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Quote:
Originally Posted by HarryH View Post
I had the same problem. Did you solve it?
Harry, unfortunately, no. I'm running the PQ 2 ways - through the paste link that Purfleet suggested and by keeping 2 Excel versions. 2013 with a downloaded PQ that allows regular sheets as data sources and the later version where I apply the paste link method.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2019 PQ shows error if source data is a range Excel range pasted into Word shows ##### instead of numbers kjc Excel 7 05-10-2020 02:15 PM
What's new in Word 2019, Excel 2019 and Outlook 2019 and Why upgrade to Office 2019 is recommended? einfomail Office 1 07-26-2019 09:44 AM
Excel 2019 PQ shows error if source data is a range Merge same excel data source in two columns Marcia Mail Merge 1 07-25-2019 02:27 PM
Excel 2019 PQ shows error if source data is a range Excel data linked to external source Doug Needham Excel Programming 1 09-15-2015 05:14 AM
Excel 2019 PQ shows error if source data is a range How can I revise Excel data source in merge? navysalad Mail Merge 6 01-07-2012 06:50 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:39 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft