Populating a Param Array 1

[hilbertl3]

Is it possible to populate a param array by looping through a table? I am calling function that uses a paramarray as a parameter, but it doesn't work if I try to pass an array to the paramarray parameter, only when I hard code in values.
eg Paramarray("value1","value2","value3").

hilbertl







'VERSION 1.0 CLASS
'BEGIN
' MultiUse = -1 'True
' Persistable = 0 'NotPersistable
' DataBindingBehavior = 0 'vbNone
' DataSourceBehavior = 0 'vbNone
' MTSTransactionMode = 0 'NotAnMTSObject
'End
'Attribute VB_Name = "CombinePDFs"
'Attribute VB_GlobalNameSpace = False
'Attribute VB_Creatable = True
'Attribute VB_PredeclaredId = False
'Attribute VB_Exposed = False
'Attribute VB_Ext_KEY = "SavedWithClassBuilder6" ,"Yes"
'Attribute VB_Ext_KEY = "Top_Level" ,"Yes"
Option Explicit

Private msProblem As String

'Public Event Status(ByVal sMessage As String)

Public Property Get Problem() As String
Problem = msProblem
End Property

Public Function Combine(ByVal sOutputFile As String, ParamArray SourceFiles()) As Boolean
' files will be combined in the order they were added to SourceFiles()
Dim i As Long
Dim nLB As Long
Dim nUB As Long

Dim nRet As VbMsgBoxResult

Dim oOutputPDF As Acrobat.CAcroPDDoc
Dim oInputPDF As Acrobat.CAcroPDDoc

Dim nPagesTo As Long
Dim nPagesFrom As Long

On Error GoTo ERRHANDLER

Combine = False


nLB = LBound(SourceFiles)
nUB = UBound(SourceFiles)

If nUB < nLB Then
Err.Raise vbObjectError + 6001, "Combine", "No files to combine"
Else
Set oOutputPDF = CreateObject("AcroExch.PDDoc")
' RaiseEvent Status("Opening " & SourceFiles(nLB))
If Not oOutputPDF.Open(SourceFiles(nLB)) Then
Err.Raise vbObjectError + 6002, "Combine", "Unable to open " & SourceFiles(nLB)
End If
nPagesTo = oOutputPDF.GetNumPages

For i = (nLB + 1) To nUB
Set oInputPDF = CreateObject("AcroExch.PDDoc")
' RaiseEvent Status("Opening " & SourceFiles(i))
If Not oInputPDF.Open(SourceFiles(i)) Then
Err.Raise vbObjectError + 6003, "Combine", "Unable to open " & SourceFiles(i)
End If
nPagesFrom = oInputPDF.GetNumPages
' RaiseEvent Status("Inserting " & nPagesFrom & " pages from " & SourceFiles(i))
If Not oOutputPDF.InsertPages(nPagesTo - 1, oInputPDF, 0, nPagesFrom, True) Then
Err.Raise vbObjectError + 6004, "Combine", "Unable to add " & SourceFiles(i)
End If
nPagesTo = oOutputPDF.GetNumPages
Set oInputPDF = Nothing
Next i

'RaiseEvent Status("Saving " & sOutputFile)
If Not oOutputPDF.Save(PDSaveFull, sOutputFile) Then
Err.Raise vbObjectError + 6005, "Combine", "Unable to save " & sOutputFile
End If

' RaiseEvent Status("Closing " & sOutputFile)
Call oOutputPDF.Close
Set oOutputPDF = Nothing

Combine = True
End If

Exit Function

ERRHANDLER:
' msProblem = Err.Description
' nRet = MsgBox("Error " & Err.Number & ": " & Err.Description, _
'' vbQuestion + vbRetryCancel, App.Title & " - " & Err.Source)
'If nRet = vbRetry Then Resume

'Combine = False
'Set oOutputPDF = Nothing
'Set oInputPDF = Nothing
End Function

 

[BobRodes]

I think this is what you need. This is from VB Doc on arrays:

Arrays that Contain Other Arrays
It's possible to create a Variant array, and populate it with other arrays of different data types. The following code creates two arrays, one containing integers and the other strings. It then declares a third Variant array and populates it with the integer and string arrays.

Private Sub Command1_Click()
Dim intX As Integer ' Declare counter variable.
' Declare and populate an integer array.
Dim countersA(5) As Integer
For intX = 0 To 4
countersA(intX) = 5
Next intX
' Declare and populate a string array.
Dim countersB(5) As String
For intX = 0 To 4
countersB(intX) = "hello"
Next intX
Dim arrX(2) As Variant ' Declare a new two-member
' array.
arrX(1) = countersA() ' Populate the array with
' other arrays.
arrX(2) = countersB()
MsgBox arrX(1)(2) ' Display a member of each
' array.
MsgBox arrX(2)(3)
End Sub

Here's a piece of code that I lifted from the internet:

Option Explicit
Option Base 1

'' ***************************************************************************
'' Purpose  : Create some test data
'' Written  : 01-Jul-2002 by Andy Wiggins, Byg Software Limited
''
Sub PassingAParamArray()
Dim x As Integer, y As String, z As String
Dim aa(3)
Dim bb(2)
Dim cc(4)

    '' Assign values to integer and string types
    x = 1
    y = "Two"
    z = "3"

    '' Assign values to an array
    aa(1) = "aa array"
    aa(2) = "Two"
    aa(3) = "Three"

    '' Assign values to another array
    bb(1) = "bb array"
    bb(2) = "Five"

    '' Assign array "bb" to the second element of "cc" to create a nested array
    cc(1) = "Nested array"
    cc(2) = bb

    '' Call this Sub to output test data to the immediate window
    PassToHere x, y, z, aa, bb, cc

End Sub

'' ***************************************************************************
'' Purpose  : Output test data to the immediate window (Ctrl+G)
'' Written  : 01-Jul-2002 by Andy Wiggins, Byg Software Limited
''
Sub PassToHere(x As Integer, y As String, z As String, ParamArray pa() As Variant)

    '' Regular parameters
    Debug.Print x
    Debug.Print y
    Debug.Print z

    '' The array is passed as the final elements in the Sub parameter.
    '' This Sub has four arguments, but in "PassingAParamArray" it shows six.
    '' The fourth, fifth and sixth are treated as one by this Sub.
    '' To address it we need:
    ''      First:  Address the array element
    ''      Second: Address the array element within the first element
    Debug.Print pa(0)(1)
    Debug.Print pa(0)(2)
    Debug.Print pa(0)(3)

    Debug.Print pa(1)(1)
    Debug.Print pa(1)(2)

    Debug.Print pa(2)(1)
    Debug.Print pa(2)(2)(1) '' Address an element within a nested array

    '' From the Excel Help file
    '' Used only as the last argument in arglist to indicate that the
    '' final argument is an Optional array of Variant elements.
    '' The ParamArray keyword allows you to provide an arbitrary number
    '' of arguments.
    '' The ParamArray keyword can't be used with ByVal, ByRef, or Optional.
End Sub

I'm thinking that you wanted the ubound of the array within the Sourcefiles array, rather than the ubound of the sourcefiles array.

HTH

Bob