+ Reply to Thread
Results 1 to 2 of 2

Importing a web query .iqy file using VBA - how to overcome enter email popup on load

  1. #1
    Forum Contributor
    Join Date
    01-18-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    127

    Importing a web query .iqy file using VBA - how to overcome enter email popup on load

    Hi everyone
    The story so far - I am utilising web queries (.iqy files) downloaded from an ERP system and then uploaded into worksheets in Excel. Trouble is there are 50 individual reports so 50 sheets, each with a web query .iqy file embedded.
    Recently, I started getting an error after copying or saving the file as xlsb so that next time the file was opened Excel would display the worksheet but an error message on open would say that a formula in one of the files in \xl path was corrupted for sheet1. Well a sheet1 does not exist so it's all very strange.

    So I reverted back to previous versions when I didn't receive such an error. In those previous versions, I only had 10 web query iqy files, and I never had this error on open.

    I tried to put back in the web queries one by one.....I got the same error message again when the 50 were input. I have decided that this is an issue with too many web queries so I am examining another approach.

    What I'd like to do is have VBA import only the minimal amount of web queries needed (I can manage with 2). I was thinking of centralising all the iqy files on the network and have VBA auto load them as and when required.

    So I recorded the macro and lmported a iqy file. The problem is that when you do this manually, your email address is asked for in a small popup window. How do I overcome this popup message from appearing? If it helps, part of the connection string in the iqy file contains the following:

    ...........webquery.nl?compid=4201333&entity=32222&email=["emailaddress","Please enter your email address:"]&role=1026&cr......

  2. #2
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: Importing a web query .iqy file using VBA - how to overcome enter email popup on load

    Quote Originally Posted by Julesdude View Post
    ... The problem is ... email address is asked ... How do I overcome this popup message from appearing? ...
    Have you tried entering the correct email address instead of:
    Please Login or Register  to view this content.
    in the "iqy" file ? Try e.g.:
    Please Login or Register  to view this content.
    'iqy' files are ordinary txt files that can be edited, e.g. with a notepad.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 10-17-2016, 04:08 PM
  2. Polish signs error during importing from TXT file to Excel - MS Query UTf 8 encoding
    By jaryszek in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-20-2016, 05:44 AM
  3. Enter data and send file as email
    By FDibbins in forum Excel Programming / VBA / Macros
    Replies: 41
    Last Post: 04-16-2015, 07:19 PM
  4. Popup Calendar Load Object
    By welshman010 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-19-2014, 03:39 AM
  5. VBA Importing Text File makes the file longer to load everytime
    By Hudas in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-15-2014, 11:26 AM
  6. Replies: 1
    Last Post: 08-13-2013, 03:25 AM
  7. An email link doesn't work when the worksheet is protected. How to overcome?
    By TomFioritto in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-31-2013, 07:28 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1