Disable pop up message in vlookup
I have some vba code that has a vlookup to a file. The file (the WBS Export.xls in my below code) has a pop up sometimes on it that I would like to disable so the vba code will move forward. I've already tried to use Application.DisplayAlerts = False but that doesn't seem to work since it is the file within a vlookup that needs disabled. Any suggestions?
Please Login or Register to view this content.
What does the pop up say? is it warning you of something?
If someone has helped you then please add to their Reputation
The pop up for the WBS Export.xls just says "One or more invalid names were detected in this workbook. Those invalid names have been changed to #REF!." Then it has just the option to check "OK" or X out of it. If I just open this file and save it, it usually doesn't give me the message again. This file is an export out of another program.
After I export and save the WBS Export file, I don't really want to have to open it and save it just so the pop up message doesn't appear.
Thanks for any help you can provide!
Your best bet then is to resolve the issues in the other workbook. By the sound of it, this is down to some named range you have in the importing file. Open that file and use the Name Manager to see the offending range(s) (they should be shown as #REF in the list). Fix those by either supplying the correct range or removing the name altogether if its not required and you should be laughing.
Well that's unfortunate because the whole purpose of my code is to pull the data, save it to a network drive so I can vlookup to that data. That WBS export file gets repulled a few times a week and by saving it the same place every time, I thought I was saving myself some time by not having to open it and mess with it. There really isn't any code that could be used to bypass that message when the vba is doing a vlookup to that file?
Im not sure if Im putting across the point correctly so apologies for that.
The issues isnt that its saved to the same place each time, it would appear that there are named ranges within that sheet which arent valid (theres also an issue with named ranges between Excel 97 and 2007 onwards which could be the problem). The point is though its some range within that 2nd workbook that is causing the problem, getting rid of that issue is how you should be approaching this, not looking to suppress the errors.
Can you upload a copy of this 2nd workbook? It doesnt even matter if you delete all the data in it, as long as its the actual workbook so we can see what named ranges exist within it.
you are talking about making updates to the WBS export file, right? And that's what I'm trying to avoid because that file gets saved over multiple times a week so it would just revert back to that same name range error each time it is resaved.
There must be a master document though that is used either as a template to output data into from scratch or a single existing document that gets updated with new items appended? Either way there should only be a need to amend it once and from that point onwards it should work.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks