Need to write values in Excel from an array using vbscript

Question

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

Ekkehard.Horner · Accepted Answer · 2013-10-07 19:59:58Z

2

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.

answered Oct 7, 2013 at 19:59

Ekkehard.Horner

38.8k2 gold badges50 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Iftakhar Ahmad Over a year ago

thanks a lot for your response, now it is working now, only problem is, it is creating a file named- "Resume.XLW" in My Documents, this .XLW file is the copy of my output file, and when I am running the script next it is saying- "File Resume.XLW" already exists in this location in an alert box. However I can disable the alert still I am worrying whether there is any harm because of this because I have to run this script on my production server. Also I need to separate the resulted strings by comma.

Collectives™ on Stack Overflow

Need to write values in Excel from an array using vbscript

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related