+ Reply to Thread
Results 1 to 3 of 3

Getting Data from external source with Prompt Window

  1. #1
    Forum Contributor
    Join Date
    04-05-2010
    Location
    Lakewood, IL
    MS-Off Ver
    Excel 2016
    Posts
    367

    Getting Data from external source with Prompt Window

    I collect data from a service that I would like to automate with VBA code. I have a list of codes that when given to a outside service over the internet supplies me with data I need. I wrote the following code, but it keeps opening up new Prompt windows as it walks through the codes. I would like it to only open 1 prompt window. The data retrieval of the data takes up to 5 second so I put a "Wait" statement into the VBA code. Here is the code:
    Please Login or Register  to view this content.
    The excel spreadsheet is a 2 column sheet with the code I send to the outside service is in column B. Column A is just a reference number 1 through x.

    Thanks in advance!

  2. #2
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Getting Data from external source with Prompt Window

    You need to take With CreateObject("WScript.Shell") out of the loop

    Please Login or Register  to view this content.
    You may want to check this link http://www.cpearson.com/Excel/ShellAndWait.aspx for code that allows to wait until shell command finish before returning to the caller (i.e. instead of using the explicit wait)
    If you are pleased with a member's answer then use the Star icon to rate it.

  3. #3
    Forum Contributor
    Join Date
    04-05-2010
    Location
    Lakewood, IL
    MS-Off Ver
    Excel 2016
    Posts
    367

    Re: Getting Data from external source with Prompt Window

    Quote Originally Posted by buran View Post
    You need to take With CreateObject("WScript.Shell") out of the loop

    Please Login or Register  to view this content.
    You may want to check this link http://www.cpearson.com/Excel/ShellAndWait.aspx for code that allows to wait until shell command finish before returning to the caller (i.e. instead of using the explicit wait)
    Hi buran, I tried your code and I get the same result as I do with my original code where a new Command Prompt window opens up for each time it progresses down the list of numbers. As for your recommendation on the link you sent me, I am not sure how to implement that into the VBA code. Thanks for trying!

+ 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. [SOLVED] Open External File, Write Data, Save and Close without prompt.
    By dwbennett in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-29-2015, 11:09 PM
  2. Data from External Source
    By HarryGreenwood in forum Excel General
    Replies: 2
    Last Post: 01-27-2015, 10:08 AM
  3. Amend macro to prompt for file name/path of source data
    By dshilan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-13-2012, 06:12 AM
  4. Getting data from external csv source
    By ajocius1 in forum Excel General
    Replies: 1
    Last Post: 07-18-2008, 10:52 AM
  5. External Data Source updating on open, how to turn back on prompt.
    By kristy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-15-2005, 02:06 AM
  6. Excel 2003 prompt to update external data links
    By Joel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-18-2005, 03:00 PM
  7. [SOLVED] External Data Source
    By S Walker in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-16-2005, 12:45 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