using OleCreateInstance("Excel.Application")

how can I open an xls file and save it as a .txt (tab) delimited file, with no excel app showing.

(save telling the user to save as .TXT before input to OI app.)

I have worked it out this far (Copy/Paste from a word one). But the file is being saved as xls type file. I thought the wdFormatText would fix it, but, maybe I cant pass it like I have.

Subroutine test_OleCreateInstance_excel(Void)

targetfile=C:\_Daniel Brown ACCESS DB\Transfer to pms & dnc for Barry.xls"

oWrd=OleCreateInstance("Excel.Application")

OlePutProperty(oWrd,'Visible', 0)

oDocuments=oleGetProperty(oWrd, 'WorkBooks')

oDoc=OleCallMethod(oDocuments, 'OPEN', targetfile)

oWrd-]visible=0

wdFormatText=2

res=OleCallMethod(oDoc , "SAVEAS", "C:\_Daniel Brown ACCESS DB\Test.TXT",wdFormatText)

* Close the template, we only need the result

x=oDoc-]Close(0)

Quit=OleCallMethod(oWrd,'QUIT')

return

Barry

wdFormatText needs to be set to -4158 for txt or 6 for csv.

also oDoc needs to be the "ActiveWorkbook"

e.g.

ObjWorkBook=OleCallMethod( objActiveWkBook,"SaveAs", tNewfilename, -4158)

where objActiveWkBook=OleGetProperty( ObjExcel, "ActiveWorkbook" ), my ObjExcel is your oWrd.

This is fine if there is only one worksheet, for multiple sheets you will need to make the sheet you want to convert the active sheet

Barry,

Below is a sample program to return the contents of an excel workbook.

You could then OSWrite the data.

0001  Function Import_Excel_WorkBook(file, names, sheets)
0002  /*
0003  *  Given an excel workook, retrieve the names of the sheets and the contents of each sheet.
0004  *  params:
0005  *  file (in)  =full path and filename to the excel workbook
0006  *  names(out) =list of sheets in the workbook, fm delimited
0007  *  sheets(out)=contents of the sheets, tab/crlf$ format, f, delimited per sheet
0008  */
0009  Declare Function Get_Property
0010  
0011  if assigned(file) else file = ''
0012  names = ''
0013  sheets = ''
0014  err = ''
0015  
0016  is_ok = ( file # '' ) and ( dir(file)<1> gt 0 )
0017   
0018  if is_ok else
0019     err = 'Cannot find excel file ' : quote(file)   
0020  end
0021  
0022  
0023  if is_ok then
0024    objExcel = OleCreateInstance('Excel.Application')
0025    objWorkBooks = OleGetProperty(objExcel, 'WorkBooks')
0026    is_ok = ( oleStatus() eq 0 )
0027    if is_ok else 
0028       err = 'Unable to start Excel'
0029    end
0030  end  
0031  if is_ok then
0032     objWorkBook = OleCallMethod(objWorkBooks, 'Open', file)
0033       is_ok = ( oleStatus() eq 0 )
0034       if is_ok else 
0035          err = 'Unable to Open ' : quote(file)
0036    end
0037  End
0038  
0039  if is_ok then
0040     WorkSheets = OleGetProperty(objWorkBook, 'Worksheets')
0041     sheetcount = OleGetProperty(WorkSheets, 'Count')
0042     names = ''
0043     sheets = ''
0044     for i = 1 To sheetcount
0045     while is_Ok
0046        this_sheet = OleGetProperty(WorkSheets, 'Item', i)
0047        names<-1> = OleGetProperty(this_sheet, 'Name')
0048        x= OleCallMethod(this_Sheet, 'Activate')
0049        is_ok = ( oleStatus() eq 0 )
0050        if is_ok then
0051           objCells = oleGetProperty(this_Sheet, 'Cells')      
0052           x =OleCallMethod(objCells, 'Select')      
0053           x = OleCallMethod(objCells, 'Copy')
0054           is_ok = ( oleStatus() eq 0 )
0055           if is_ok else
0056              err = 'Unable to copy spreadsheet'
0057           end
0058        End
0059        
0060        if is_ok then      
0061    text = Get_Property('CLIPBOARD', 'TEXT')
0062          sheets<i> = text
0063        End
0064     next
0065  end   
0066  return is_ok   

Great information! Thank you!

Thanks, this is great, but, any chance of getting source code published in the future without line numbers, or is there an easy way of getting then stripped.

Great stuff, thanks heaps. But still having trouble.

What should my OPEN command be.

OK, sorry, worked it out.

Sorry about that. I use Sprezz Basic+ Source Code Publisher to do those listings. A checkbox on the publisher toggles linemarks on or off. I'll leave it off in the future.

You can also get the source here

Thank you.

@bobcarten

Should you worry about the clipboard being overwritten by another program in between copying the data to the clipboard and retrieving the data?

@bobcarten

Should you worry about the clipboard being overwritten by another program in between copying the data to the clipboard and retrieving the data?

Most apps don't do this (as it could be overwritten by another app) so it's probably 99% safe ;)

The Sprezzatura Group

The Sprezzatura Blog

World leaders in all things RevSoft

"Most apps don't do this (as it could be overwritten by another app) so it's probably 99% safe"

99% safe doesn't seem safe enough to me. I will try to figure out how to read read the data into OI without using the clipboard. One way would be to use the SaveAs method to save the excel file as a tab delimited file and then read the tab delimited file into OI using osread. But surely the simplest solution would be to read the data directly into a local variable. But I haven't figured out how to do this yet.

https://docs.microsoft.com/en-us/office/vba/api/excel.application(object)

Hi Josh,

the copy / paste is convenient, but you are correct that is assumes that the clipboard is not being used.

You can use the VBA range offset method to walk the cells of the spreadsheet and get the value property.

I'll look for an example.

Here is an example of walking the cells of a spreadsheet to pull in the values

SUBROUTINE EXCEL_TO_OI_EXAMPLE(void)

/*

**  An example of pulling data from excel into OI

*/





$insert msg_equates

$insert logical



$insert class_grade_equates



filename = 'D:\warehouse\SOME_DATA.xlsx'



valid_filename = ( filename != "")

If valid_filename Then

	valid_filename = ( dir(filename)<1> gt 0)

End





If valid_Filename Else

	

		Return ''

end



* Open the Excel file

rowcount = 0

hasTitles = 1

test = dir(filename)

status = ( dir(filename)<1> gt 0)

If status Then

	oXl = OleCreateInstance('Excel.Application')

	oWkbks = oXl->WorkBooks

	ThisWorkbook = oWkbks->Open(filename)

	Sheets = ThisWorkbook->Sheets

	oSheet = OleGetProperty(Sheets, 'Item', 'Sheet1')

	usedRange = oSheet->UsedRange

	usedRows =  usedRange->Rows

	rowcount = usedRows->Count + usedRows->Row - 1

	oXl->Visible = -1

	

	If rowcount Else

		status = false$

		Msg(@window, 'No Excel File Chosen')

		Return ''

	End

End



Open 'CLASS_GRADE' to f_class else 

   msg(@window, 'Unable to open CLASS')

   return ""

end



* Convert the rows into my file

* Could read the top row, use the headings as dict ite,

oCells = usedRange->Cells



* We want

* rows 5 - end 

* columns 1-5

firstRow = 5

lastCol = 5



for xlRow = firstrow to rowcount

    xl_rowdata = ""

	for xlCol = 1 to lastCol

		this_Cell =  OleGetPRoperty(oCells, 'Item', xlRow, xlCol)

		this_Val = this_Cell->value

		xl_rowData<xlcol> = trim(this_Val)

	next

   

	testid = xl_rowData<1>

	studentId = xl_rowData<2>

    score = xl_rowData<3>

    classID = xl_rowData<4>

    SectionId = xl_rowData<5>

	

	

	* Valid student? Check the roster

	roster_id = class_Id:"*":section_id

	roster = Xlate("CLASS_ROSTER", roster_id, "CLASS_ROSTER_STUDENTIDS", 'X' )

	is_enrolled = InlisT(roster, studentId, @vm)

	if is_enrolled else

	   msg(@window, 'Hey! This guyis not a on the ':quote(roster_id:'roster!' : quote(studentId) )

	end

	

	

	* Get the record of multivalued grades for this class for this section for this student

	class_grade_id = class_Id:"*":section_id:"*":studentId

	read rec from f_class_grade else 

	    rec = ""

	end

	

	* Next position for the grade?

	next_grade_nr = fieldcount(rec<class_grade_testid$>, @vm) + 1

	

	* Add the multivalues

	rec<class_grade_testid$,next_grade_nr> = testId

	rec<class_grade_score$,next_grade_nr> = score

	rec<class_grade_date$, next_grade_nr> = date()

	

	* Update

	write rec on f_class_grade, class_grade_Id else

	    * Do something

	end

next



Return ""

Thank you. I will try to incorporate this into our excel import function. We have been using the copy method for about a year. I hope it hasn't caused any issues (it's unlikely to have given that we validate the data after importing it).

Hi Josh,

For completeness, here is an export to excel example.

It will generate spreadsheets from rlist statements.

Subroutine rlist_to_excel(cmd, method)

/*

**  Sample program to demonstrate exporting to excel using rlist

**

**  parameters:

**  Cmd     (required)= an rlist statement, e.g. LIST PERSON WITH CITY EQ "BOSTON" LNAME FNAME ZIP

**  method ( optional ) = "CELL" if you want to test the slower cell-by-cell way of loading data

**                        null to use the faster copy-paste mathod 

**  

**

**  Notes: 

**  I included the cell-by-cell as sample code.  You could modify it if you wamted to plug values into specific spots on an excel sheet, for example to put the values into known locations on an exsting spreadsheet.

**  copy-paste ( the default) is usually sufficient

**

**  Select-Into will accept a stack of selects with a list at the end, eg 'SELECT_

**  10-Aug-2019  rjc  Created

*/



Declare function exec_method, Get_Property

Declare Function select_Into, select_into_callback

Declare Function registry_method



$Insert logical

$Insert Rlist_Equates

$Insert msg_equates



If Assigned(cmd) Else cmd = ""

If Assigned(method) Else method = ""



* Command is required

If cmd = "" Then Return ""



*run the command

Gosub getResults



If results = "" Then

   Msg(@window, 'No Records selected')

   Return ""

end



* Display in excel using the desired method

If method = "CELL" Then

   Gosub showExcelbyLoadCells

End else

   Gosub ShowExcelbyCopyPaste

end



Return ""

//////////////////////////////////////////////////////////////

//////////////////////////////////////////////////////////////





GetResults:



	* Run the report

	call Select_Into(cmd, 'EDT')



     * Get the results

	 results = ""

	 call select_into_callback(CALLCODE_INFO$, 'GET_RESULT',results)



return







ShowExcelbyCopyPaste:

/*

* Quick and simple - put all of the data ino a blank sheet

* Paste results into a blank Excel Sheet

*

*/





*

* Copy it to Clipboard

* Use tabs between columns, crlf$ between rows

*

swap @fm with char(9) in results

swap @rm with char(13):char(10) in results



call Set_Property('CLIPBOARD', 'TEXT', results)



*

* Paste it into a blank Excel Sheet

*

oXl = OleCreateInstance('excel.application')

oWkBks = OXl->Workbooks

oWkBk = oWkbks->Add()

oXl->Visible = 1

x= oWkBk->Activate()

oActiveSheet = oWkBk->ActiveSheet

x =OleCallMethod(oActiveSheet, 'Paste')



call Set_Property('CLIPBOARD', 'TEXT', '')

     

Return

//////////////////////////////////////////////////////////////





showExcelbyLoadCells:

/*

**  Slower but your could manipulate this to put th data into specific cells on the spreadsheet

*/

     

      * results looks like colheadings : @rm : data ; so your could set the ' COLLABEL' property of an edit table

	 * colheadinsg are @fm dellimited, data is @rm by row, @rm by col

     colheadings = results[1,@rm]

     list = results[col2()+1, Len(results)]



     Convert @rm:@fm:@vm To @fm:@vm:@svm In list



     *

     * load it into a blank Excel Sheet

     *

     // Create an instance of Excel and add a workbook

     xlApp = OleCreateInstance("Excel.Application")

     xlwbs = xlApp->Workbooks

     xlWb = xlwbs->Add()

     xlSheets = xlWb->Worksheets

     xlWs = OleGetProperty(xlSheets, 'Item',  "Sheet1")

     oCells = xlws->Cells



     // Display Excel and give user control of Excel's lifetime

     xlApp->Visible = True$

     xlApp->UserControl = True$



     //Copy field names to the first row of the worksheet

     fldCount = Fieldcount(colheadings, @fm)



     For iCol = 1 To fldCount

          oCell = OleGetProperty(oCells, 'Item', 1, iCol)

          oCEll->Value = colheadings<iCol>

     Next



     // Copy the recordset to the worksheet, starting in cell A2

     rp   = 1

     rnum = 1

     Loop

          rowdata = list[rp,@fm];rp=col2()+1

          If rowdata != "" Then

               rnum += 1

               cp = 1

               For iCol = 1 To fldCount



                    val = rowdata[cp,@vm]; cp = col2()+1



                    // wrap the multivalues

                    Convert @svm To char(10) In val



                    oCell = OleGetProperty(oCells, 'Item', rnum, iCol)

                    oCEll->Value = val

                    

               Next

          End

     While rp lt len(list)

     repeat



Return

Thanks, our users asked for the ability to export to excel, so this will be useful.

View this thread on the Works forum...