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!
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.
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)
These two lines are just wasting CPU cycles:
OutputArray = ArrDimension(11, 4)
OutputArray = OutputValues
You can just use the OutputValues variable directly in your call to ArrayFilePutCSV.