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

[tchor7]

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

Error is on the following line:
Selection.ListObject.QueryTable.Refresh

+++++++++++++++++++++++++++++++++++++++++++++++++
code:
++++++++++++++++++++++++++++++++++++++++++++++++++
Sub QryUpdateRegion()
'Author: Thoeum Chor 5/11/2011
'Updates Market and Project_Status queries based on Region selected
Application.ScreenUpdating = False
Worksheets("Values1").Visible = True
Sheets("Values1").Select
Range("M1").Select
Selection.ListObject.QueryTable.Refresh
Range("W1").Select
Selection.ListObject.QueryTable.Refresh

Sheets("CASPR Upload Request").Select
Range("B10").Select
Worksheets("Values1").Visible = False
Application.ScreenUpdating = True

End Sub

 

[tchor7]

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

 

[SkipVought]

hi,

give this a try. It will refresh ALL queries on ALL sheets. If you need to discriminate, it just takes a small change...

Sub QryUpdateRegion()
    Dim ws As Worksheet, lo As ListObject
    
    For Each ws In Worksheets
        For Each lo In ws.ListObjects
            lo.QueryTable.Refresh False
        Next
    Next
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tchor7]

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 some computers and not on others? Is there some security/configuration setting that needs to be changed?

Thanks,
-Thoeum

 

[SkipVought]

What data source is being referenced?

Is this source on an accessable network?

Do the others have the necessary permission to access this source?

Is the path to the source data DRIVE specific or does it reference the logical server name?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tchor7]

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

Thanks,
-Thoeum

 

[SkipVought]

Where is this workbook saved? Same questions as above.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tchor7]

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 Settings\tc074h\Desktop;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

Data source is in the excel file on a different sheet (see code below for SQL):

SELECT DISTINCT `Market$`.Market
FROM `Market$` `Market$`
WHERE (`Market$`.Region=?)
ORDER BY `Market$`.Market


No permission settings (i.e. no passwords were created). This file is being used by hundreds of users, but approx 5-10 are running into this issue.

Thanks,
-Thoeum

 

[SkipVought]

the connection string (which gets updated when the user saves the excel file to their local directory)

HOW?

Please post the code and process that changes this string.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tchor7]

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, lo As ListObject
Dim sOldConn As String, sNewConn As String
'Author: Thoeum Chor 5/10/2011
'Replaces the queryTable's connection string
'loops through each worksheet and updates the connection string
'allows workbook to be moved into another location

sPath = ThisWorkbook.Path
sDB = ThisWorkbook.Name

'check if '\' is in root directory
If Right(sPath, 1) = ":" Then
sPath2 = sPath & "\"
Else
sPath2 = sPath
End If

sOldConn = ThisWorkbook.Worksheets("Admin").Range("A2")

sNewConn = "DSN=Excel Files;"
sNewConn = sNewConn & "DBQ=" & sPath & "\" & sDB & ";"
sNewConn = sNewConn & "DefaultDir=" & sPath2 & ";"
sNewConn = sNewConn & "DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"


For Each ws In ThisWorkbook.Worksheets
For Each lo In ws.ListObjects
lo.QueryTable.Connection = Replace(lo.QueryTable.Connection, sOldConn, sNewConn)

Next
Next

ThisWorkbook.Worksheets("Admin").Range("A2") = sNewConn

End Sub

 

[SkipVought]

try this. You don't need all that OLD/NEW replacement. Simply assigning the new string will suffice. I do this often. I actually code each of my queries and have this connection assignment code in the procedure along with my SQL, which also often changes with user parameters. Also, this is an ODBC process, which should be represented in the string as noted.

Sub ChangeConnections()
    Dim sPath As String, sDB As String
    Dim ws As Worksheet, lo As ListObject
    Dim sNewConn As String
     'Author: Thoeum Chor 5/10/2011
     'Replaces the queryTable's connection string
     'loops through each worksheet and updates the connection string
     'allows workbook to be moved into another location
     
    sPath = ThisWorkbook.Path
    sDB = ThisWorkbook.Name
     
    sNewConn = "ODBC;DSN=Excel Files;"
    sNewConn = sNewConn & "DBQ=" & sPath & "\" & sDB & ";"
    sNewConn = sNewConn & "DefaultDir=" & sPath & ";"
    sNewConn = sNewConn & "DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"

    For Each ws In ThisWorkbook.Worksheets
        For Each lo In ws.ListObjects
            lo.QueryTable.Connection = sNewConn
        Next
    Next
    
    ThisWorkbook.Worksheets("Admin").Range("A2") = sNewConn
    
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tchor7]

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?

Thanks,
-Thoeum

 

[SkipVought]

You could exclude that server in some control logic.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tchor7]

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 its worth a shot.

Any ideas where we could find out if the security is an issue?

Thanks,
-Thoeum

 

[SkipVought]

Yes, we also have instances where a user cannot manually configure an ODBC Driver, due not having administrative rights.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tchor7]

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

 

[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 = ThisWorkbook.Worksheets("Admin").Range("A" & n + 4)

I know the code isn't clean, but this is somewhat of a temporary fix. So it works good for now.

Thanks,
-Thoeum