Excel Range into Array or File

Started by mark_in_atx, January 01, 2018, 01:58:49 PM

Previous topic - Next topic

mark_in_atx

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

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

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

These two lines are just wasting CPU cycles:

Code (winbatch) Select
OutputArray = ArrDimension(11, 4)
OutputArray = OutputValues


You can just use the OutputValues variable directly in your call to ArrayFilePutCSV.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade