querytable

  1. 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...
  2. 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...
  3. 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...
  4. 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...
  5. 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...
  6. 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...
  7. 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...
  8. 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...
  9. 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...
  10. 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...
  11. P

    Managing querytables, connections, recordsets

    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...
  12. 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...
  13. 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...
  14. 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...
  15. 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...
  16. 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;\\*****\" &...
  17. O

    RunTime Error 1004 Invalid Object Name when connection to SQL Server Database

    Hi, I've searched the forum and not found any posts reporting this problem. When running a QueryTable command in VBA I keep getting the error: RunTime Error 1004: Invalid object name: 'MyDatabase.Data.Amount_Source' What I cannot understand is that when I copy the SQL Query Command Text...
  18. N

    How do you refresh SQL data in Excel with VBA?

    I have a correctly connected query table on a worksheet called "SQL Data" that I can refresh manually no problem. However, when I run this VBA code I get the following error: Dim ws As Worksheet Set ws = Worksheets("SQL Data") 'Refresh the SQL Table ws.QueryTables(1).Refresh >>ERROR...
  19. J

    Incomplete Datasource Error when Queryinf Another Workbook

    I have the following VBA code which is failing with an "incomplete datasource" error on the last line: Application.ScreenUpdating = False Worksheets(Import).Select Range("F10").Select Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False The name of the ListObject is...
  20. L

    Changing queryTable name in 2007/2010 using VBA

    Since Excel 2007 querytables are part of a Listobject. I've been able to succesfully alter the CommandText and Connection property using VBA For Each ws In ActiveWorkbook.Sheets For Each lo In ws.ListObjects If lo.SourceType = XlListObjectSourceType.xlSrcQuery Then...

Some videos you may like

This Week's Hot Topics

Top