_Excel_RangeRead

[antmar904]

Hi

I'm trying to read only column A in Excel but I am unable to.  Also, how would I skip reading cell A:1 which is the column name?

$Data = _Excel_RangeRead($Book, Default, "A:A") <-- This is not working "A:A"
    If @error Then
        MsgBox(0, "ERROR: Reading Cells", "ERROR: " & @error & "  Extended: " & @extended)
    EndIf

 

[JLogan3o13]

@antmar904 it is not working because you do not have it coded correctly. Look at the help file under _Excel_RangeRead, specifically Example script #3 to see what you are doing wrong.

Edit: and as far as removing the first cell, I personally would just read the entire range in and then delete that index from the array (or skip over it in any loops), much easier.

Edited July 19, 2018 by JLogan3o13

[water]

I can't test at the moment but I think "A:A" should be a valid range. What's the value of @error after calling _Excel_RangeRead?

[JLogan3o13]

I have always used 

$oWorkbook.ActiveSheet.Usedrange.Columns("A:A")

rather than just "A:A", otherwise it tries to pull every cell in the column, used or not.

[antmar904]

18 minutes ago, JLogan3o13 said:

I have always used 

$oWorkbook.ActiveSheet.Usedrange.Columns("A:A")

rather than just "A:A", otherwise it tries to pull every cell in the column, used or not.

@JLogan3o13 Yes this is what I am seeing.

[antmar904]

I ended up using a txt file for right now just to get the job done and will rewrite it using the excel file a little later and post.

[water]

Using the UsedRange approach as posted by @JLogan3o13 is by far the best solution

[ternal]

correct use of excel would be the best solution as said before

however I had a similar issue with trying to read columns of a big list (50k+). sometimes it would run perfectly on xlsx files sometimes it would cause an error on same files.
Excel functions like search are a lot slower as well for big files to read into an array
I ended up changing them from xlsx to csv and then used _FileReadToArray.

If you still would like to use txt files this function will convert all xlsx files to csv in a directory. There might be an UDF for this however I did not find any.

#include <Excel.au3>
#include <File.au3>

File_Excel2CSV("C:\user")

Func File_Excel2CSV(Const $FilePath)
    Local $o_Excel = _Excel_Open(False); opens an excel without making it visible
    If @error then MsgBox(0, "Error opening excel in Func File_Excel2CSV", "Error : " & @error & @CRLF & "Extended : "& @extended )
    Local $a_Filelist = _FileListToArray($FilePath, "*", $FLTA_FILES, True); get all files in the directory provided by $filepath
    Local $a_xlsxlist[UBound($a_Filelist , 1)]
    Local $iCount = 0
    If @error = 1 Then MsgBox(0, 0, "Path was invalid.")
    If @error = 4 Then MsgBox(0, 0, "No file(s) were found.")

    For $i = 1 to UBound($a_Filelist, 1) - 1 ; find all .xlsx files
        Local $a_Split_Line = StringSplit($a_Filelist[$i],".", $STR_NOCOUNT)
        If $a_Split_Line[1] == "xlsx" Then
            $a_xlsxlist[$iCount] = $a_Filelist[$i]
            $iCount +=1
        EndIf
    Next
    ReDim $a_xlsxlist[$iCount] ;get rid of free space in the list with all xlsx files
    For $i = 0 to UBound ($a_xlsxlist, 1) - 1
        Local $t_CSVName = StringSplit($a_xlsxlist[$i], ".", $STR_NOCOUNT) ; get the name of the file
        Local $o_WBtemp = _Excel_BookOpen($o_Excel, $a_xlsxlist[$i], False, False) ; open the xlsx file
        _Excel_BookSaveAs($o_WBtemp, $t_CSVName[0] & ".csv", $xlCSVMSDOS, True) ;save as csv
        If @error then MsgBox(0, "Error saving excel in Func File_Excel2CSV","error : " & @error & @CRLF & "Extended : " & @extended & @CRLF & "Filepath : " & $t_CSVName[0] & ".csv")
        _Excel_BookClose($o_WBtemp, False); close the file
        If @error then MsgBox(0, "error", "failed to close book in Func File_Excel2CSV" & @CRLF & "error : " & @error & @CRLF & "Extended : " & @extended)
    Next
    _Excel_Close($o_Excel, False)
    If @error then MsgBox(0, "error", "failed to close excel in Func File_Excel2CSV" & @CRLF & "error : " & @error & @CRLF & "Extended : " & @extended)
EndFunc

 

[antmar904]

On 7/21/2018 at 4:55 AM, ternal said:

correct use of excel would be the best solution as said before

however I had a similar issue with trying to read columns of a big list (50k+). sometimes it would run perfectly on xlsx files sometimes it would cause an error on same files.
Excel functions like search are a lot slower as well for big files to read into an array
I ended up changing them from xlsx to csv and then used _FileReadToArray.

If you still would like to use txt files this function will convert all xlsx files to csv in a directory. There might be an UDF for this however I did not find any.

#include <Excel.au3>
#include <File.au3>

File_Excel2CSV("C:\user")

Func File_Excel2CSV(Const $FilePath)
    Local $o_Excel = _Excel_Open(False); opens an excel without making it visible
    If @error then MsgBox(0, "Error opening excel in Func File_Excel2CSV", "Error : " & @error & @CRLF & "Extended : "& @extended )
    Local $a_Filelist = _FileListToArray($FilePath, "*", $FLTA_FILES, True); get all files in the directory provided by $filepath
    Local $a_xlsxlist[UBound($a_Filelist , 1)]
    Local $iCount = 0
    If @error = 1 Then MsgBox(0, 0, "Path was invalid.")
    If @error = 4 Then MsgBox(0, 0, "No file(s) were found.")

    For $i = 1 to UBound($a_Filelist, 1) - 1 ; find all .xlsx files
        Local $a_Split_Line = StringSplit($a_Filelist[$i],".", $STR_NOCOUNT)
        If $a_Split_Line[1] == "xlsx" Then
            $a_xlsxlist[$iCount] = $a_Filelist[$i]
            $iCount +=1
        EndIf
    Next
    ReDim $a_xlsxlist[$iCount] ;get rid of free space in the list with all xlsx files
    For $i = 0 to UBound ($a_xlsxlist, 1) - 1
        Local $t_CSVName = StringSplit($a_xlsxlist[$i], ".", $STR_NOCOUNT) ; get the name of the file
        Local $o_WBtemp = _Excel_BookOpen($o_Excel, $a_xlsxlist[$i], False, False) ; open the xlsx file
        _Excel_BookSaveAs($o_WBtemp, $t_CSVName[0] & ".csv", $xlCSVMSDOS, True) ;save as csv
        If @error then MsgBox(0, "Error saving excel in Func File_Excel2CSV","error : " & @error & @CRLF & "Extended : " & @extended & @CRLF & "Filepath : " & $t_CSVName[0] & ".csv")
        _Excel_BookClose($o_WBtemp, False); close the file
        If @error then MsgBox(0, "error", "failed to close book in Func File_Excel2CSV" & @CRLF & "error : " & @error & @CRLF & "Extended : " & @extended)
    Next
    _Excel_Close($o_Excel, False)
    If @error then MsgBox(0, "error", "failed to close excel in Func File_Excel2CSV" & @CRLF & "error : " & @error & @CRLF & "Extended : " & @extended)
EndFunc

 

Looks good, thank you!

[water]

BTW: Do not use the "Quote" button when you intend to reply to an answer. We all know what has been posted before.
Post your reply at the end of the thread in the "Reply to this thread ..." field.
Unnecessary quoting just clutters the thread

[antmar904]

Roger