Excel Querytable Connection to Password Protected Workbook Problem

[RustyAfro]

I'm am trying to create a query table using VBA in an open password protected Excel workbook called RustysWorkbook.xls. This works fine as long as I have NOT launched any other excel workbooks (on the windows taskbar) prior to executing my code.

But if i launch any other workbooks before opening RustysWorkbook.xls, something strange happens. First, Excel switches the focus from RustysWorkbook.xls to the workbook preceding it (on the windows taskbar), then prompts me for the password to RustysWorkbook.xls, then and reopens RustysWorkbook.xls in a different window.

This behavior begins at the following line of code => .Refresh BackgroundQuery:=False

How to I get my query to refresh successfully even if there is already another workbook preceding it ?



Here's my code
Sub QueryData()
On Error GoTo ErrHandler:

Dim wks As Worksheet
Dim DestinationRange As Range
Dim qtQuery As QueryTable
Dim connString As String
Dim sqlString As String
Dim CurrentDirectory As String

Set DestinationRange = wkstmpCombined.Range("A2")

For Each qtQuery In wkstmpCombined.QueryTables
qtQuery.Delete
Next qtQuery
wkstmpCombined.Range(DestinationRange, "Z15000").Clear

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Build connection string path using current location of workbook.
If (Left(ThisWorkbook.Path, 1) = "\") Or (Left(ThisWorkbook.Path, 1) = "C") Then
CurrentDirectory = ThisWorkbook.Path & "\"
Else
CurrentDirectory = GetNetPath(Left(ThisWorkbook.Path, 1))
End If

'Assign connection string
connString = "ODBC;Driver={Microsoft Excel Driver (*.xls)};DriverId=790;" & "Dbq=" & CurrentDirectory & ActiveWorkbook.Name & ";" & "DefaultDir=" & CurrentDirectory & ";"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


'Build query SELECT statement
sqlString = " SELECT *"

'Build query FROM statement
sqlString = sqlString & " FROM {oj `wkstmpPrimary$` `wkstmpPrimary$`" & " LEFT OUTER JOIN `wkstmpAncillary$` `wkstmpAncillary$`" & " ON `wkstmpPrimary$`.PK = `wkstmpAncillary$`.PK} "


'Add querytable w/ dynamic SQL
Set qtQuery = wkstmpCombined.QueryTables.Add(connString, DestinationRange, sqlString)
With qtQuery
.Refresh BackgroundQuery:=False
.RefreshStyle = xlOverwriteCells
End With

ExitFinally:

Set DestinationRange = Nothing
Set qtQuery = Nothing
Exit Sub


ErrHandler:
If Err.Number = vbObjectError + 1001 Then
MsgBox "Your request returned too many results." & "Please refine your search.", vbInformation, "Result Error"
Err.Clear
Else
GoTo ExitFinally
End If


End Sub

 

[SkipVought]

Hi,

How about save/closing the other workbooks, perform the query, reopen the closed workbooks.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RustyAfro]

Thanks SkipVought,

That's a good suggestion, but its not uncommon for the users to have 6-7 different Excels open before they run my app. Having them save and close everything before launching my app will not work as a long term solution.

 

[SkipVought]

I was not referring to "Having them save and close everything"

I was suggeting that your procedure do that.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RustyAfro]

Just in case anyone has this same issue, I decided to just just block the user from using RustysWorkbook.xls if they have any other Excels open.

I call the below function in the .xla file that launches RustysWorkbook.xls. If there are other Excels open, I prevent them from opening RustysWorkbook.xls.


Here's the code :
' RustysWorkbook.xls must be the first instance of Excel for the query tables to work correctly.
' This function will check Windows for any other instances of Excel
' If more than 1 instance in located, then this function will return a TRUE

Function IsExcelAlreadyRunning() As Boolean
IsExcelAlreadyRunning = False

Dim strComputer As String
Dim objWMIService As Object
Dim colExcels As Object


strComputer = "."

Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

'query Win32_Process for all Excel Instances
Set colExcels = objWMIService.ExecQuery("Select * from Win32_Process Where Name = 'Excel.exe'")

'Count the instances of excel returned, if >1 notify user
If colExcels.Count > 1 Then
IsExcelAlreadyRunning = True
MsgBox ("RustysWorkbook.xls has detected other instances of Excel." & vbCrLf & _
"Please close all other instances of Excel and try again ."), vbExclamation, "Attention!"

End If


End Function