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...
Hi all I have the following code that works to an extent. Unfortunately, it inserts the data and shifts all other cells to the right. I need it to overwrite the existing data. Any help is appreciated!
'Import text dates
Sheets("Split Request").Select
Set ws1 = ActiveSheet...
Why am I getting the following Error Message:
The Query 'Evidentials' was not found.
Application.CutCopyMode = False
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data...
Hi all,
Using Excel 365.
Is my the background refresh false tripping over another background refresh?
I'm getting
There are 4 worksheets each with 1 Table created from Power Query Close & Load
With wb
For Each ws In .Worksheets
For Each lo In ws.ListObjects...
I'm currently generating Querytables via vba since Windows Queries don't carry over to MAC . Is it possible to use this method for older versions of Excel that don't have Power Query /Get and Transform?
Additionally how can I access QueryTable Events { QueryTable object (Excel) } ? I don't...
Hi all,
Been searching for hours into the wee morning about this but to no avail.
I'm running a QueryTable (first time using this) to scrape some csv info from a webpage...then I format/clean the scraped data.
This works on the first time, but not on the second/third/fourth, etc time. By...
Hi All,
having a real problem with the below which I cant seem to solve. For some reason it doesn't seem to delete the query table at the very end of the code and the data just stays there
Sub test()
Const SheetName = "Sheet1"
Dim QT As QueryTable
'Get rid of all the...
Hello!
I googled a code to open a text file and I can't remember where I found the code. To be honest, I don't know what a querytable is yet. The file format is actually a "*.eft" which can be opened up in notepad.
I want to achieve 2 things:
open the textfile in my CURRENT workbook and the...
I have various workbooks that I've used for years to import data from .csv (text) files that were downloaded from my company system. These were run using buttons and Querytable Refresh macros, and worked beautifully, omitting unwanted data, maintaining my formats and updating/inserting...
This was posted a few years ago but never resolved. I have a web connection that retrieves data from the company website.
ActiveWorkbook.Connections("my_Connection").Refresh
Currently this is the code I use to clear, but it also removes the link...
I have the following code which places results from a web query into a QueryTable, starting at cell B2:
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & url, _
Destination:=Range("$B$2"))
.WebFormatting = xlNone
.WebSelectionType = xlSpecifiedTables...
Many years ago, I found this wonderful code on mrexcel.com that let me update all the query tables just by changing the connection string. A few Excel versions later, my code is no longer working for all the queries. It works with all the tables created in older versions of Excel, but not the...
I am getting "Run-time error '-2147287038'(80030002)" when the code getting to populating the recordset. I can always fall back on embedded SQL, but want to introduce my team to faster techniques.
What I thought of trying was to open the connection, the use QueryTable.Add to bypass the...
Hi
I'm new to using List Objects and Query Tables in VBA, and have been using some of the many tips I've found through Google, but I'm a bit stuck now.
I am writing some VBA to:
check if a List Object Query Table already exists
if it doesn't, I want to create it using passed SQL and...
I have the code below to do a query that is working fine:
With ActiveSheet.QueryTables.Add(Connection:=ConnDB, Destination:=ActiveSheet.Cells(LRow, 2), SQL:=SQL)
.Name = "ANALISEVD"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False...
HI ALL, FIRST OF ALL, SORRY ABOUT MY ENGLISH, IM NOT AMERICAN.
I HAVE AN ADODB.CONNECTION, FROM I QUERY DATA USING AN ADODB.RECORDSET, SO I ADD A QUERYTABLE TO PASTE THIS DATA ON MY SHEET USING REFRESH.
I HIGHLY WANT THAT THIS QUERYTABLE REFRESHS ALONE, SO THE DATA IS UPDATED WITHOUT THE NEED...
Hi,
i have a report created in txt using a powershell script and i'm trying to import data from report to excel using QueryTable. The report is similar to this but have much more rows
<tbody>
Cluster
Name
Node
State
-------
----
----
-----
Server1
Cluster Disk
Server1-nodeA
Online...
I have set up a query from a pervasive sql database and would like to write some code that should execute before the query table is refreshed.
I have put the following code in the sheet1 object where the table is located. This is just to see if I can get the before refresh event to work:
Private...
I built an Excel workbook that uses Microsoft Query via VBA to pull data in from another Excel workbook. I pass different parameters to the VBA to perform a search for purchase order information. My problem is that this must run on various version of Excel (from 2000 to 2010) therefore I find...
Hi All,
I've scavenged some code from a forum and I'm getting a subscript out of range error and I'm not sure what's going on. I'm really pretty new to VBA so go easy on me please. Also, sorry for the code snippets not being up to snuff in terms of formatting...I couldn't get the MrExcel add...
We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the icon in the browser’s toolbar. 2)Click on the "Pause on this site" option.
Go back
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
1)Click on the icon in the browser’s toolbar. 2)Click on the toggle to disable it for "mrexcel.com".
Go back
Disable uBlock Origin
Follow these easy steps to disable uBlock Origin
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
Go back
Disable uBlock
Follow these easy steps to disable uBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.