How to properly from VBA connection from Excel sheet to another Excel sheet?

Question

I currently have 2 separate Excel worksheets. One is data entry and another is display.

The display uses VBA to connect to data entry to obtain data. Normally, it functions well enough. However I'm required to have the 2 worksheets in separate windows, meaning both can be displayed at the same time in separate windows, in the same screen.

The issue in this scenario is that when I click Execute in display to begin SQL query, the display window opens another data entry worksheet (read-only) and reads that instead of the one I opened initially. Is this issue due to my connection string or my ADODB.Recordset has issues?

Here is the sub which contains the connection string and ADODB.Recordset. Edit: Full code is included to provide full context for those who need it.

Public Sub QueryWorksheet(szSQL As String, rgStart As Range, wbWorkBook As String, AB As String)
Dim rsData As ADODB.Recordset
Dim szConnect As String
On Error GoTo ErrHandler

If AB = "1st" Then
wbWorkBook = ThisWorkbook.Sheets("Inner Workings").Range("B9").Text
End If

Application.StatusBar = "Retrieving data ....."
'Set up the connection string to excel - thisworkbook
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & wbWorkBook & ";" & _
            "Extended Properties=Excel 8.0;"

Set rsData = New ADODB.Recordset
'Run the query as adCmdText
rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly, adCmdText

'Check if data is returned
If Not rsData.EOF Then
    'if the recordset contains data put them on the worksheet
    rgStart.CopyFromRecordset rsData
Else

End If
'Close connection
rsData.Close
'Clean up and get out
Set rsData = Nothing
Application.StatusBar = False
Exit Sub

ErrHandler:
'an error occured in the SQL-statement
MsgBox "Your query could not be executed, the SQL-statement is incorrect."
Set rsData = Nothing
Application.StatusBar = False

End Sub

Sub process()
Call clear
Call testsql("1st")  ' populate 1st Summary
Call testsql("2nd")  ' find Date+Time
Call testsql("3rd")  ' arrange record by newest
Call testsql("4th")  ' show final results
End Sub

Sub testsql(AB As String)

Dim rgPlaceOutput As Range    'first cell for the output of the query
Dim stSQLstring As String     'text of the cell containing the SQL statement
Dim rg As String, SQL As String

If AB = "1st" Then
stSQLstring = ThisWorkbook.Sheets("Inner Workings").Range("B2").Text
Set rgPlaceOutput = ThisWorkbook.Sheets("1st Summary").Range("A2")
End If
If AB = "2nd" Then
stSQLstring = ThisWorkbook.Sheets("Inner Workings").Range("B3").Text
Set rgPlaceOutput = ThisWorkbook.Sheets("2nd Summary").Range("A2")
End If
If AB = "3rd" Then
stSQLstring = ThisWorkbook.Sheets("Inner Workings").Range("B4").Text
Set rgPlaceOutput = ThisWorkbook.Sheets("3rd Summary").Range("A2")
End If
If AB = "4th" Then
stSQLstring = ThisWorkbook.Sheets("Inner Workings").Range("B5").Text
Set rgPlaceOutput = ThisWorkbook.Sheets("Final Summary").Range("A5")
End If

QueryWorksheet stSQLstring, rgPlaceOutput, ThisWorkbook.FullName, AB

End Sub

Sub clear()
ActiveWorkbook.Sheets("1st Summary").Range("A2:BR5000").Value = Empty
ActiveWorkbook.Sheets("2nd Summary").Range("A2:BR5000").Value = Empty
ActiveWorkbook.Sheets("3rd Summary").Range("A2:BR5000").Value = Empty
ActiveWorkbook.Sheets("Final Summary").Range("A5:BR5000").Value = Empty
End Sub

Also another thing I noticed. Depending on which file I open first, it can result in both files creating a read-only copy when I click Execute. If I open Display first then Entry form, both in different instances of Excel, it will create read-only copies of both files.

If I open Entry form first, then Display in again, different instances of Excel, only the read-only copy of Display will appear.

The only time a read-only does not appear is when both files are in a single instance of Excel, which is not what I want.

Edit2:

For more info, here is the SQL I used (4 total)

SQL1 - select * from EntryTable

SQL2 - select A.*,[Date + Time] from Summary1 A left join (select [Die No], max (Date + Time) as [Date + Time] from Summary1 group by [Die No]) B on A.[Die No] = B.[Die No]

SQL3 - select * from Summary2 where [Date + Time] = Date + Time

SQL4 - select Project_No, Die_No, Description, Repair_Details, Status from Summary3

Workbook name in cell B9 = V:\Die Maintenance System v2\Die Maintenance Menu.xlsx

Update: My colleague has tested the system on her PC and tested no problems. I've been told its most likely my Excel settings. But for the life of me, I can't figure out what is causing it. What type of setting is used to prevent the read-only file from appearing?

Edit: I can see that this post has gone on too long. I decided to continue this on a new thread right here.

[wbWorkBook = ThisWorkbook.Sheets("Inner Workings").Range("B9").Text] means that in the display sheet, cell B9 contains the location of the data entry sheet. — hjh93, Jun 06 '17 at 08:19
Can you put a breakpoint on the line `QueryWorksheet stSQLstring, rgPlaceOutput, ThisWorkbook.FullName, AB` and share what values are being passed? — Siddharth Rout, Jun 06 '17 at 10:24
done that already. from what I can see only the clear sub was activated cuz the previous display was erased. — hjh93, Jun 06 '17 at 23:58
I created breakpoints throughout the code, and from what I see the read-only file is open when ever it passes by my ADODB.Recordset. `rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly, adCmdText`. What needs to be changed? — hjh93, Jun 07 '17 at 03:32

score 0 · Answer 1 · answered Jun 06 '17 at 08:40

0

So i would do it with the Workbook.Open() Method.

Sub Example()
Dim wb as Workbook
Dim path as String

path = "C:\Users\User\Desktop\1.xlsx"

set wb = Workbook.Open(path) 
End Sub

Now you can use wb to execute every vba function. Then there a options to check if a workbook is already opened, look here. I dont think you can do that with adodb.

answered Jun 06 '17 at 08:40

Plagon

2,689
1
11
23

the link you shown me seems to be used for when you want to check if file is opened. But how do you convert it so that it can be used to prevent it from opening another copy? – hjh93 Jun 07 '17 at 03:39
some of the methods check if a file is open, if its already opened they will return the opened workbook instead of trying to open it again. – Plagon Jun 07 '17 at 05:54

score 0 · Answer 2 · answered Jun 06 '17 at 09:02

0

I tired using ACE and it worked just fine. It didn't open a new file.

szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & _
            wbWorkBook  & "';" & _
            "Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"

answered Jun 06 '17 at 09:02

Siddharth Rout

147,039
17
206
250

Not working, unfortunately. It goes straight to the error message I made at the end saying query cannot be executed and sql statement incorrect. I forgot to mention that my sql statements uses the same method as the source file. Where it is written in the display excel sheet and VBA is directed to it and read the statement and execute. Is the issue that? – hjh93 Jun 06 '17 at 09:25
My code is tested and tried... What are the values of `szSQL As String, rgStart As Range, wbWorkBook As String, AB As String` – Siddharth Rout Jun 06 '17 at 09:31
Hold on. I'll add the remainder of my code in my question. Hopefully it provides more context to the sub. – hjh93 Jun 06 '17 at 09:40

Wils Mils · Answer 3 · 2017-06-07T10:06:32.560

0

Your SQL FROM clause is referencing a different named range. Please post your SQL text. It must be qualified to correct Named Range or sheet name.

    SELECT Project No, Die No, Description, Repair Details, Status
    FROM DATA1  <- correct this to qualified named range or sheet name

like

   FROM [Entry Form$] 'or

   FROM [Named Range] <- this can be found in Formulas | Name Manager

Edit: I am not sure about your "1st" source workbook's location so let us try to insert the line I commented below

   wbWorkBook = ThisWorkbook.Sheets("Inner Workings").Range("B9").Text
   wbWorkBook = Workbooks(wbWorkbook).FullName  '<- add this line

If it still does not work, please post your SQL AND Workbook name in cell B9.

Edit 2: What is the result if you change the FROM clause like:

   select * from [EntryTable$]

Edit 3: Do you have password? if so, try to disable it first to isolate the problem in read only.

edited Jun 07 '17 at 10:06

answered Jun 06 '17 at 09:03

Wils Mils

613
4
9

I tried changing to sheet name as you said. But the read-only sheet still appears. – hjh93 Jun 07 '17 at 02:46
done that. it triggers the error handler. i'll give you the sql used and the workbook name. – hjh93 Jun 07 '17 at 09:29
its in the question now. – hjh93 Jun 07 '17 at 09:36
no password at all. from what I heard from my collegue, its most likely excel/pc setting. cuz she had no problems with it. but for the life of me I can't figure out what the setting is... – hjh93 Jun 08 '17 at 09:54

How to properly from VBA connection from Excel sheet to another Excel sheet?

3 Answers3