Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. tchor7

    Excel Run Time error 1004 for different user/machine - ListObject

    Hi - just an update. Using .CommandTxt solved the problem. Still don't know why some machines were erroring out, probably had to do with some security settings on those particular machines. I ended up adding the following code: lo.QueryTable.CommandText =...
  2. tchor7

    Excel Run Time error 1004 for different user/machine - ListObject

    Skip - I'm going to try and fix this in a somewhat convoluted way. Going to add the .CommandText to append with the SQL string. I have 7 querytables, so this shouldn't take much of a concerted effort. Will let you know how that goes. Thanks, -Thoeum
  3. tchor7

    Excel Run Time error 1004 for different user/machine - ListObject

    Skip - could there be an issue with the pc that this is failing on, and not the code itself? I work for a very large company and it appears that on some of these laptops, where re-imaging had occured, they could have put some extra security that I do not know about. I'm just speculating, but...
  4. tchor7

    Excel Run Time error 1004 for different user/machine - ListObject

    Hi Skip - still getting a run time error 1004. This approach created another problem. I have some tables that are connected to a SQL database. This particular update replaces the SQL connection strings (on a network server). So this approach doesn't appear to work. Any other suggestions...
  5. tchor7

    Excel Run Time error 1004 for different user/machine - ListObject

    Below is the code for changing the connection string. It appears to work (I check the connection string, and its updated). Just don't quite undersand why the SQL string would be deleted. Sub ChangeConnections() Dim sPath As String, sPath2 As String, sDB As String Dim ws As Worksheet...
  6. tchor7

    Excel Run Time error 1004 for different user/machine - ListObject

    Hi - below is the connection string (which gets updated when the user saves the excel file to their local directory): Connection String: DSN=Excel Files;DBQ=C:\Documents and Settings\tc074h\Desktop\C-PRT 6.0.xlsm;DefaultDir=C:\Documents and...
  7. tchor7

    Excel Run Time error 1004 for different user/machine - ListObject

    Hi Skip - its actually just referencing itself. i.e. the table exists on the same workbook, different sheet. Thanks, -Thoeum
  8. tchor7

    Excel Run Time error 1004 for different user/machine - ListObject

    Thanks for your reply Skip. But the error still remains. After some research, I see that in the "Connection Properties" for the QueryTable, that the SQL Command Text has disappeard! Which now makes sense why the refresh is greyed out. So why is that the SQL command text is dissappearing on...
  9. tchor7

    Excel Run Time error 1004 for different user/machine - ListObject

    One more thing. Forgot to mention that the Querytable that is referenced has the "refresh" selection disabled (i.e. greyed out when I right click on the Querytable to try and refresh manually). It appears to be some security setting issue. Thanks, -Thoeum
  10. tchor7

    Excel Run Time error 1004 for different user/machine - ListObject

    Hi - on some machines, I am getting "run time error 1004". We are all on Microsoft XP Pro 2002. And all running Excel 2007. I have had the users edit their macro security settings to "Enable all macros" and "Trust access to VBA project object model". I am stumped. Thanks in advance for your...
  11. tchor7

    Excel - Return Function into an Array

    Thank you PH! Worked perfect with the ReDim Preserve -Thoeum
  12. tchor7

    Excel - Return Function into an Array

    ...As Variant, bR As Range, i As Integer arange = StoreRanges("IN", Range("STstart"), Range("Data")) For i = 0 To UBound(arange) '***arange is empty here Debug.Print arange(i) Next i End Sub Function StoreRanges(ls As String, sR As Range, cR As Range) As Variant Dim...
  13. tchor7

    Excel queryTable connection string query table missing?

    Thanks Skip - I made the changes you suggested. Just had use the replace() fn and it works like a charm! much thanks. lo.QueryTable.Connection = Replace(lo.QueryTable.Connection, stOldConnection, sConnection) -Thoeum
  14. tchor7

    Excel queryTable connection string query table missing?

    Hi Skip - thanks for the reply. It did loop through, but now getting error: 1004 My code: sPath = ThisWorkbook.Path sDB = ThisWorkbook.Name sConnection = "DSN=Excel Files;DBQ=" & sPath & "\" & sDB & ";DefaultDir=" _ & sPath & "\" _ &...
  15. tchor7

    Excel queryTable connection string query table missing?

    Looks like it cannot find my query tables??? below is an almost identical thread to this issue: http://www.tek-tips.com/viewthread.cfm?qid=1099238 Many searches on the internet proposed Skips solution: For Each ws In ThisWorkbook.Worksheets ws.select For Each qt In ws.QueryTables...
  16. tchor7

    Excel Query into Validation List

    Hi Skip - ended up using a parameter query, which worked great. Question: Is it possible to use a parameter query for multiple cell references. I found your answer in the thread below, but that solution will not work for me. http://www.tek-tips.com/viewthread.cfm?qid=1318984&page=424 e.g...
  17. tchor7

    Excel Query into Validation List

    Hi Skip, thanks for the response. I like the idea of using a dynamic QueryTable. I was also considering storing the data in a recordset. http://www.ozgrid.com/forum/showthread.php?t=18289&page=1 Ultimately, I will be querying from two worksheets. If I use the Named Range method, I will have...
  18. tchor7

    Excel Query into Validation List

    Skip - much thanks. Got the query to run, but now need to pass the results to a validation list. The validation list will be populated in a column (with 500+ rows), so the procedure will repeat. I will repeat this entire process about 10 times for other criterias. I'm testing if performance...
  19. tchor7

    Excel Query into Validation List

    Multiple Validation Lists in worksheet (wk1). Validation list needs to pull data from another worksheet (wk2) - which houses 5+ columns/variables. Access db, etc not an option. Need to query wk2 with 1+ parameters/variables. Found articles on MS Query and on Validation lists, but none linking...
  20. tchor7

    Business card background

    v2003. I am using the business card template, but need the background color for the full page(10-up). This is so that when I have the cards cut, it makes the bleed possible (so the white background doesn't show). One solution would be to set the page to a full page, and copy my business card...

Part and Inventory Search

Back
Top