Querytable.commandtext Application-defined Or Object-defined Error
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies application defined or object defined error excel of this site About Us Learn more about Stack Overflow the company Business application defined or object defined error access Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask application defined or object defined error 32809 Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign application defined or object defined error in macro up Application Defined or Object Defined Error on QueryTable up vote 1 down vote favorite I'm trying to take data from a table in SQL and export it to a worksheet using VBA. I have been unable to figure out how to do this without creating a new data connection each time. Here is what I have been doing as of now:
Application Defined Or Object Defined Error In Vba
I am putting the SQL query into a cell and giving the variables names like '#ID' and "Date". In the macro, I am replacing the data within those variables with the actual information. That has not given me any problems thus far as the query runs without issue when using SQL. To export the data from SQL to my worksheet, I am using code that works in other workbooks; however when I run it in this workbook, I am getting the error "Application Defined or Object Defined Error". Here is the full macro: Sub CurrentBreakout() Dim SQLString As String Dim ID As String Dim Date As String Dim year As String Dim daycount As String SQLString = Range("CurrentBreakout") 'This is the range where the query is stored ID = Range("ID") Date = Range("CurrMonth") year = Range("CurrYear") daycount = Range("CurrDayCount") SQLString = Replace(SQLString, "#ID", ID) SQLString = Replace(SQLString, "#Date", Date) SQLString = Replace(SQLString, "#CurrDayCount", daycount) SQLString = Replace(SQLString, "#CurrYear", year) With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "ODBC;DSN=____________;UID=________;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=________;DATABASE=_______;" _ , Destination:=Range("CurrCon")).QueryTable .CommandText = SQLString .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery =
Forum Microsoft Office Application Help - Excel Help forum Excel Programming / VBA / Macros [SOLVED] Selection.QueryTable problem To get
Application Defined Or Object Defined Error Vba 1004
replies by our experts at nominal charges, follow this link to application defined or object defined error vba formula buy points and post your thread in our Commercial Services forum! Here is the FAQ for application defined or object defined error vba range this forum. + Reply to Thread Results 1 to 3 of 3 Selection.QueryTable problem Thread Tools Show Printable Version Subscribe to this Thread… Mark this thread as http://stackoverflow.com/questions/35186925/application-defined-or-object-defined-error-on-querytable unsolved… Rate This Thread Current Rating Excellent Good Average Bad Terrible Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 01-06-2005,11:06 AM #1 JenC Guest Selection.QueryTable problem Hi, I am trying to use the following in an excel (2003) spread sheet: With Selection.QueryTable ..Connection= "ODBC;DSN=Live;UID=admin;;SERVER=LIVE;DBNAME=DATA;LUID=admin;" .CommandText http://www.excelforum.com/showthread.php?t=332932 = strSelectStatement .Refresh BackgroundQuery:=False End With and I keep getting : Error Number: 1004 Error Description: Application-defined or object-defined error I have had a quick look at the help files and online and have tried a number of alternatives but with no success. Any help/tips greatly appreciated. Thanks, J Register To Reply 01-06-2005,12:06 PM #2 Sharad Naik Guest Re: Selection.QueryTable problem Selection.QueryTable will give that error. This is not proper way to refer to a QueryTable. You can do, for example: With ActiveSheet.QueryTables(1) 'if 1 is the correct index of the required querytable. 'else replace 1 with the correct index. Sharad "JenC"
SQL Server 2014 Express resources Windows Server 2012 resources Programs MSDN subscriptions Overview Benefits Administrators Students Microsoft Imagine Microsoft Student Partners https://social.msdn.microsoft.com/Forums/en-US/3339ded6-726b-42b0-8fba-1421c7a05eb4/runtime-error-1004-applicationdefined-or-objectdefined-error-editing-a-data-checking-workbook?forum=isvvba ISV Startups TechRewards Events Community Magazine Forums Blogs Channel 9 Documentation APIs and reference Dev centers Samples Retired content We’re sorry. The content you requested has been removed. You’ll be auto redirected in 1 second. Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove defined or From My Forums Asked by: Run-Time error '1004': Application-defined or object-defined error (Editing a data checking workbook I did not create) Microsoft ISV Community Center > Visual Basic for Applications (VBA) Question 0 Sign in to vote PLEASE HELP ME, I have a deadline I need to meet tomorrow defined or object and in order to complete my work I need to use this 'data checking' sheet that I am not familiar with (please note I am a total newby at VBA so please be kind). I made the changes I needed to and then when I hit the Macro button I recieved "Run-Time error '1004': Application-defined or object-defined error". When I select Debug it takes me to VBA and this is what I can see: Private Sub btnRefresh_Click() Application.ScreenUpdating = False Sheets("AM").Select Selection.QueryTable.Refresh BackgroundQuery:=False Sheets("PM").Select Selection.QueryTable.Refresh BackgroundQuery:=False Sheets("Peaks").Select End Sub Private Sub btnRefresh_Links2_Click() On Error Resume Next Range("A1").Select ActiveWorkbook.UpdateLink Name:= _ "P:\PSG NA Common Folder\OTR 2011 - 2012\2011_12 Worksheet.xls", Type:= _ xlExcelLinks End Sub Private Sub btnRefresh2_Click() Application.ScreenUpdating = False Sheets("AM Contra").Select Selection.QueryTable.Refresh BackgroundQuery:=False Sheets("PM Contra").Select Selection.QueryTable.Refresh BackgroundQuery:=False Sheets("Off Peak").Select Selection.