Author Topic: Excel Range into Array or File  (Read 57 times)

mark_in_atx

  • Newbie
  • *
  • Posts: 10
Excel Range into Array or File
« on: January 01, 2018, 01:58:49 pm »
I need to place a range from Excel (using OLE) into either a Winbatch Array or write it to a text file. 

2 conditions:
No loops
No use of clipboard

I have spent a couple hours and cannot figure out how to do this without using OLE .Copy -> Clipget() -> FilePut.  Of course this uses the clipboard which I need to avoid.

Any ideas?  Thanks and happy new year!



stanl

  • Pundit
  • *****
  • Posts: 683
Re: Excel Range into Array or File
« Reply #1 on: January 02, 2018, 03:49:32 am »
To avoid the clipboard, I would normally suggest a For...Next operation for the cells in the range to be written to a text file. But you also said no loops. That leaves something like Powershell and the 'Export-csv' cmdlet which could be called from WB [2 lines of code for the export].  Then there is the old-school method of using WB to write code into the VBA Object in Excel to export the range as a macro. Either alternative is pretty slick code but begs the question of why 'no loops' as a condition.

mark_in_atx

  • Newbie
  • *
  • Posts: 10
Re: Excel Range into Array or File
« Reply #2 on: January 02, 2018, 05:24:08 am »
Figured it out:

OutputValues = oApp.WorksheetFunction.Transpose(objWB.Worksheets('Worksheet 1').Range('P10:S20').Value)
OutputArray = ArrDimension(11, 4)
OutputArray = OutputValues
ArrayFilePutCSV('C:\Test.csv', OutputArray, ',', @FALSE, 2)


td

  • Tech Support
  • *****
  • Posts: 2282
    • WinBatch
Re: Excel Range into Array or File
« Reply #3 on: January 02, 2018, 06:31:25 am »
These two lines are just wasting CPU cycles:

Code: Winbatch
OutputArray = ArrDimension(11, 4)
OutputArray = OutputValues

You can just use the OutputValues variable directly in your call to ArrayFilePutCSV.
"Success is a lousy teacher. It seduces smart people into thinking they can't lose."
  - Bill Gates