How to save an excel file as a text in powerbuilder 6.5

Question

The question is how to open a .xls file and save it as a .txt in powerbuilder 6.5.

For example the following doesn't work and I am stuck in the opening:

oleobject testexcel
testexcel= Create oleobject
testexcel.ConnectToNewObject("excel.application")
testexcel.workbooks.Open("something.xls")
testexcel.ActiveWorkbook.saveas("something.txt")
testexcel.disconnectobject() 
Destroy testexcel

score 1 · Accepted Answer · edited May 23 '17 at 11:50

1

The OLE solution is the way to go if you need the process to be automated. If you are doing data manipulation (one time) than simply open excel and saveas (txt, csv, etc) and then do dw_1.importfile(xxxx ) to import the temporary file you created from Excel.

I suspect you are looking for the first solution because the second is more obvious-- and I think that is where Matt was going with his answer.

Found another StackOverflow question like this that was answered here: How to import Excel file into DataWindow

Here is some code, that isn't exactly what you wanted, but it could be revised to work...

string                ls_pathname, ls_filename
long                        ll_rc
oleobject        loo_excel

IF GetFileOpenName ( "Open File", ls_pathname, ls_filename, "XLS", "Excel (*.xls),*.xls" ) < 1 THEN Return

loo_excel = CREATE OLEObject
loo_excel.ConnectToNewObject( "excel.application" )
loo_excel.visible = false
loo_excel.workbooks.open( ls_pathname )
loo_excel.ActiveCell.CurrentRegion.Select()
loo_excel.Selection.Copy()
ll_rc = dw_1.ImportClipBoard ( 2 )
ClipBoard('')
loo_excel.workbooks.close()
loo_excel.disconnectobject()
DESTROY loo_excel

The clipboard seems kind of round-about but it was all I could find on short-time. Good luck

edited May 23 '17 at 11:50

Community

1
1

answered Jul 11 '14 at 20:34

Rich Bianco

4,141
3
29
48

I have seen the above example. This is what I'm trying to do with my code. However, the open function in `loo_excel.workbooks.open( ls_pathname )` does not seem to exist in powerbuilder 6. – Alex Jul 12 '14 at 07:51
Alex, you said the function doesn't exist in PB6 but that is incorrect, when you use OLE objects it is like using "object" where the compiler will let you type anything you want, expecting the function will be available at run time. So, either you are using the wrong syntax for the version of Excel, or that function doesn't exist in *EXCEL* as anything to the right of the oleobject is specific to the OLE Object and not PB. Did that make sense? So.. look into Microsoft Docs for the exact version of excel you have on the offending machine that fails. Did the connecttonewobject have errors? – Rich Bianco Jul 13 '14 at 20:36
Alex, also get the return value from connecttoobject and add this after it.. if li_rtn <> 0 then ls_errorMessage = "Error running MS Excel api" li_rtn = -1 else – Rich Bianco Jul 13 '14 at 20:37
1

Thanks. It seems that the open function with wrong file name gives a not well described error: `error calling external object function open`. Otherwise it works. – Alex Jul 14 '14 at 08:32
1

Using OLE in PowerBuilder is VERY unforgiving. You really need to check return codes and better yet use try catch blocks to prevent application crashes when your OLE code is wrong. Glad you got it going! – Rich Bianco Jul 15 '14 at 17:17

score 0 · Answer 2 · answered Jul 11 '14 at 15:04

0

You can save it as a csv file which is basically the same as text.

answered Jul 11 '14 at 15:04

Matt Balent

2,337
2
20
23

How to save an excel file as a text in powerbuilder 6.5

2 Answers2