-2

I have a ruby program that downloads a group of Microsoft excel cvs files off a website. I then want to tell ruby to run VBA script or program, I am not sure what the proper term is, that will combine and manipulate all the cvs files into one excel workbook.

I have looked through the ruby API, but I don't know what I should look for.

Thank you,

Kai

Rommel
  • 312
  • 2
  • 11

1 Answers1

2

Don't use vba for that. You can use vba in Ruby like the example below illustrates but there are Ruby gems that do all you want easier and better, eg take a look at the Roo gem. And since it is csv you could just join the files and save it als one big csv file. Excel will have no trouble opening the joined csv. You don't even need the csv gem (included in the core libraries) for that unless you need managing the data.

require 'win32ole'

excel = WIN32OLE.new('Excel.Application')
excel.visible = true
workbook = excel.Workbooks.Add();
worksheet = workbook.Worksheets(1);
worksheet.Range("A1:D1").value = ["North","South","East","West"];
worksheet.Range("A2:B2").value = [5.2, 10];
worksheet.Range("C2").value = 8;
worksheet.Range("D2").value = 20;

range = worksheet.Range("A1:D2");
range.select
chart = workbook.Charts.Add;

workbook.saved = true;

Here an exampel of using Roo

require 'roo'

w1 = Roo::Spreadsheet.open( "C:/Ruby193/test/roo/2.csv" )
w1.each_with_pagename do |name,sheet|
  puts name, sheet.row(1)
end
peter
  • 41,770
  • 5
  • 64
  • 108
  • for reading the CSC just use the CSV gem, use Roo to create your Excel file and like i said, if you don't need processing just join the csv's by File.read and File.Write and don't bother about converting to .XLSx since Excel is perfectly capable of opening .CSV give more explanation. If that is not what you want, i suppose your -1 indicates that – peter Jul 05 '13 at 15:42
  • by the way, Roo DOES supprt opening CSV, I have adapted my Roo example to show that – peter Jul 05 '13 at 15:47