querytable

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...

Hi, I have found similar posts on this but not one with a solution. I'm trying to import a .csv file via a QueryTable but I want to vary the .TextFileColumnDataTypes so that a different array of column types are entered. With ActiveSheet.QueryTables.Add(Connection:="TEXT;\\*****\" &...