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]

I have Access code that performs automation and Excel code that finds cells with comments and lists them. Before I can put something together I need to know
- wb path, including wb name (or you will alter that after)
- sheet name that contains the comments
- range that contains the comments
- what you want to do with the comments it finds

I might think of something else I need as I go.

 

[AlexanderBB]

New threaded comments? This is somethig in later versions. I use Office 2010 and 2019. The former is on XP which I still finds useful.
It may be very helpful - automation as I was unable to get Excel 2010 to connect and read from a accdb file.
It was ok with an mdb file so I had to use that as a sort of intermediary stage.

 

[Micron]

I was unable to get Excel 2010 to connect and read from a accdb file.

Except this thread is about getting comments from Excel sheet(s) by using Access code, yes?
You saw post 11?

 

[AlexanderBB]

Hi Micron, the wb path is "\\Dave\Uli\BBSpreadsheet2024-2.xlsm". There's 4 worksheets each with comments, "1990+" is the name of the first and presumable I could specify which when wanting a different sheet? The range is Cols J & O on every row,
If I can read the comments in Access, they're compared to data in an access table (fields called 'AComment' and 'BComment'). Where different Excel is updated.
Eventually (Access data is updated by FTP file) once all working I would aim to update Excel and Access together.

 

[AlexanderBB]

Yes, it's reading Excel in Access.

 

[Micron]

Then I guess one thing at a time. First I think if you get it to work we'll just create list via Debug.Print. If that looks good, then one of us would have to figure out what to do with the values. Putting them in a staging table might be the way to go, then you could "compared to data in an access table" whatever that means. What I mean by a staging table, some refer to as temp tables. To me, temp tables exist and then they don't until next time. A staging table is never deleted as part of a process. You usually delete its data then append for the current operation. You'd then do your compare against your existing db data and the staging table.

What sheet gets acted on should be easy enough to automate but that depends on how you want it to function. One sheet at a time? All the sheets in the wb? For the former I would suggest launching this from an Access form. Perhaps
- user clicks a button on said form. File dialog opens up, user navigates to file, selects it, clicks OK
- if they cancel the file dialog, or manage to pick a file that is not xls or xlxs or whatever you need, code exits
- code generates a list for a form listbox that shows the sheet names in the chosen file.
- user selects sheet(s) from the listbox, then you retrieve the comments from those sheets. What is done with the comment data is unclear at this point.

I'm just throwing out ideas for how I might handle this from Access. Of course you are free to handle it any way you choose.

NOTE: - that's J and O, not J to O?

 

[AlexanderBB]

It's nice to get ideas and I'm flying solo here, self taught and an amateur... and I'm not sure how much info you want. All sounds good and getting something to debug print would be quite buzzy. Basically the whole thing is in Excel. But it was easier to do stuff in Access, queries etc and the Save was immediate... where Excel takes much longer A copy of all sheets in the Workbook is an Access Table. Excel comments had to go into their own fields. 99% are in Col J and Col O, but not all. Those in other columns are in a 3rd field, along with their address. Maybe that's best ignored for now I'm assuming once Access reads the data, it can write it back as well, and the goal is to have Excel the same as Access. A group work on data updates and ftp the results to me. As I add them in, a date and id are written so only the current ones are processed and updated if different. At the moment I use this third stage (mdb that Excel can use) so removing that will be a good thing.

 

[Micron]

Here's what I have so far. Please pay close attention to the notes in the code. If you can get it to work then I suppose the next step is figure out what to do with the note text as Debug.Print is probably not useful output. Also, feeding it sheet names is likely another mod that's needed; maybe loop over sheets or something else. Perhaps you have the ability to complete those parts. Note that this would be a bit slow using automation. For those critical of looping over 2 ranges, I get that and tried to work with a non-contiguous range but abandoned that for expediency.

So far I have not been able to close Excel; my attempts make Access hang up. It may be that you will have to close Excel manually, but I have an idea about what is happening and will look into it. Lastly, this worked with my own path and sheet names but I have inserted yours in the posted code.

Public Sub getXLcomments()
Dim xlx As Object, xlwb As Object, xlsht As Object
Dim rngJ As Range, rngO As Range
Dim x As Long, Lrow As Long
Dim strFilePath As String, strCmnts As String
'******this code not tested on wb that is already open*******

On Error GoTo errHandler

'open an Excel application...
Set xlx = CreateObject("Excel.Application")
xlx.ScreenUpdating = False
xlx.DisplayAlerts = False
xlx.Visible = False
'enable next line for your tests
''strFilePath = "\\Dave\Uli\BBSpreadsheet2024-2.xlsm"

strFilePath = "\\Dave\Uli\BBSpreadsheet2024-2.xlsm"
Set xlwb = xlx.Workbooks.Open(strFilePath)

'*****if needed, put this function in a standard module*****
''    Sub pause(sngSecs As Single)
''    Dim endTime As Long
''
''    endTime = Timer
''    Do Until Timer > endTime + sngSecs
''    Loop
''
''    End Sub
'***** end of pause function *****

'test without this call first?
''pause (2) 'allow time for server to open workbook before reading/writing

'if code is in a standard module, put sheet name next.
'Otherwise, maybe use "ActiveSheet" and run this via command buttons.
Set xlsht = xlwb.Worksheets("1990+")
'choose appropriate column to determine which is last row with data or use .Find method
Lrow = xlsht.Range("A" & xlsht.Rows.Count).End(xlUp).Row
With xlsht
    'start at first row after header. In this case assume row 2
    For x = 2 To Lrow
        Set rngJ = .Range("J" & x)
        Set rngO = .Range("O" & x)
        If Not rngJ.Comment Is Nothing Then strCmnts = strCmnts & rngJ.Comment.Text & vbCrLf
        If Not rngO.Comment Is Nothing Then strCmnts = strCmnts & rngO.Comment.Text & vbCrLf
    Next
    On Error GoTo errHandler
End With

Debug.Print strCmnts

exitHere:
xlx.ScreenUpdating = True
xlx.DisplayAlerts = True
'next line of code will make opened wb visible.
'If not wanted, code to close wb with or without saving
xlx.Visible = True

'or close wb without saving. THIS PART NW YET
''xlwb.Saved = True
''xlwb.Close
''xlx.Quit
''Set xlx = Nothing
Exit Sub

errHandler:
If Err.Number = 424 Or Err.Number = 462 Then Exit Sub 'wb or Excel was closed so is no longer available
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub

 

[Micron]

This change would close wb without saving changes

'or close wb without saving
xlwb.Close False
xlx.Quit
Set xlx = Nothing

Another approach might be to move .DisplayAlerts line down to after the wb is closed but before closing Excel.

 

[AlexanderBB]

Ahh.... darn! We've been shot in the foot by Excel itself.
Access hung on line Set xlwb = xlx.Workbooks.Open(strFilePath)
I ended task on Access, and tried again with a empty Workbook. This opened and continued past that line
In case it was something in Workbook Open I remmed out everything there. No change , still hung.
Then the penny dropped, my Workbook uses active X Controls and opens with prompt
This application is about to initialize ActiveX controls that might be unsafe. If you trust the source of this file, select OK and the controls will be initialized using your current workspace settings.
Although the Trust Center offers a switch to suppress this, it doesn't work. I did ask about that here but never got a reply. Guess no-one knows or it doesn't happen to them.
So it's waiting for a click on OK , which can't be done. Or be done yet, maybe.
After which the xlsm file decides its Locked for editing, not sure how to stop that, but it doesn't matter too much as it was a copy.
Although if the real thing and it won't release that could be a problem. (Found our later an OS reboot sorts that)

The Workbook is 27MB and does take a while to Open... perhaps too long to integrate like this, and it looks like it has to be closed first.
I'm tending to think continue with an ADODB connection - with another method just for handling comments, OR find some way for Excel to connect with an accdb file and run the code in Excel, not Access.
All good challenging stuff! Not sure where to from here, but it's been exceptionally good of you to go to all he trouble so far. Much appreciated, and your part has been successful getting the comment.