querytables

I wrote a VBA code to download stock data from Yahoo Finance. To explain the bug that I discovered, I wrote the following VBA program. The VBA program downloads data for stock symbols SPY, XXX, and QQQ from the Yahoo Finance website and displays the downloaded data on sheet 1. Because the stock...

I found the following class file and have edited it to run certain procedures after a background refresh of a QueryTable has completed. The class events work properly if the QueryTable supplied isn't contained within a ListObject (ie: created via VBA) but fail to trigger if they are. I've tried...

I have created a Macro as follows to create a web query and loop through the alphabet to append the data of multiple queries into a worksheet. The macro is connected to a button on one of the worksheets in my Workbook. Issue: I can run it on my computer (Trust Center settings set to Macros -...

I have coded a web query as follows: With ActiveSheet.QueryTables.Add(Connection:= _ "URL;https://query2.finance.yahoo.com/v7/finance/options/" & UnderlyingSymbol & "?date=" & ExpiryEpoch & "", _ Destination:=Range("OptionQuote")) .BackgroundQuery = True .TablesOnlyFromHTML =...

I am trying to download a CSV file through a QueryTable in VBA. I don't know why this is occurring because I can download the CSV file just fine when I use a browser. The path to the CSV file is: https://www.tsp.gov/InvestmentFunds/FundPerformance/index.html?whichButton=CSV The VBA code to...

Hello, I have written some VBA code (below) that sets up an external data connection in a workbook, queries a stored SQL procedure on an external server and imports the resulting recordset into a QueryTable. However, I have a stored procedure saved on a server that contains two select...

Hi, I'm running a QueryTables.Add(Connection:="URL".. that generates a Google site: search. It works fine on my laptop computer, generating the results I expect (the indexed URLs on a site). However, on my desktop, which is a faster machine, I am only generating this Google message...

Hello, I am using VBA to call for an export of the SharePoint list I am using into an Excel workbook. The code, seen below, works nearly perfectly but runs into one snag. There is one column in my SharePoint view that is Linked to a different SharePoint List. This column is one of the...

Environment: Excel 2010 in Windows 7 Question: Can QueryTables gracefully handle encountering a .zip file when expecting .txt? We have a database of text-based information logs from a large number of machines. The log files are stored as the same file name, but located in a folder path that...

The following code gives me the "Run-time error '9': Subscript out of range" error on the last line, but only the first time it is run in a blank workbook. mypath = [windows network path]<WINDOWS Path Network> myfile = [text file name]<FILENAME extention without> With...

Hi, I have a VBA program that pulls messages out of another system (that posts messages on our intranet within HTML code) and processes them further in an Excel program. We have used this for several months with everything working great, until we figured out that Excel was ignoring text within...

I've built an Excel VBA Application using QueryTables that connects to an internal server. Users must be logged in through VPN to connect. Sometimes, however, even if a user is logged into VPN, excel sometimes doesn't realize it. I know this is the case bc if I hit the same URI in a web...

I'm trying to build a workbook that builds a query table based on a user's entry of a parameter and have this change when they enter a new parameter. I've built the macro so that it's structured like so 1) clear any existing data 2) get data from report 3) copy and transpose it to another...

My workbook will have between 20 and 200 sheets (situation dependent) with querytables added to each sheet. With that quantity I am hoping to not have to make each sheet active to get a query added. However, my testing so far shows it works every time the current query is referring to the...

I am trying to create a dynamic QueryTable connection. The site I am using displays a table of data as well as some filters. Everytime you change the filter, the webpage reloads with different data. I need to import these various data tables into Excel. How can I automate this via VBA? I...

Hey guys. Why dosnt this syntax import data from the file I choose? Sub Makro1() Dim filename As String filename = Application.GetOpenFilename("Text Files (*.tsp),*.tsp", , "Please selec text file...") With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;filename", _...

Hi! Recently I created a VBA Macro which automatically adds web query tables into a worksheet. I have about 100+ of them in a worksheet and it loops by itself once I run the macro. And the problem is that once in a while it just paused at one of the query for a long time (Statusbar shows...