Problem with Excel Read

[gahhon]

Hi Guys,

I was trying to read some data from the excel file and without opening the file. But I tried a lot of methods, it still open the file.

And also, I am able to capture the ColumnA value but not Column B.

Thanks for advance information.

Global $oDataA, $oDataB

Call ("ExcelRead", "B2", "C2")

Func ExcelRead($oColumnA, $oColumnB)
    Local $oPath = @ScriptDir & "\MyFile.xlsx"
    Local $oExcel = _Excel_Open()
    Local $oWorkbook = _Excel_BookOpen($oExcel, $oPath, 1, 0)

    $oDataA = _Excel_RangeRead($oWorkbook, "Sheet 1", $oColumnA)
    $oDataB = _Excel_RangeRead($oWorkbook, "Sheet 1", $oColumnB)
    MsgBox(0, "Test Value", $oDataA & ", " & $oDataB)
EndFunc

 

Edited November 25, 2018 by gahhon

[water]

How about:

Local $oWorkbook = _Excel_BookOpen($oExcel, $oPath, True, False)

[gahhon]

1 minute ago, water said:

How about:

Local $oWorkbook = _Excel_BookOpen($oExcel, $oPath, True, False)

Tried that as well. It still popped the excel file.

[water]

So the Excel application AND the workbook is visible?

[gahhon]

The Excel application is showed, but is empty. There's no workbook.

[water]

Then try:

Local $oExcel = _Excel_Open(False)
    Local $oWorkbook = _Excel_BookOpen($oExcel, $oPath, True)

 

[water]

Just now, gahhon said:

The Excel application is showed, but is empty. There's no workbook.

So it WAD
To make the application invisible you have to specify this when calling _Excel_Open.
To make the workbook invisible you have to specify this when calling _Excel_BookOpen. Not needed when the whole application is invisible.

 

[Subz]

Assume you also meant Sheet1 not "Sheet 1", you should also add _Excel_BookClose, _Excel_Close as well for example:

#include <Excel.au3>
Global $oDataA, $oDataB

ExcelRead("B2", "C2")

Func ExcelRead($oColumnA, $oColumnB)
    Local $sPath = @ScriptDir & "\MyFile.xlsx"
    Local $oExcel = _Excel_Open(False, False, False, False)
    Local $oWorkbook = _Excel_BookOpen($oExcel, $sPath, True, False)
    $sUsername = _Excel_RangeRead($oWorkbook, Default, $oColumnA)
    $sPassword = _Excel_RangeRead($oWorkbook, Default, $oColumnB)
    _Excel_BookClose($oWorkbook, False)
    _Excel_Close($oExcel)
    MsgBox(0, "Test Value", $sUsername & ", " & $sPassword)
EndFunc

 

[gahhon]

6 minutes ago, Subz said:

Assume you also meant Sheet1 not "Sheet 1", you should also add _Excel_BookClose, _Excel_Close as well for example:

#include <Excel.au3>
Global $oDataA, $oDataB

ExcelRead("B2", "C2")

Func ExcelRead($oColumnA, $oColumnB)
    Local $sPath = @ScriptDir & "\MyFile.xlsx"
    Local $oExcel = _Excel_Open(False, False, False, False)
    Local $oWorkbook = _Excel_BookOpen($oExcel, $sPath, True, False)
    $sUsername = _Excel_RangeRead($oWorkbook, Default, $oColumnA)
    $sPassword = _Excel_RangeRead($oWorkbook, Default, $oColumnB)
    _Excel_BookClose($oWorkbook, False)
    _Excel_Close($oExcel)
    MsgBox(0, "Test Value", $sUsername & ", " & $sPassword)
EndFunc

 

 

9 minutes ago, water said:

Then try:

Local $oExcel = _Excel_Open(False)
    Local $oWorkbook = _Excel_BookOpen($oExcel, $oPath, True)

 

Both of the solution is work fine to me. But with Subz solution, I am able to capture the ColumnB value and I dont know why.

And also, I did use a button trigger this function, but every 2nd time it showed the Excel Activation Error and showed 0, 0. Any idea?

[water]

What do you mean by:

10 minutes ago, gahhon said:

Excel Activation Error

Can you please post the error message or a screen shot?

[gahhon]

Here you go. Sometime it work okay, sometime are not.

If got value, then it work fine.

But if value = 0, it will showed this error.

[water]

Is this an activated version of Excel you run?

[gahhon]

3 minutes ago, water said:

Is this an activated version of Excel you run?

Haha. No.

But I thought I open file, read and store, then close. It shouldn't be showing any error like that.

And also, When I got value from the MsgBox other than 0. It no showing any activation error.

[water]

6 minutes ago, gahhon said:

Haha. No.

Unfortunately the Excel UDF can't help here. Active Excel and the problem will disappear

[gahhon]

1 minute ago, water said:

Unfortunately the Excel UDF can't help here. Active Excel and the problem will disappear

Even the value issue?

[water]

The Excel UDF calls Excel functions using COM. How Excel reacts to an unregistered version can't be handled by the UDF.

[gahhon]

56 minutes ago, water said:

The Excel UDF calls Excel functions using COM. How Excel reacts to an unregistered version can't be handled by the UDF.

Understood. In the meantime, then I will import the TextFile method instead. Thanks for help