0

I am sending a request two times using LOOP, and in output I am getting a unique value. I want to store these unique values in an excel sheet column so that further I can parameterize all the values.

I have successfully got the values to a TEXT file. But what I need is to store them in separate rows (in a column) in an EXCEL file, which I am not able to do.

Kritika Sharma
  • 11
  • 3
  • You need to use Custom Code feature. You will pass your output value as input parameter for Custom Code and then write a code to write these value in Excel. – ManishChristian Nov 24 '15 at 13:54

1 Answers1

0

Directly, I don't know. But you can always cheat. For example, EXCEL has a secondary format called .CSV, which is plain test...with ";" as separators.

So write your "output.csv" file with the following kind of structure :

Name;Income;Status
Martin;25000;OK
Johnson;32500;KO

And it should be plain openable in EXCEL. As a CSV. Once in EXCEL, save it back as a XLS or XLSX, and you're done, you've got a EXCEL file perfectly usable.

If you need some kind of automation for making this .csv into a .xlsx, here is a sample macro I made quickly & dirty, but works with EXCEL 2013 :

Sub ConvertFile()
    On Error Resume Next
    Kill "C:\MyPath\output.xlsx"
    On Error GoTo 0
    Workbooks.Open Filename:= _
        "C:\MyPath\output.csv"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\MyPath\output.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
End Sub

Beware : this is a EXCEL macro, not vbscript.

gazzz0x2z
  • 326
  • 2
  • 12
  • Thanks... but I am facing one more issue that the data is not getting populated in excel file, whether it's in .csv format or .xslx format. This could be due encoding issue or something that I am not able to figure out... – Kritika Sharma Dec 09 '15 at 10:46
  • What did you do exactly? Do you generate a .csv? When you open is with a text editor, is it full? If yes, is it formatted like a real .csv? If yes, is the issue when you try to autoconvert it to Excel? (or did I completely misunderstand you?) – gazzz0x2z Dec 09 '15 at 12:45