Writing to a Closed Workbook using ADO/SQL

[bradlydraper]

Hello everyone,

I am designing a program that will involve writing data to a Closed Workbook. Consider this Closed Workbook (Output.xls) to be a Database, where the Open Workbook will occasionally write data to this Closed workbook for Data Storage purposes. For reasons that are rather lengthy to discuss, I would strongly prefer to avoid Opening the other workbook and turning screen updating to false. Writing to this workbook while it remains closed is an important factor.


I have been exploring ADO/SQL in order to achieve this, and I have made some decent progress but need some help referencing what cells to write to in the Closed Workbook.

This code currently transfers the value from cell "A1" in the Open Workbook to cell "A1" in the Closed Workbook.

Sub Example()
    Call ClosedWB("C:\Output.xls", _
    "Select * From [Sheet1$A1:A1]", _
    Sheets("ADO").Range("A1").Value)
End Sub

 
Sub ClosedWB(WorkbookFullName As String, SQL As String, NewValue As String)
    Dim conn As New Connection, rs As New Recordset
     
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & WorkbookFullName & _
    ";Extended Properties=""Excel 8.0;HDR=NO;"""
    
    rs.Open SQL, conn, 1, 3
    rs.Fields(0).Value = NewValue
    rs.Update: rs.Close: conn.Close
End Sub

The part which I'm struggling to understand is

Select * From [Sheet1$A1:A1]

I'm not sure how to work with Ranges in this format, which I currently understand to be a reference to a table range. What I would like to do is identify the LastRow in the closed workbook and write a value to a cell in that offset row.

Can anyone help me with this problem?


Thanks for the help in advance; I will also post a comment explaining my understanding of this SQL/ADO for those who are unfamiliar with this capability and are interesting in getting started.

 

[bradlydraper]

Here's my understanding with a lot of my code that I posted, which I've primarily pulled through various forums and websites explaining ADO/SQL.

Connection- Defines the path or type of Database. With VBA, we're using Microsoft Jet Engine as mentioned above
RecordSet- Either a table or query in the database. Most ADO methods reference the RecordSet
Extended Properties- 8.0 is the ADO Provider Version, not the Excel Version. Also, HDR is specifying whether or not there is a Header Row in the table
DataSource- Spaces in the name of the File can cause complications, though I'll cross that bridge when I get there.

As far as the last few lines of the code (rs.Open, rs.Fields(0), etc), I'm not completely sure how it works. I just know that, somehow, it works.

 

[bradlydraper]

Part 2 of this problem is if it's possible for a User on a different computer to Open the Workbook and utilize these Macros without having Microsoft ActiveX Data Objects 2.8 Library enabled. Or if there's a way to enable it through VBA?

I will have a "Master" Shared Workbook that Users will utilize, and the ADO/SQL macros need to work with different Users on different computers.

 

[Kyle123]

Part 2. If the reference is enabled, it will be enabled on the users machine automatically. If they do not have it installed the macro will fail, it may be worth considering late binding to get around version issues of ADO.

Why are you using a workbook as a datastore? something like Access or SQLServer would be much easier and more performant.

To add to the "end", you use INSERT, you're still thinking of your datasource in terms of an Excel Workbook than a database - you don't find the last row of a database, you insert into the table (where it's inserted doesn't really matter) and it will be appended automatically.

 

[RoryA]

Extended Properties- 8.0 is the ADO Provider Version, not the Excel Version.

No, it's actually the Excel file format version (BIFF8). For newer format Excel workbooks, you should not use that.

You should also probably be using the ACE.OLEDB.12.0 provider, rather than Jet4.

There's a lot more detail here: https://www.connectionstrings.com/excel/

 

[bradlydraper]

Part 2. If the reference is enabled, it will be enabled on the users machine automatically. If they do not have it installed the macro will fail, it may be worth considering late binding to get around version issues of ADO.
Why are you using a workbook as a datastore? something like Access or SQLServer would be much easier and more performant.
To add to the "end", you use INSERT, you're still thinking of your datasource in terms of an Excel Workbook than a database - you don't find the last row of a database, you insert into the table (where it's inserted doesn't really matter) and it will be appended automatically.

I'm only able to use Excel on the system I'm working on, which is why it's my current datastore. Can you give me an example with the INSERT? With what I've been looking at, it doesn't seem to append data, it only seems to replace the existing data. Also, what is late binding?
Apologies for my lack of knowledge with this; as I had mentioned, I'm very new to learning this SQL/ADO stuff.

No, it's actually the Excel file format version (BIFF8). For newer format Excel workbooks, you should not use that.
You should also probably be using the ACE.OLEDB.12.0 provider, rather than Jet4.

Appreciate the correction/link.