Hi
I am using Power Query because it does not require to open the source WB to fetch data. I am facing two problems
1) It fetches data as a table (while I need it in a simple range)
2) It does not (Or may be I don't know) import named ranges.
I want to bring a complete sheet from a closed workbook along with its named ranges without opening the source book.
Help would be greatly appreciated
Best Regards
Imran Bhatti
Teach me Excel VBA
You are using the wrong tool I'm afraid- that is not what Power Query does. You would have to actually copy the worksheet for what you want.
Don
Please remember to mark your thread 'Solved' when appropriate.
Hi sir Don
Thanks for reply
The only reason to use PQ is its ability to fetch data with out opening the source workbook and nothing else.
Yes, but it only brings data. I don't know of any way to extract the definition of a named range without opening the workbook.
My understanding is that PQ can and does import Named Ranges, and this is actually a gotcha when working with PQ
This screenshot below shows that when I go to import a workbook there are 3 things:
- Sheet1 is just the sheet.
- _xtnm.Print_Area is a print area that I set up.
- MyRange is a named range
This issue came up when a client had some weird duplicates all of a sudden. I discovered that someone had set a print area in just one of the workbooks. Thus, PQ was importing the same data twice. Named Ranges create the same problem if you aren't aware.
GetNR.PNG
It will import data from a named range but it will not replicate a worksheet including naming ranges which I think is what the OP wants.
Let's see what OP responds with.
What I want:
1: Get Full Data of a specific worksheet of another book (source book) to Destination Book
2: Get all Named Ranges of the worksheet of source book to Destination Book
3: During all this procedure, my source book should not be opened
Now
What PQ is offering
Point 1
Point 3
What PQ is unable to do
Point 2
What VBA is offering
Point 1
Point 2
What VBA is unable to do
Point 3
Hope this makes sense
Edit:1 :By Named Ranges I mean the same Named Ranges formulas should be imported from source book. like if a Named Range is defined in source book as MyNamed Range,Referes to:=Sheet1!$A$1:$A$20, it should also appear in destination book if I press Ctrl + F3
Last edited by ImranBhatti; 06-19-2018 at 01:10 AM.
Copy source file
Paste as copy
all three points are done without opening file
Hi sandy thanks
But I have a Destination book already
so make a new destination
if PQ doesn't work and VBA doesn't work
change point of view and try alternative way
I have to agree with Sandy - why does the pre-made destination book need to be there at all if there is nothing in it? All you seem to be wanting to do is replicate an existing workbook in its entirety.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Simply can't change the Destination book as my Destination book is a dictator application
and Named Ranges are necessary to run a batch process on multiple source books in a folder from my dictator application (Destination book)
Don't worry I have alot'ov reasons for what I am sticking to.
You cannot import any Name with formula definition
You can import Name if this is a range (with data or without) but in destination file it should be loaded into the sheet. If not - this Name wont exist
as mentioned above - PQ works with data not with formula/calculation or something like that
Thanks for the contribution
according to sandy I must think ov some other way so here is my workaround.if it is helpful for someone.
I am Saving the Named Range info in every source book once for all as
Two adjacent columns at the right of dataset named as "Name of Named Range" and "Referes to:" .Printing the names and formulas there. Getting that full sheet data to dest book with PQ (using VBA) and then recreating named ranges from those two columns(with vba)
Not as efficient way but as for as I know ,it is the only way to get what I want.
You might try using Worksheets.Add and specifying the source file as the type argument.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks