r/vba • … • u/Jemjar_X3AP

[WORD] Collection as pointer to Application.Documents

I am trying to create a generic macro to allow me to select from amongst the open files in each of the various Office apps.

I have this working for Word and PowerPoint and I'm sure I'll have no real issue doing it with Excel and so on, but I am trying to make my code as efficient as possible and I'm coming a bit unstuck with the fact that I need to access wordApp.*Documents* for Word, pptApp.*Presentations* for PowerPoint, excelApp.*Workbooks* for Excel and so on.

Ideally I'd do this once (within a necessary Case statement, along with some other variable assignment) and then do all the actual processing outside the Case statement but I'm getting stuck here.

If I create a Collection object, I don't seem able to assign it to wordApp.Documents, I just get a type mismatch error.

I don't really want the hassle of cloning all the document objects at all (if I even can?) but I'd quite like to just create a simple pointer to let me use one variable name to point to each apps individual list of open files.

Any suggestions?

Function F_SelectOpenFile(filetype As String, mbtitle As String, mbtext As String) 'filetype input as String 'For MS Word: "word" 'For MS Excel: "excel" 'For MS Powerpoint: "powerpoint"

'mbtitle input as String 'Used as title for selection prompt messagebox

'mbtext input as String 'Used as text for selection prompt messagebox

Dim officeApp As Object Dim officeAppName As String Dim officeAppFileName As String Dim optional_n As String Dim openFile As Object Dim openFileList As String Dim n As Integer Dim i As Integer Dim fileArray() As Variant Dim message_text As String Dim message_title As String Dim fileSelection As String

Dim fileColl As Collection

Select Case filetype

Case "word" '######################################################################### 'Setting key parameters - Program specific officeAppName = "Word" officeAppFileName = "document" optional_n = "" '#########################################################################

'Checking at least one file of type is open - Generic for all file types On Error Resume Next Set officeApp = GetObject(, officeAppName & ".Application")

If Err.Number <> 0 Then MsgBox (officeAppName & " is not running! Open a" & optional_n & " " & officeAppName & " " & officeAppFileName & " to continue.") Err.Clear Exit Function End If

On Error GoTo 0

'######################################################################### 'Creating file list - Program specific Debug.Print "=================================================" Debug.Print "VarType(officeApp.Documents) = " & VarType(officeApp.Documents) Debug.Print "Number of items in officeApp.Documents = " & officeApp.Documents.Count Debug.Print "ObjPtr(officeApp.Documents) = " & ObjPtr(officeApp.Documents)

'Code throws error on .Count line if I don't do this - aware that it's a redundant step though Set fileColl = New Collection

Debug.Print "-------------------------------------------------" Debug.Print "VarType(fileColl) = " & VarType(fileColl) Debug.Print "Number of items in fileColl = " & fileColl.Count Debug.Print "ObjPtr(fileColl) = " & ObjPtr(fileColl)

'Code throws type mismatch error on next line Set fileColl = officeApp.Documents

'... '... '... '... '...

End Function

— 0

RedditPP

[WORD] Collection as pointer to Application.Documents