Access reading Excel Comment

[AlexanderBB]

I have a routine in Access that uses ADODB to connect and read Excel cells.
But I cannot see how to read a comment (Note). Is it possible?

 

[Micron]

Should be possible with automation. You'd create an Excel reference then an Excel object. Then you could do pretty much anything that you'd be able to do when in Excel.

Lots of code here using automation. You'd focus on the principles involved and maybe not so much on the examples themselves.

EXCEL Import And Export

 

[AlexanderBB]

By Automation do you mean TransferSpreadsheet method? I'm not sure what you man by create an Excel reference then an Excel object.
I couldn't see that in the link... did I miss it?
I did import a worksheet into Access but it doesn't include comments.

 

[Micron]

Automation is a thing between Office apps where you use one app to control the other. I suspect that when you are in the vbe (vb editor) you know about Tools>Relationships because of your post count, but I could be wrong. In your Access project, you would add the Excel library as a reference. Doing so would give you access (not Access) to the Excel object model. Then by writing code in Access you would then be able to push/pull data to/from Excel while in Access, alter sheet data, save/save as a wb - just about anything you could do if you were in Excel itself. The site I linked to is jam-packed with info that would guide you to achieve the goal, I think, but it may be buried in there. One example that seems pertinent to my suggestion (note that it uses late binding):
Write Data From a Recordset into an EXCEL Worksheet using Automation (VBA)

I snipped some code parts from a db that pushed Access query data to a dynamic named range in Excel. The range was used as chart data. The code also checked out the wb for editing (because the wb was on a Sharepoint server) and checked it back in when done. This is just to illustrate the use of automation, not necessarily that you could use any or all of it.

Dim lngColumn As Long, result As Integer
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim db As DAO.Database, rst As DAO.Recordset
Dim svChartPath As String, msg As String
Dim blnCanCheckout As Boolean, blnWeCheckedOut As Boolean

'some code before here, then...
'open an Excel application...
Set xlx = CreateObject("Excel.Application")
xlx.ScreenUpdating = False
xlx.DisplayAlerts = False
xlx.Visible = False
'... now test to see if we can check out the workbook (exlusive use)
blnCanCheckout = xlx.Workbooks.CanCheckOut(svChartPath)

If blnCanCheckout Then
    Set xlw = xlx.Workbooks.Open(svChartPath)
    xlx.Workbooks.CheckOut svChartPath
    'if previous successful, we should store this in case of interruption
    Pause (2) 'allow time for server to open workbook before writing data
    Set xls = xlw.Worksheets("DATA")
    'delete data in the ranges in case fewer records are being entered
    With xls
        .Range("DailyCountRange").ClearContents
        .Range("DateRange").ClearContents
    End With
    Set xlc = xls.Range("A6") ' this is the first cell into which data goes
    rst.MoveFirst
    For lngColumn = 0 To rst.Fields.count - 1
        'enable next to write data column headers
        ''xlc.Offset(0, lngColumn).value = rst.Fields(lngColumn).Name
    Next lngColumn
    ''Set xlc = xlc.Offset(1, 0)'offset IF the headers were written
    Set xlc = xlc.Offset(0, 0) 'otherwise, no offset
    ' write data to worksheet
    Do While rst.EOF = False
        For lngColumn = 0 To rst.Fields.count - 1
            xlc.Offset(0, lngColumn).value = rst.Fields(lngColumn).value
        Next lngColumn
            rst.MoveNext
        Set xlc = xlc.Offset(1, 0)
    Loop
MsgBox "Data push was successful!"
    With Forms!frmWait
        .lblMsg.Caption = "Workbook will now be saved, checked in and closed."
        .Repaint
    End With
    Pause 3
    xlw.Save 'save the changes
    xlw.CheckIn svChartPath 'check in the workbook
    blnWeCheckedOut = False 'record that the workbook is no longer checked out
End If
'more code 'til the end

 

[AlexanderBB]

I did try stepping through that to see what would happen. If I could set a Range then I might succeed reading Excel Data in Access.
I had a reference to Microsoft Excel 16.0 Object Library
First problem was "Pause" which it didn't like... then I didn't know what svChartPath should be so made that the fileSpec of my xlsm file. "D:\temp\test.xlsm"
blnCanCheckout came back False, so not knowing what Workbooks.CanCheckOut did I remmed it out.
Access never recovered/returned from Set xlx.Workbooks.Open("D:\temp\test.xlsm") so more to it, will look into that link. Could be promising. Thanks.

 

[Micron]

That was for reading and guidance, not for use. "Pause" is a udf (user defined function) that I wrote to inject a pause where a 2nd and dependent action might occur before the first has had time to complete.

I think I have assumed too much wrt your vba knowledge. If so I apologize. I was not trying to show off or bamboozle you.

I will try to review all of this tomorrow to see if I can make better sense of what I'm saying.

 

[AlexanderBB]

I'm all for trying something out. Didn't matter whether it worked or not at this stage. That is can work is worth persevering.
I know there's a lot I don't know... LOL. My msgs count is me asking too many questions. I may know how to do something but I like
seeing is there's better methods. This automation code is something I vaguely recall from a while back, but details lost.

 

[Micron]

So if I tomorrow I can read an Excel cell comment from an Access db would that code help? I rate my Excel knowledge below that of Access and IIRC, there are 2 types of cell notes. One is no longer used? Which one are we talking about here?

 

[AlexanderBB]

Yes, thank you, that would help. It's the pop-comment that shows when you hover over a cell. I think the names changed to Notes.
It's only one I know of, so hope it's not no longer used. I use it a lot.

 

[RoryA]

They are still available, they've just been somewhat superseded by the new threaded comments.

You can't read comments via ADO, so opening the workbook via automation is going to be your best bet.