Recent content by tchor7

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

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

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

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

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

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

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

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

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

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

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

Hi - I have a procedure that calls a Function to return an array. The Function uses the .Find method to store into an array. Problem arises after the function is called and the array is returned to the sub procedure (my 'arange' is empty). There must be some conflict with returning an array as...

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

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

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