View Single Post
 
Old 09-14-2020, 03:56 PM
Guessed's Avatar
Guessed Guessed is offline Windows 10 Office 2016
Expert
 
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
Default

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
Code:
Set recSet = connection.Execute(strQuery2, , adCmdText)
With recSet
  .MoveFirst
  Do While Not .EOF
    Debug.print recSet!person_number
    .MoveNext
  Loop
End With
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote