1. L

    VBA - Overwrite current data with text file data

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

    Error on creating Query Table?

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

    Power Query Background Refresh False Error

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

    When were Querytables added to excel?

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

    QueryTable, working on First query...but source data is affected on 2nd, 3rd query

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

    VBA Issue - Not Clearing Contents - Querytable

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

    Why does my text file open in a new workbook?

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

    Convert Text Data Queries to use Excel file as source file

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

    Clearing cell contents but keeping the web connection

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

    Store the results of a QueryTable in an array rather than a range of cells

    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...
  11. 9tanstaafl9

    Use VBA to update ODBC path of Visual FoxPro query table connection

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

    VBA-ADO recordset problem pulling from Access

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

    Updating a List Object QueryTable with new SQL

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

    Exclude header from a QueryTable

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

    Managing querytables, connections, recordsets

  16. U

    Error with VBA to copy data from txt file to excel

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

    Query table before refresh event not calling

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

    VBA With Selection.ListObject.QueryTable as string?

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

    Subscript out of range error in query table code

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

    How do I vary .TextFileColumnDataTypes array?

    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;\\*****\" &...

We've detected that you are using an adblocker.

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