View Single Post
Old 09-14-2020, 03:56 PM
Guessed's Avatar
Guessed Guessed is offline Windows 10 Office 2016
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 1,864
Guessed is a name known to allGuessed is a name known to allGuessed is a name known to allGuessed is a name known to allGuessed is a name known to allGuessed is a name known to all

Yes, the function can be called from any Sub or even another Function. It doesn't have to be an automacro.

Your other macro is getting the data from Excel as an ADODB Recordset and then converting this into an array and then finally into a string by concatenating all the array position contents into a string. So essentially you have the same content in three very different formats.

The point of going all the way to a string is so you can save that in a document metadata store so it can be retrieved any time WITHOUT needing to get it fresh from Excel again. The problem with this format is that you will need to split the contents of that string to firstly break it into rows and then split each row into columns in order to get back to the contents of any single Excel cell.

However, if you want to make use of the data WHILE the macro is still running, you have the choice of using it in either of the earlier formats where it is still broken up into the individual cells of data.

Say you wanted to use the Recordset (recSet) to grab all the data from one field. After the recSet has been populated by the first line, you can loop through its records to grab a field
Set recSet = connection.Execute(strQuery2, , adCmdText)
With recSet
  Do While Not .EOF
    Debug.print recSet!person_number
End With
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote