Excel to Ini Loop

[pro2gramer5]

Hey guys,

New to the forum; been doing pretty well for the last few months writing my code by reading countless wikis, help files, forums, etc. but now I'm a little stuck.

I'm writing a relatively simple code (below) using For..To loops to go through multiple sheets in an excel file and read used ranges in a given column, then, writing a unique ini file based on the name of the active worksheet, and writing the array of that column to that ini file. I wouldn't have to do this if AutoIt could just read excel values without having to open the file...

I have 2 problems:

1. It's writing the same array (column data of the 1st worksheet) to each ini file

2. After going through 10 or so worksheets, it generates a COM error (80020008, bad variable type), and returns @error = 3 for "_Excel_RangeRead" which means the range is invalid. Every worksheet in the excel file has the same formatting and the same amount of columns, especially the column I'm requesting to be pulled.

Help would be appreciated!!

expandcollapse popup

#Include <MsgBoxConstants.au3>
#Include <Excel.au3>
#Include <WinAPIFiles.au3>

Global $oMyError = ObjEvent("AutoIt.Error","MyErrFunc")    ; Initialize a COM error handler

Global $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $oWorkbook = _Excel_BookOpen ($oExcel, @ScriptDir & "\Sample Physician List.xlsx")

If @error Then
    MsgBox($MB_SYSTEMMODAL, "Test", "Error opening workbook")
    _Excel_Close($oExcel)
    Exit
EndIf


Local $sSheet = _Excel_SheetList ($oWorkbook)
For $i = 0 To UBound($sSheet) - 1
    $sResult = _Excel_RangeRead ($oWorkbook, $sSheet[$i][0], $oWorkbook.ActiveSheet.Usedrange.Columns("E:E"))
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended & @CRLF & "Client = " & $sSheet[$i][0])
    For $k = 1 To UBound($sResult) - 1
        IniWriteSection ($sSheet[$i][0] & ".tmp", "Physician" & $k, $sResult[0], $sResult[$k])
    Next
Next

MsgBox($MB_SYSTEMMODAL, "Message", "Export Complete!")

Func MyErrFunc()
  Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !"    & @CRLF  & @CRLF & _
             "err.description is: " & @TAB & $oMyError.description  & @CRLF & _
             "err.windescription:"   & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "       & @TAB & hex($oMyError.number,8)  & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "   & @TAB & $oMyError.scriptline   & @CRLF & _
             "err.source is: "       & @TAB & $oMyError.source       & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile     & @CRLF & _
             "err.helpcontext is: " & @TAB & $oMyError.helpcontext _
            )
Endfunc

 

[pro2gramer5]

My apologies, in tinkering with the code, I left something that was incorrect. IniwriteSection is actually Iniwrite

For $k = 1 To UBound($sResult) - 1
    IniWrite ($sSheet[$i][0] & ".tmp", "Physician" & $k, $sResult[0], $sResult[$k])
Next

 

[water]

Welcome to AutoIt and the forum!
First question: Which version of AutoIt do you run?

[JLogan3o13]

@pro2gramer5 welcome to the forum. In your For Loop, even though you are stating $sSheet[$i][0] as the sheet you want to read from, you are then inputting ActiveSheet.UsedRange.Columns as your range parameter. Since you are not activating the next sheet, it continues to read only from the sheet that is active when you open the workbook.

 

[water]

33 minutes ago, pro2gramer5 said:

I wouldn't have to do this if AutoIt could just read excel values without having to open the file...

Second question: AutoIt (and every other language) needs to OPEN a file to read the content. Do you mean you do not want to see Excel open and DISPLAY the workbook? If yes then the solution is to open Excel and the workbook hidden.

[pro2gramer5]

Thanks for the reply!

@water I'm using Autoit3 (should be the latest version?). What I meant to say is that it's not like ini content where you can read values without having to open the inifile itself. The content I need will be viewed/accessed by many people and having them all open an excel file simultaneously in a shared location can (and will) be troublesome. This is why I want to create an easy way to export the contents of the excel file to several ini files.

@JLogan3o13 I tried tinkering with the range parameter, but I don't think it accepts variables. Is there a way to place a variable or to have it loop through worksheets?

[water]

2 minutes ago, pro2gramer5 said:

The content I need will be viewed/accessed by many people and having them all open an excel file simultaneously in a shared location can (and will) be troublesome.

_Excel_BookOpen allows to open a workbook as read-only. So multiple users accessing the same workbook shouldn't be a problem.

[water]

All functions allow you to specify the range as an object or as an "A1"-range (example: "A1:D7").
As soon as we are sure you can't solve your problem by directly accessing the Excel workbook we will check how to solve this problem

[water]

The following example starts 2 Excel intances in the background and opens the same workbook twice in read-only mode.

#include <Excel.au3>
Global $sPath = @ScriptDir & "\example.xlsx"
$oExcel1 = _Excel_Open(False, False, False, False, True) ; Force a new instance
$oWorkbook1 = _Excel_BookOpen($oExcel1, $sPath, True)
$oExcel2 = _Excel_Open(False, False, False, False, True) ; Force a new instance
$oWorkbook2 = _Excel_BookOpen($oExcel2, $sPath, True)
MsgBox(0, "Info", "Finished ...!")
_Excel_Close($oExcel1, False)
_Excel_Close($oExcel2, False)

 

Edited June 22, 2016 by water

[pro2gramer5]

@water Even in read only, I've had issues when attempting to open a shared excel file, getting issues such as "The file is locked for editing by...". I've done extensive troubleshooting such as looking at shared drive configurations, user config, excel config, etc. and can't seem to replicate the issue. Another issue is that I'm having a GUI read the content from that excel file and display it in a listview format for the user. It's already time consuming to open the large excel file, but longer if the GUI were to encounter an issue with opening the file.

Do you have any ideas for the code I posted?

[JLogan3o13]

@pro2gramer5 if you must do it the way you have it, you can change the active worksheet with this at the beginning of your For loop:

$oWorkbook.Worksheets($sSheet[$i][0]).Activate

 

[water]

In Excel you can allow shared access to a single workbook. Now you will get an error as soon as a users opens the workbook in write mode and a second user tries to read the workbook.
How large is the Excel workbook in question?

I will post some code how to access the workbook by multiple users where one of them modifies the workbook.

Edited June 22, 2016 by water

[pro2gramer5]

@JLogan3o13 your code worked, thanks!! I seem to have surpassed the COM error by adding in a Sleep (500) after the 2nd loop. Maybe this has something to do with the fact that _Excel_RangeRead has the automatic cleanup at the end of the function...? Either way, the code I posted reads only one column but the final product will read a lot more. We'll see how that works out.

@water  The excel file is about 15MB, has about 40 worksheets all containing 40 columns with a data range of 500 - 4000 rows each. I'm definitely interested in the code you speak of because we encounter this issue with other shared excel files. However, the content in this particular spreadsheet will be displayed in a custom GUI/listview according to certain filters/criteria selected by the user. Having the GUI open the excel file, read the ranges, filter by selected criteria, display them, all the while the same process is being done by multiple people, is too time consuming. It's MUCH faster to do this with iniread loops.

[water]

Depending on the version of Excel you run there shouldn#t be a problem with multiple users accessing the same workbook.
The following example has one Excel instance open the file R/W, alter cell A1 and have another instance open the same workbook as R/O.

#include <Excel.au3>
Global $sPath = @ScriptDir & "\example.xlsx"
$oExcel1 = _Excel_Open(Default, Default, Default, Default, True)
ConsoleWrite("1: " & @error & @CRLF)
$oWorkbook1 = _Excel_BookOpen($oExcel1, $sPath, False)
ConsoleWrite("2: " & @error & @CRLF)
_Excel_RangeWrite($oWorkbook1, Default, "***")
ConsoleWrite("3: " & @error & @CRLF)
$oExcel2 = _Excel_Open(Default, Default, Default, Default, True)
ConsoleWrite("4: " & @error & @CRLF)
$oWorkbook2 = _Excel_BookOpen($oExcel2, $sPath, True)
ConsoleWrite("5: " & @error & @CRLF)
$sResult = _Excel_RangeRead($oWorkbook2, Default, "A1")
ConsoleWrite("6: " & @error & "-" & $sResult & @CRLF)
MsgBox(0, "Info", "Finished ...!")
_Excel_Close($oExcel1, False)
ConsoleWrite("7: " & @error & @CRLF)
_Excel_Close($oExcel2, False)
ConsoleWrite("8: " & @error & @CRLF)

 

Edited June 23, 2016 by water

[water]

This thread describes what's going on when you get the "is locked for editing" message:

https://blogs.technet.microsoft.com/the_microsoft_excel_support_team_blog/2012/05/14/the-definitive-locked-file-post-updated-772014/

When do you encounter this error message? When opening or when saving the workbook?

 

[pro2gramer5]

The error message usually comes up when attempting to open the workbook. Some users have Excel 2010 while others have 2016.

All users would need to open in R/W mode. Is this possible without generating that error message?

[water]

As long as each user works on a different worksheet this should be possible.