XLSheetToFile

Ideas for new features & functions

Moderators: Dorian (MJT support), JRL

Post Reply
User avatar
Bob Hansen
Automation Wizard
Posts: 2475
Joined: Tue Sep 24, 2002 3:47 am
Location: Salem, New Hampshire, US
Contact:

XLSheetToFile

Post by Bob Hansen » Wed Oct 10, 2012 1:47 am

How about sending spreadsheet contents to a text file?

I recently had to create a file with the contents of two columns from a spreadsheet. When I could not find an XL function to do that, I ended up doing it the hard way, but it made me think how convenient a function would be.

Here is my code:

Code: Select all

XLSheetToArray>%SpreadsheetFile%,Sheet1,vFieldName
Let>vRow=1
Repeat>vRow
    Let>vRow=vRow+1
    // Set values for columns 1,4
    Let>vColumn1=vFieldName_%vRow%_1
    Let>vColumn4=vFieldName_%vRow%_4
    WriteLn>Path\OutputFile.txt,vResult,%vColumn1%;;;%vColumn4%
Until>vRow=%vFieldName_count%
So how about a function like this:
XLSheetToFile>ExcelFile,Sheet,{Rows/Columns},Delim,OutputFile,Result,

Syntax:
Excel file = Path\FileName of source file
Sheet = SheetName or Number
Rows/Columns ="All","R/C #-# or #,#,#,#" or "RangeName" to be exported
Delim = Column delimiter ( comma, space, semicolon, quotes, tab, other)
OutputFile =Path\FileName of destination file.
Result = 1 for successful export, 0 for failure.

This would be the command for the code sample I showed above:

Code: Select all

XLSheetToFile>%SpreadsheetFile%,Sheet2,{C1,4},;,%OutputFile%,vResult
This would create a text file for every row, showing col1; ; ; col4 values
Hope this was helpful..................good luck,
Bob
A humble man and PROUD of it!

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts