Activesheet.querytables.add Runtime Error 1004
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might
Runtime Error 1004 Vba
have Meta Discuss the workings and policies of this site About runtime error 1004 excel 2010 Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting runtime error 1004 application-defined or object-defined error ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7
Runtime Error 1004 Excel 2013
million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up VBA Error 1004 - QueryTable.Add up vote 0 down vote favorite I am getting a very strange error in VBA (1004) when trying to import a csv file. Here is my code: With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;" & ThisWorkbook.Path
Runtime Error 1004 Method Range Of Object _global Failed
& "/" & "IJR" & ".csv", Destination _ :=Range("$A$1")) .Name = "IJR" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .TextFilePromptOnRefresh = False .TextFilePlatform = xlMacintosh .TextFileStartRow = 2 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = True .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(3, 9, 9, 9, 9, 9, 1) .Refresh BackgroundQuery:=False .UseListObject = False End With It gives me the error on .Refresh BackgroundQuery:=False. IJR.csv IS a file in the same directory as my workbook. I have a bunch of csv files in the directory. The strange thing is, when I change "IJR" to "AA" or "HES" (which are also csv files in the directory), the code works. Most of the csv filenames do not work ("USO.csv", "AAL.csv", "AAPL.csv", "HD.csv", etc.), however a few of them do (so far just "AA.csv" and "HES.csv"). Theyre all identically forma
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more runtime error 1004 select method of range class failed about Stack Overflow the company Business Learn more about hiring developers or posting ads
Runtime Error 1004 Pastespecial Method Of Range Class Failed
with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow runtime error 1004 select method of worksheet class failed is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Excel 2007 - 1004 Run-time error Refresh Query Table up vote 1 http://stackoverflow.com/questions/35787556/vba-error-1004-querytable-add down vote favorite It appears that this error is so generic that many of the existing solutions address different issues. In my case I have created a macro that works in Excel 2010 and does not work in Excel 2007. Here is a summary of the code: cn = "ODBC;Driver={SQL Server Native Client 10.0};Server=serverName;Database=dbName;Trusted_Connection=yes;" sql = "select top 10 * from tableName" Dim S As Worksheet Set S = ActiveWorkbook.Sheets("Medical") http://stackoverflow.com/questions/16151560/excel-2007-1004-run-time-error-refresh-query-table With S.QueryTables.Add(Connection:=cn, Destination:=S.Range("B1")) .CommandText = sql .Refresh BackgroundQuery:=False End With This code executes perfectly in Excel 2010, but errors out in Excel 2007. Do I need to change the connection string structure for 2007, or is there another issue at work? excel vba odbc runtime share|improve this question edited May 6 '13 at 22:06 Undo♦ 18.3k147298 asked Apr 22 '13 at 16:03 lance 26115 Error 1004 is generic error but do you have the actual error text? Should say something like Runtime error 1004: followed by more description - that will help narrow down the problem. –AxGryndr Apr 22 '13 at 16:20 Run-time error '1004': General ODBC Error –lance Apr 22 '13 at 16:39 Is there a query in range B1 of the Medical sheet? If you right click in the cell you should see the refresh option. –AxGryndr Apr 22 '13 at 16:54 It does in 2010, but not in 2007. –lance Apr 22 '13 at 17:35 Instead of .Commandtext can you try changing S.QueryTables.Add(Connection:=cn, Destination:=S.Range("B1")) to S.QueryTables.Add(Connection:=cn, Destination:=S.Range("B1"), Sql:=sql)? –AxGryndr Apr 22 '13 at 17:51 add a comment| 3 Answers 3 active oldest votes up vote 2 down vote accepted Solution: All machines I
+ Ask a Question Need help? Post your question and get tips & https://bytes.com/topic/access/answers/964830-run-time-error-1004-query-tables-add-connections-need-help solutions from a community of 418,417 IT Pros & Developers. It's quick & easy. Run time error-1004 on query tables.add connections - need help. P: 57 aflores41 http://www.ozgrid.com/forum/showthread.php?t=161094 Expand|Select|Wrap|Line Numbers Submacro1() 'DimfrmAsUserForm DimIAsInteger 'Tocouldbeasmanyasyouwant.Ionlyput25asalimit. ForI=1To25 DimaAsString a=Sheet1.Cells(I,1).Value DimbAsString b=ActiveSheet.Cells(I,2).Value 'getURLs 'optionafter"URL;"&a 'UserForm1.txtbox1.value WithActiveWorkbook.ActiveSheet.QueryTables.Add(Connection:="URL;"&a,Destination:=Range("$A$1")) 'WithActiveWorkbook.ActiveSheet.QueryTables.Add(Connection:="URL;"&a,Destination:=Range(b)) .Name=_ "its_details_value_node.html?nsc=true&listId=www_s201_b9233&tsId=BBK01.ED0439" .FieldNames=True .RowNumbers=False .FillAdjacentFormulas=False .PreserveFormatting=True .RefreshOnFileOpen=False .BackgroundQuery=True .RefreshStyle=xlInsertDeleteCells .SavePassword=False runtime error .SaveData=True .AdjustColumnWidth=True .RefreshPeriod=0 .WebSelectionType=xlEntirePage .WebFormatting=xlWebFormattingNone .WebPreFormattedTextToColumns=True .WebConsecutiveDelimitersAsOne=True .WebSingleBlockTextImport=False .WebDisableDateRecognition=False .WebDisableRedirections=False .RefreshBackgroundQuery:=False 'MsgBox"Done!" EndWith NextI 'closeallforms 'ForEachfrmInUserForms 'Unloadfrm 'Nextfrm EndSub Hello, Code above is having issue when ran from a form. The code works if it's ran from spreadsheet however when placed in a button in the form it fails with runtime error 1004 error code 1004. Please help. I've researched and most of the blogs weren't very helpful. Thanks. I use this VBA to pull URLs from Sheet one (craigslist URL) then scrape it up to 25 sites using loop function. This works when manually placed in sheet but not in button of excel. Nov 13 '15 #1 Post Reply Share this Question 16 Replies Expert Mod 2.5K+ P: 4,993 zmbd what this is saying is that something isn't formatted correctly. Just to be sure, this is the exact same code that works in the worksheet level module? What I would do here is instead of referencing "activesheet" I would reference the worksheet either by name or index. It's possible that the sheet is not being seen as active when the form has loaded. Nov 13 '15 #2 reply P: 57 aflores41 Not working. I would get the run-time error '-2147024809 (80070057)'; "The destination range is not on the same w
Way | Trading Add-ins For Excel | Convert Excel Into Calculating Web Pages Excel Web Pages | Produce Clean Efficient VBA Code Every Time | Build Automated Trading Models In Excel | Excel Web Pages | Excel Video Training Forum New Posts FAQ Calendar Forum Actions Mark Forums Read Quick Links Today's Posts What's New? Advanced Search Forum HELP FORUMS Excel General Trapping run-time error 1004 when web query fails Excel Training / Excel Dashboards Reports If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. If you don't like Google AdSense in the posts, register or log in above. Click here to view the relaunched Ozgrid newsletter. Results 1 to 6 of 6 Thread: Trapping run-time error 1004 when web query fails Thread Tools Show Printable Version Search Thread Advanced Search December 31st, 2011 #1 jjessary View Profile View Forum Posts Senior Member Join Date 3rd February 2006 Location St. Louis, Missouri USA Posts 161 Trapping run-time error 1004 when web query fails Hello All, I am trying to trap run-time error 1004 Unable to open http://... that occurs when I am running a series of web queries. The error occurs because the website I am querying goes down temporarily so the web query is unable to connect with it. I have verified this by pasting the URL into a regular browser window. The browser cannot make connection with the particular URL or the home page. My code is working fine when the site is up, but when it is temporarily down, my code throws the 1004 error and waits for me to hit debug and then F5 to restart my code. I would like to trap this error and restart the code automatically. I have the following code that I pieced together from various contributors and sites, but it is not trapping the error. VB: Sub QueryTheSite() Retry: On Error Goto ErrTest With ActiveSheet.QueryTables.Add(Connection:=myURL, Destination:=Cells(TopRow, 2)) .Name = myTableName .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = False .RefreshPeriod = 0 .WebSelectionTyp