excel ADODB.connection to itself ? how?

[djorkaef]

Hi,

I am new to vba programming in excel and I saw this forum that seems to have good member information. Maybe some of you can help me with this.
I created a VBA button in Excel that creates a Word-document (report) based on data in this same excel file. I used the ADODB connection to run query's on the manual filled in user data and excel tables in order to get the report data. It is like this:

 

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Provider = "MSDASQL"
cn.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
     "DBQ=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "; ReadOnly=True;"
 
strQuery = "SELECT " & _
" SupplierTech.[rptTechGroup] " & _
", SupplierTech.[rptTech] " & _
", SuppliersWelcome.[rptSuppliers] " & _
", IIF(SuppliersQual.[rptCountry] is null, 'Not named', SuppliersQual.[rptCountry]) " & _
", IIF(SuppliersQual.[rptSites] is null, 'Not named', SuppliersQual.[rptSites]) " & _
", IIF(SuppliersQual.[rptContract] is null, 'Not named', SuppliersQual.[rptContract] )" & _
", IIF(SuppliersQual.[rptAdditional] is null, 'No', SuppliersQual.[rptAdditional])" & _
", IIF(SuppliersWelcome.[rptTechnologyCommaSep] is null, 'No', SuppliersWelcome.[rptTechnologyCommaSep])" & _
", IIF(SuppliersQual.[rptDirectly] is null, 'No', SuppliersQual.[rptDirectly])" & _
", IIF(SuppliersQual.[rptPerson] is null, 'No', SuppliersQual.[rptPerson])" & _
", IIF(SuppliersQual.[rptTelephone] is null, 'No', SuppliersQual.[rptTelephone])" & _
", IIF(SuppliersQual.[rptEmail] is null, 'No', SuppliersQual.[rptEmail])" & _
", IIF(SuppliersQual.[rptWebsite] is null, 'No', SuppliersQual.[rptWebsite])" & _
", IIF(SuppliersQual.[rptLocal] is null, 'No', SuppliersQual.[rptLocal] )" & _
", IIF(SuppliersQual.[rptRegional] is null, 'No', SuppliersQual.[rptRegional] )" & _
", IIF(SuppliersQual.[rptGlobal] is null, 'No', SuppliersQual.[rptGlobal])" & _
" FROM " & _
" [Welcome sheet$BJ17:BL1000] SuppliersWelcome, [Welcome sheet$M18:O1000] SupplierTech, [Qualified suppliers$A4:BZ1000] SuppliersQual WHERE " & _
" (SuppliersWelcome.[rptTechnology] like '%' + SupplierTech.[rptTechGroupTech] + '%') AND " & _
" SuppliersWelcome.[rptSuppliers] = SuppliersQual.[rptSupplier]"
Set RsReport = cn.Execute(strQuery)
Sheet1.Cells(19, 30).CopyFromRecordset RsReport

If the excel file is not opened as read-only and I get the ADODB connection to itself, it will detect the manual data the user has made, even if the user did not save the excel file first. Then the report works fine.

But if the user opens it as a read-only file and presses the 'create report' button, the ADODB connection will not detect the changes (manual user data) the user has made and it will just 'open' the document as it was during the last save.
So actually I need something like

 
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Provider = "MSDASQL"
cn.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
     "DBQ=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & " [Read-Only]"; ReadOnly=True;"

Do you see what i mean? Is there any way to get this? To refer to the actual opened read-only document? Or any other solution?

Best Regards,

Joris

[djorkaef]

I solved the problem by first saving the file if it was read-only.

[romperstomper]

FYI, due to memory leaks, it is not a good idea generally to use ADO to query an open workbook. You should be fine if it's only a couple of queries, but otherwise it's best avoided.