CODEWITHSUNDEEP
vbscript
Iftakhar Ahmad
Iftakhar Ahmad

Reputation: 1

Need to write values in Excel from an array using vbscript

I need to store values from excel into array using vbscript, I then need to write distinct values from this array to some other excel. From the below scipt I am able to write excel values into array and display it in message box, however I need to write it in another excel. I am getting the error- "Type Mismatch 'Join'" at line 31. Could someone please look into it and assist, Thanks in Advance.

  Dim MyArray()
  Dim UniqValues

  Set objExcel = CreateObject("Excel.Application")
  Set objWorkbook = objExcel.Workbooks.Open("D:\Read Excel.xls")


  objExcel.Visible = True
  objExcel.displayalerts = false

  i = 1
  x = 0

  Do Until objExcel.Cells(i, 1).Value = ""
  ReDim Preserve MyArray(x)
  MyArray(x) = objExcel.Cells(i, 1).Value


  i = i + 1
  x = x + 1 

  Loop

  Set objExcel2 = CreateObject("Excel.Application")
  strPathExcel = "D:\file1.xls"
  objExcel2.Workbooks.open strPathExcel 
  Set oSheet = objExcel2.ActiveWorkbook.Worksheets(1)

  oSheet.Cells(1,1).Value = Join(UniqValues)

  'WScript.Echo Join(MyArray) 
  UniqValues = uniqFE(MyArray)
  'WScript.Echo Join(UniqValues)

  Function uniqFE(fex)
    Dim dicTemp : Set dicTemp = CreateObject("Scripting.Dictionary")
    Dim xItem
    For Each xItem In fex
    dicTemp(xItem) = 0
   Next
    uniqFE = dicTemp.Keys()
   End Function

  objExcel.Save
  objExcel.Quit

Upvotes: 0

Views: 8915

Answers (1)

Ekkehard.Horner
Ekkehard.Horner

Reputation: 38775

Your UniqValues is not initialized wheb you try to Join it:

>> Dim UniqValues
>> X = Join(UniqValues)
>>
Error Number:       13
Error Description:  Type mismatch

Call uniqFE() before you assign/display it.

Upvotes: 2

Related Questions