> An example of using the OLEParameter class would be helpful
>
> Do you have an example somewhere you could share??
Here is a method I use for sending an RB listbox full of db records to Excel
where rngVals is a 2D array prepared elsewhere from the contents of the
listbox, and spdSheet is the listbox with the records in RB:
Dim excel as New ExcelApplication
Dim book as ExcelWorkbook
Dim sheet as ExcelWorksheet
Dim r As ExcelRange
Dim cols, rows as integer
Dim param as new OLEParameter
excel.ScreenUpdating = false
excel.Visible = false
book = excel.Workbooks.Add
excel.ActiveSheet.Name = "ProjectRulesReport"
sheet = book.ActiveSheet
cols = spdSheet.ColumnCount
rows = spdSheet.listcount
' transfer it into an OLEParam
param.Type = OLEParameter.ParamTypeString
param.ValueArray = rngVals
' transfer it into the spreadsheet
r = excel.Range(sheet.Cells(1,1) , sheet.Cells( rows , cols))
r.Value2 = param
'do some formating in excel
excel.ActiveWindow.View = 1
sheet.Rows("1:1").Select_
excel.Selection.Font.FontStyle = 2
excel.Selection.Font.Size = 14
sheet.UsedRange.Select_
sheet.Cells.EntireColumn.AutoFit
sheet.Rows("2:2").Select_
excel.ActiveWindow.FreezePanes = True
excel.ScreenUpdating = true
excel.Visible = true
'destroy excel refs
sheet = Nil
book = Nil
excel = Nil
(BTW this is for 5.5?+ where the param type name changed)
Here the Excel range object "r" gets its Val2 param loaded with the array of
strings in my case. Also note that lots of the code above passes straight
through as params that are their RB values straight up, but Im not sure
about optional params you'll have to try it out. In your case, have you
tried passing something like:
Excel.Range("A1").TexttoColumns(TextQualifier=Tab)
If that doesn't do it, you might have to look up the constant values of the
TextQualifiers according to VBA and nil pad the ones you don't need and then
stuff the whole mess in there.
Can you format your RB lines from your app in RB since you have them in hand
already (as above, prepare an array to suit), then pass them as rows or
columns or whatever? I suppose that's lame though when you want to affect
the excel data itself, I can see that feeling. No way but to try it out and
exercise...
So really just get the Office objects you want a handle to, and then use the
VB or VBA object browser to figure out what you can do with them. RB isn't
going to have docs on it because its part of the Borg once you use
TextToColumns on the range object. So when I do this stuff, I have Excel
and the VBA editor running so I can either inspect macros, or use the
objectBrowser to find options.
Let me/us know if the above works or not - maybe William or others can shed
some light on passing optional params to VBA that I am unaware of.
Best,
-seanA
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
|