Hello All,
I have a circumstance where I need to return data from a data source and, through a VBA form, have the end user populate (assign) an Order Number in an empty column associated with a given record.
Since Dataproviders are read only, I am trying to utilize a dpVBACube to do the following:
[list]1. Create the VBA cube at run time assigning as many columns in the DPVBAC type as in the Query Technique type plus an additional, empty column.[/list]
[list]2. Assign column names programmatically in the DPVBAC to be the same as the Query Technique. Also, assign a name from code to the empty column (Order Number).[/list]
[list]3. Construct a VBA form that enables the end user to assign an order number to the record (populate the empty column in the VBA Cube).[/list]
Right now a am stuck on step 2. The following is my code:
From my Module
Public Sub DynaCube(DpInterface As DpVBAInterface)
Dim dpVBA As New DataProvider
Dim dpUserCube As DpVBACube
Dim colsUserCube As DpVBAColumns
Set dpUserCube = DpVBAInterface.DpVBACubes.Item(1)
Set colsUserCube = dpUserCube.DpVBAColumns
colsUserCube.SetNbColumns (InputBox("No of Cols")) 'need to change this to the column count of the query technique DP plus one empty Column.
End Sub
In the after refresh event:
Dim dpvba As New DataProvider 'Dimensioning this as a
Set dpvba = Application.ActiveDocument.DataProviders. _
AddDPVBA("VBA1.rea!CreateCube.DynaCube")
Dim DpQtAJPO As DataProvider
Dim DpVBAAJPO As DpVBACube
Dim colsQT As Columns
Dim colsVBA as dpVBAColumns
Dim i As Integer
Set DpQtAJPO = Application.ActiveDocument.DataProviders("AJPO- QT")
Set DpVBAAJPO = dpvba.DpVBACubes.Item(2) 'this returns a type mismatch error because one is a VBA cube and one is a QT type dataprovider.
Set colsQT = DpQtAJPO.Columns
Set colsVBA = DpVBAAJPO.Columns
i = 0
For i = 1 To colsVBA.Count
colsVBA.Item(i).Name = colsQT.Item(i).Name
Next i
Here’s the problem:
It seems as though I can only access the the dpVBAInterface object from the module. However, when referencing it from the AfterRefresh event it is dimensioned as a dataprovider. As such, it is read only and I can’t modify it at run time.
Here’s the question:
How do I access the dpVBAInterface object from the same block of code that would enable me to populate it with data from the QT dp and then enable the end-user to modify it via a VBA form? It would seem that I need to work with an object that is dimensioned as a VBAcube and not as a dataprovider.
BOTeam (BOB member since 2003-09-22)
(BOB member since 2003-10-22)