Hello. I have done very well in programming with Office Automation. I think these classes are very well done. Documentation is easy to find online and convert.
I am having an issue with what looks like, to me, very simple code. I receive a familiar compile error that is not clear enough to know how to remedy the issue. Here is a snippet of the code to give context:
[code] Dim excel As New ExcelApplication
Dim exbookWO As ExcelWorkbook
Dim firstSheetName As String
Dim firstSheet, thisSheet As ExcelWorksheet
Dim sheetTot, cnt As Integer
Dim SheetNames(-1) As String
Dim thisParam As New OLEParameter
exbookWO = excel.Workbooks.Add(thisName)
sheetTot = exbookWO.Sheets.Count
for cnt = 1 to sheetTot
thisSheet = exbookWO.Sheets(cnt)
if thisSheet.Name <> "Master" then
SheetNames.Append thisSheet.Name
if cnt = 1 then firstSheetName = thisSheet.Name
end if
next
if UBound(SheetNames) > -1 then
thisParam.Type = OLEParameter.ParamTypeString
thisParam.ValueArray = SheetNames
exbookWO.Sheets(thisParam).Select_
firstSheet = exbookWO.Sheets(firstSheetName)[/code]
The compile error is on the line: thisParam.ValueArray = SheetNames.
The error is: There is more than one item with this name and it’s not clear to which it refers.
The use of thisParam in this method (the project for the matter) is limited to exactly the code that you see here. Additionally, the line above it, thisParam.Type = OleParameter.ParamTypeString
, seems to be accepted by the compiler.
To study this issue a bit more, I replaced the thisParam.ValueArray = SheetNames
with thisParam.Value = SheetNames
and it does compile then. I doubt, though, that it would accept an array with the Value parameter.
Does anyone have any suggestions?
Thanks,
Rocky Scofield