Jump to content
Skeletor

FileReadLine to Array to Excel

Recommended Posts

Skeletor

Hi All,

I would like to know how you would take a FileLineRead and insert it into an array which then inserts it into Excel?

One thing to know is the files content is broken up, so I only use half of the content within $FileRead1.

So its imperative that the $value1, $value2, etc variables be used. 

Code below:

$FileRead1 = FileReadLine("C:\temp\sample.txt",1)


For $count = 1 To _FileCountLines($FileRead1) Step 1
    $string = FileReadLine($FileRead1, $count)
    $input = StringSplit($string, ",", 1)
    $value1 = $input[1]
    $value2 = $input[2]
    $value3 = $input[3]
    $value4 = $input[4]

    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "A1")
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "B1")
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1")
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value4, "D1")

Next

 

Edited by Skeletor

Share this post


Link to post
Share on other sites
FrancescoDiMuro

@Skeletor
What about _FileReadToArray() and _Excel_RangeWrite()? :)

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

Global $strFilePath = @ScriptDir & "\SampleFile.txt", _
       $arrFileContent, _
       $objExcel, _
       $objWorkbook, _
       $strExcelFile = @ScriptDir & "\SampleWorkbook.xls"


_FileReadToArray($strFilePath, $arrFileContent, $FRTA_NOCOUNT + $FRTA_ENTIRESPLIT, ",")
If @error Then
    ConsoleWrite("Error with _FileReadToArray(). Error: " & @error & @CRLF)
Else
    ; _ArrayDisplay($arrFileContent)
    $objExcel = _Excel_Open(False)
    If @error Then
        ConsoleWrite("Error while creating Excel object. Error: " & @error & @CRLF)
    Else
        $objWorkbook = _Excel_BookNew($objExcel)
        If @error Then
            ConsoleWrite("Error while creating the Workbook. Error: " & @error & @CRLF)
        Else
            _Excel_RangeWrite($objWorkbook, $objWorkbook.ActiveSheet, $arrFileContent)
            If @error Then
                ConsoleWrite("Error while writing the content of $arrFileContent in the Workbook. Error: " & @error & @CRLF)
            Else
                If FileExists($strExcelFile) Then FileDelete($strExcelFile)
                _Excel_BookSaveAs($objWorkbook, $strExcelFile, $xlExcel8)
                If @error Then
                    ConsoleWrite("Error while saving the Workbook. Error: " & @error & @CRLF)
                Else
                    ConsoleWrite("The content of the $arrFileContent array has been written in the Excel file." & @CRLF)
                    _Excel_BookClose($objWorkbook)
                    _Excel_Close($objExcel)
                EndIf
            EndIf
        EndIf
    EndIf
EndIf

Cheers :)

SampleFile.txt

Edited by FrancescoDiMuro
  • Like 1

Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
Skeletor

@FrancescoDiMuro 

Cool, but that's a straight dump from text file to Excel. 
I wanted to pick out only a few values from the text file and insert them into the Excel sheet... 

So, FileReadLine does this prefectly, reads one line at a time from the text file and inserts it into the Excel SpreadSheet.. but, it takes a long time. 
 

Share this post


Link to post
Share on other sites
FrancescoDiMuro

@Skeletor
You can always modify your array as much as you want.
Keep in mind that with _FileReadToArray() you fill your array with the content of the file; and then, you can do whatever you want, since you are working with the array...
You can create another array which contains only what you want ( or use directly the rows/columns you want to store in the second file ).

So, it's your choice :)
 


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
Skeletor

This is where I am confused.. I'm trying to figure out this whole array saga... 

I'm not much of a fan about these.. that's why I use FileReadLines and stuff like that... 
so if I use:
 

$Array = ($value1, $value2, $value3, $value4)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $Array, "A1")

Will that work?
Especially if it can trickle down the rows.. like A1, A2, A3, etc....?

Share this post


Link to post
Share on other sites
FrancescoDiMuro

@Skeletor
It is what _Excel_RangeWrite() does...
But all depends on how your array is composed...
If you have a 1-Dimensional array, using _Excel_RangeWrite() you are going to set all the values in the Workbook starting from "A1" cell ( leaving the Range parameter default ); so, in this case, if you have a 1-Dimensional array and you would like to set values in B, C, D... columns, you have to format you array in order to have a 2-Dimensional array ( rows and columns ).
So, the line 

5 minutes ago, Skeletor said:

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $Array, "A1")

it's correct, but you need to know how your $Array is formatted :)
 


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
Skeletor

Thanks FrancescoDiMuro, so I need to just work on my array... so do I get rid of the For..Loop then? 
Or will this loop through the file, read each line, then the StringSplit break it up like how I want and then spit it out like so....?

$FileRead1 = FileReadLine("C:\temp\sample.txt",1) ;File has a url in

For $count = 1 To _FileCountLines($FileRead1) Step 1
    $string = FileReadLine($FileRead1, $count)
    $input = StringSplit($string, ",", 1)
    $value1 = $input[1]
    $value2 = $input[2]
    $value3 = $input[3]
    $value4 = $input[4]
Next

$Array = ($value1, $value2, $value3, $value4)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $Array, "A1")

Is this correct? Or how would I format my Array... ?

Share this post


Link to post
Share on other sites
FrancescoDiMuro
24 minutes ago, Skeletor said:

$Array = ($value1, $value2, $value3, $value4)

This is not correct.
To set correctly your values in your array, you should do something like this:

#include <Array.au3>

Global $Array[1][4]

$FileRead1 = FileReadLine("C:\temp\sample.txt",1) ;File has a url in

For $count = 1 To _FileCountLines($FileRead1) Step 1
    $string = FileReadLine($FileRead1, $count)
    $input = StringSplit($string, ",", 1)    
    $value1 = $input[1]
    $value2 = $input[2]
    $value3 = $input[3]
    $value4 = $input[4]
    
    _ArrayAdd($Array, $value1 & "|" & $value2 & "|" & $value3 & "|" & $value4 & @CRLF)
Next

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $Array, "A1")

But I am going to tell you again that you are doing unecessary steps, which will take additional time to do what they do :)

Edited by FrancescoDiMuro

Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
Subz

As per FrancescoDiMuro _FileReadToArray example, just add the following afterwards:

This just deletes all the columns except for Column 0 to 3, then just use _Excel_RangeWrite to write it directly into Excel.

_FileReadToArray($strFilePath, $arrFileContent, $FRTA_NOCOUNT + $FRTA_ENTIRESPLIT, ",")

For $i = UBound($arrFileContent) - 1 To 4 Step - 1
    _ArrayColDelete($arrFileContent, $i)
Next

 

  • Like 1

Share this post


Link to post
Share on other sites
Skeletor

Thanks Subz, however I'm selecting only a few values form the files.. 

Example: 
 

aaa,sss,ddd,fff,ggg,hhh

When I stringSplit I take get this:
 

For $count = 1 To _FileCountLines($FileRead1) Step 1
    $string = FileReadLine($FileRead1, $count)
    $input = StringSplit($string, ",", 1)
    $value1 = $input[1]
    $value2 = $input[2]
    $value3 = $input[3]
    $value4 = $input[4]
Next

I then want to take $value1 and $value3 out from the file and insert it into Excel... 
 

Share this post


Link to post
Share on other sites
Skeletor

Using the FileArray will only dump the entire file as is into Excel... Yes splits it into columns, but thats not what I want.. i want to use select my data...

Share this post


Link to post
Share on other sites
FrancescoDiMuro

@Skeletor

6 minutes ago, Skeletor said:

I then want to take $value1 and $value3 out from the file and insert it into Excel... 

#include <Array.au3>

Global $Array[1][2]

$FileRead1 = FileReadLine("C:\temp\sample.txt",1) ;File has a url in

For $count = 1 To _FileCountLines($FileRead1) Step 1
    $string = FileReadLine($FileRead1, $count)
    $input = StringSplit($string, ",", 1)    
    $value1 = $input[1]
    $value3 = $input[3]
    
    _ArrayAdd($Array, $value1 & "|" & $value3 & @CRLF)
Next

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $Array, "A1")

If you don't want to use all the samples we already gave to you.

Edited by FrancescoDiMuro

Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
Subz

If you only wanted Input1 and Input3 then just delete the second column as well, or maybe I'm missing something?

_FileReadToArray($strFilePath, $arrFileContent, $FRTA_NOCOUNT + $FRTA_ENTIRESPLIT, ",")
For $i = UBound($arrFileContent) - 1 To 3 Step - 1
    _ArrayColDelete($arrFileContent, $i)
Next
_ArrayColDelete($arrFileContent, 1)
_ArrayDisplay($arrFileContent)

 

Share this post


Link to post
Share on other sites
Skeletor

Thanks Subz, but my examples are only a tiny bit of the large code I have.. 

So, basically, the $value1 and $value3 will not go in order... 

Let's say I have this:

$FileRead1 = FileReadLine("C:\temp\sample.txt",1)
Local $countlines = _FileCountLines($FileRead1)
Global $Array[$countlines]
For $count = 1 To _FileCountLines($FileRead1) Step 1
    $string = FileReadLine($FileRead1, $count)
    $input = StringSplit($string, ",", 1)
    $value1 = $input[1]
    $value2 = $input[2]
    $value3 = $input[3]
    $value4 = $input[4]
    $value5 = $input[5]
    $value6 = $input[6]
    $value7 = $input[7]
    $value8 = $input[8]
    $value9 = $input[9]
    _ArrayAdd($Array, $value9 & "|" & $value2 & "|" & " " & "|" & $value5 & "|" & $value7 & "|" & $value5 & "|" & $value3, 1, "|")
Next

_ArrayDisplay($Array)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $Array, "A2")

Now, I turned the Array into a 1D.. I get results but each one of the $values are in rows, and not like how I specified them... 
If in 2D I get no results.

Share this post


Link to post
Share on other sites
FrancescoDiMuro

@Skeletor
This

_ArrayAdd($Array, $value9 & "|" & $value2 & "|" & " " & "|" & $value5 & "|" & $value7 & "|" & $value5 & "|" & $value3, 1, "|")

Should be

_ArrayAdd($Array, $value9 & "|" & $value2 & "|" & " " & "|" & $value5 & "|" & $value7 & "|" & $value5 & "|" & $value3 & @CRLF, 1, "|")

 


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
caramen

Teacher @FrancescoDiMuro deserve the mvp statut xD


My video tutorials : HERE ( In construction ) 

How to Ask Help ||  UIAutomation From Junkew || WebDriver From Danp2 || And Water's UDFs in the Quote

Spoiler

 Water's UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Subz

Two other methods which would be much faster than FileReadLine

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

Global $g_sFilePath = @ScriptDir & "\SampleFile.txt"
Global $g_aFileData
_FileReadToArray($g_sFilePath, $g_aFileData, $FRTA_NOCOUNT + $FRTA_ENTIRESPLIT, ",")
    If @error Then Exit

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)

_Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, _ArrayExtract($g_aFileData, -1, -1, 8, 8), "A1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, _ArrayExtract($g_aFileData, -1, -1, 1, 1), "B1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, _ArrayExtract($g_aFileData, -1, -1, 4, 4), "D1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, _ArrayExtract($g_aFileData, -1, -1, 6, 6), "E1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, _ArrayExtract($g_aFileData, -1, -1, 4, 4), "F1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, _ArrayExtract($g_aFileData, -1, -1, 2, 2), "G1")

Second example:

#include <Array.au3>
#include <ArrayWorkshop.au3> ;~ https://www.autoitscript.com/forum/topic/180467-arrayworkshop/
#include <Excel.au3>
#include <File.au3>

Global $g_sFilePath = @ScriptDir & "\SampleFile.txt"
Global $g_aFileData
_FileReadToArray($g_sFilePath, $g_aFileData, $FRTA_NOCOUNT + $FRTA_ENTIRESPLIT, ",")

;~ Create the Order of Columns to write into Excel
;~ Note Columns are 0 index based.
Global $g_aColumnOrder[] = [8, 1, "", 4, 6, 4, 3, ""]
Global $g_aColumnData = _FileExtract($g_aFileData, $g_aColumnOrder)
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)
_Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $g_aColumnData)

Func _FileExtract($_aFileData, $_aColumnOrder)
    If $_aColumnOrder[0] = "" Then
        Local $aColumnData[UBound($_aFileData)][1]
    Else
        Local $aColumnData = _ArrayExtract($_aFileData, -1, -1, $_aColumnOrder[0], $_aColumnOrder[0])
    EndIf
    For $i = 1 To UBound($_aColumnOrder) - 1
        If $_aColumnOrder[$i] = "" Then
            _ArrayColInsert($aColumnData, $i)
        Else
            _ArrayAttach($aColumnData, _ArrayExtract($_aFileData, -1, -1, $_aColumnOrder[$i], $_aColumnOrder[$i]), 2)
        EndIf
    Next
    Return $aColumnData
EndFunc

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • nooneclose
      By nooneclose
      I forgot an important and needed aspect of my code. I need to be able to check for people who work every other week. Is it possible to write the dates that they work (every other week) between two date ranges? 
      Example:  Bob Smith has a Start date of 8-26-2018 (that is the date he is hired) And He stops working on 12-12-18 (that's the day he goes on vacation or whatever) However his work shift is every other Saturday from 7:30 AM to 6:00 Pm.  I need to calculate every other Saturday between 8-26-18 and 12-12-18 (is this clear?)
       
      here is the code I have so far. I am just missing this last part:
      ; Step 7 Func SendData() ;******************************************************************************* ; Sends all collected data to the Excel file in correct order for Upload ;******************************************************************************* MsgBox($MB_ICONINFORMATION, "Scheduler_Bot", "Sending Data", 2) ; Loop Counters $LoopCount = 0 $Array_Index = 0 $DayIndex = 0 $dataIndex = 0 ; Counter for the day of the week Local $dCount = 2 ; Counter for the numbers of Ys Local $yesCount = 0 ; Excel Write Counter (VERY IMPORTANT!) Local $EWriteCount = 2 ; Declare the global shift arrays (Sunday - Saturday) Global $ShiftDaySU[100][600] Global $ShiftDayM[100][600] Global $ShiftDayT[100][600] Global $ShiftDayW[100][600] Global $ShiftDayR[100][600] Global $ShiftDayF[100][600] Global $ShiftDayS[100][600] Global $sDates[400] While $Formatted_Names[$dataIndex] <> $Formatted_Names[$IndexRows] ; $LoopCount < $IndexRows $ACounter = 0 ; Array counter ; Gets the Start date from the array $Temp = $StartDate[$Array_Index] $TempStart = StringLeft($Temp, 8) $StartTempYear = StringLeft($TempStart, 4) $StartTempMonth = StringMid($TempStart, 5, 2) $StartTempDay = StringRight($TempStart, 2) ; Gets the End date from the array $Temp = $EndDate[$Array_Index] $TempEnd = StringLeft($Temp, 8) $EndTempYear = StringLeft($TempEnd, 4) $EndTempMonth = StringMid($TempEnd, 5, 2) $EndTempDay = StringRight($TempEnd, 2) ; The starting date (in value form) $sdate = _DateToDayValue($StartTempYear, $StartTempMonth, $StartTempDay) ;_DateToDayValue(2019,1,9) ;ConsoleWrite(@CRLF & "$start date " & $sdate & @CRLF & @CRLF) ; The ending date (in value form) $edate = _DateToDayValue($EndTempYear, $EndTempMonth, $EndTempDay) ;_DateToDayValue(2019,4,9) ;ConsoleWrite(@CRLF & "$end date " & $edate & @CRLF & @CRLF) ; Variables for readability Local $iYear, $iMonth, $iDay ;Stores what day of the week that shift lands on Local $tSU = _Excel_RangeRead($OpenWorkbook, Default, "I" & $dCount) If $tSU = "Y" Then $yesCount = 1 EndIf Local $tM = _Excel_RangeRead($OpenWorkbook, Default, "J" & $dCount) If $tM = "Y" Then $yesCount += 1 EndIf Local $tT = _Excel_RangeRead($OpenWorkbook, Default, "K" & $dCount) If $tT = "Y" Then $yesCount += 1 EndIf Local $tW = _Excel_RangeRead($OpenWorkbook, Default, "L" & $dCount) If $tW = "Y" Then $yesCount += 1 EndIf Local $tR = _Excel_RangeRead($OpenWorkbook, Default, "M" & $dCount) If $tR = "Y" Then $yesCount += 1 EndIf Local $tF = _Excel_RangeRead($OpenWorkbook, Default, "N" & $dCount) If $tF = "Y" Then $yesCount += 1 EndIf Local $tS = _Excel_RangeRead($OpenWorkbook, Default, "O" & $dCount) If $tS = "Y" Then $yesCount += 1 EndIf ConsoleWrite(@CRLF & @CRLF) ConsoleWrite($tSU & @CRLF) ConsoleWrite($tM & @CRLF) ConsoleWrite($tT & @CRLF) ConsoleWrite($tW & @CRLF) ConsoleWrite($tR & @CRLF) ConsoleWrite($tF & @CRLF) ConsoleWrite($tS & @CRLF) ConsoleWrite(@CRLF & @CRLF) ; Check to see if they work every other week Local $rotationWeek = _Excel_RangeRead($OpenWorkbook, Default, "U" & $dCount) If $rotationWeek = "0" Then ; Do nothing Else If $rotationWeek = 1 ; Do something Else ; $rotationWeek = 2 ; Do something EndIf EndIf Local $repeatWeek = _Excel_RangeRead($OpenWorkbook, Default, "V" & $dCount) If $rotationWeek = "0" Then ; Do nothing Else If $rotationWeek = 1 ; Do something Else ; $rotationWeek = 2 ; Do something EndIf EndIf While $yesCount > 0 If $tSU = "Y" Then For $Index = $sdate To $edate _DayValueToDate($Index, $iYear, $iMonth, $iDay) ; Finds the day based on its numerical value (1 = Sunday) If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 1 Then $Temp = $iYear & "-" & $iMonth & "-" & $iDay & "T" & "00" & ":" & "00" & ":" & "00" & ":" & "000" $ShiftDaySU[$ACounter][$DayIndex] = $Temp _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $ShiftDaySU[$ACounter][$DayIndex], "AI" & $EWriteCount) ; Send Name _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Formatted_Names[$dataIndex], "AA" & $EWriteCount) ; Send Start Time _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $StartTimes[$dataIndex], "AB" & $EWriteCount) ; Send End Time _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $EndTimes[$dataIndex], "AC" & $EWriteCount) ; Send Work Hours _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $HoursWorked[$dataIndex], "AD" & $EWriteCount) ; Send Work Group _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkGroup[$dataIndex], "AE" & $EWriteCount) ; Send Department _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkDepartment, "AF" & $EWriteCount) ; Send Supervisor _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Supervisors[$dataIndex], "AG" & $EWriteCount) ; Send Notes _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkNotes[$dataIndex], "AH" & $EWriteCount) ; Send Shift Number $tempStime = StringReplace($StartTimes[$dataIndex], ":", "") $tempEtime = StringReplace($EndTimes[$dataIndex], ":", "") _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, "SU " & $tempStime & "-" & $tempEtime, "AJ" & $EWriteCount) $ACounter += 1 $EWriteCount += 1 ConsoleWrite(@CRLF & "Sunday " & $Temp & @CRLF) EndIf Next $yesCount -= 1 $tSU = "N" ElseIf $tM = "Y" Then For $Index = $sdate To $edate _DayValueToDate($Index, $iYear, $iMonth, $iDay) ; Finds the day based on its numerical value (1 = Sunday) If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 2 Then $Temp = $iYear & "-" & $iMonth & "-" & $iDay & "T" & "00" & ":" & "00" & ":" & "00" & ":" & "000" $ShiftDayM[$ACounter][$DayIndex] = $Temp _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $ShiftDayM[$ACounter][$DayIndex], "AI" & $EWriteCount) ; Send Name _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Formatted_Names[$dataIndex], "AA" & $EWriteCount) ; Send Start Time _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $StartTimes[$dataIndex], "AB" & $EWriteCount) ; Send End Time _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $EndTimes[$dataIndex], "AC" & $EWriteCount) ; Send Work Hours _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $HoursWorked[$dataIndex], "AD" & $EWriteCount) ; Send Work Group _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkGroup[$dataIndex], "AE" & $EWriteCount) ; Send Department _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkDepartment, "AF" & $EWriteCount) ; Send Supervisor _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Supervisors[$dataIndex], "AG" & $EWriteCount) ; Send Notes _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkNotes[$dataIndex], "AH" & $EWriteCount) ; Send Shift Number $tempStime = StringReplace($StartTimes[$dataIndex], ":", "") $tempEtime = StringReplace($EndTimes[$dataIndex], ":", "") _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, "M " & $tempStime & "-" & $tempEtime, "AJ" & $EWriteCount) $ACounter += 1 $EWriteCount += 1 ConsoleWrite(@CRLF & "Monday " & $Temp & @CRLF) EndIf Next $yesCount -= 1 $tM = "N" ElseIf $tT = "Y" Then For $Index = $sdate To $edate _DayValueToDate($Index, $iYear, $iMonth, $iDay) ; Finds the day based on its numerical value (1 = Sunday) If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 3 Then $Temp = $iYear & "-" & $iMonth & "-" & $iDay & "T" & "00" & ":" & "00" & ":" & "00" & ":" & "000" $ShiftDayT[$ACounter][$DayIndex] = $Temp _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $ShiftDayT[$ACounter][$DayIndex], "AI" & $EWriteCount) ; Send Name _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Formatted_Names[$dataIndex], "AA" & $EWriteCount) ; Send Start Time _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $StartTimes[$dataIndex], "AB" & $EWriteCount) ; Send End Time _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $EndTimes[$dataIndex], "AC" & $EWriteCount) ; Send Work Hours _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $HoursWorked[$dataIndex], "AD" & $EWriteCount) ; Send Work Group _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkGroup[$dataIndex], "AE" & $EWriteCount) ; Send Department _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkDepartment, "AF" & $EWriteCount) ; Send Supervisor _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Supervisors[$dataIndex], "AG" & $EWriteCount) ; Send Notes _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkNotes[$dataIndex], "AH" & $EWriteCount) ; Send Shift Number $tempStime = StringReplace($StartTimes[$dataIndex], ":", "") $tempEtime = StringReplace($EndTimes[$dataIndex], ":", "") _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, "T " & $tempStime & "-" & $tempEtime, "AJ" & $EWriteCount) $ACounter += 1 $EWriteCount += 1 ConsoleWrite(@CRLF & "Tuesday " & $Temp & @CRLF) EndIf Next $yesCount -= 1 $tT = "N" ElseIf $tW = "Y" Then For $Index = $sdate To $edate _DayValueToDate($Index, $iYear, $iMonth, $iDay) ; Finds the day based on its numerical value (1 = Sunday) If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 4 Then $Temp = $iYear & "-" & $iMonth & "-" & $iDay & "T" & "00" & ":" & "00" & ":" & "00" & ":" & "000" $ShiftDayW[$ACounter][$DayIndex] = $Temp _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $ShiftDayW[$ACounter][$DayIndex], "AI" & $EWriteCount) ; Send Name _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Formatted_Names[$dataIndex], "AA" & $EWriteCount) ; Send Start Time _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $StartTimes[$dataIndex], "AB" & $EWriteCount) ; Send End Time _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $EndTimes[$dataIndex], "AC" & $EWriteCount) ; Send Work Hours _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $HoursWorked[$dataIndex], "AD" & $EWriteCount) ; Send Work Group _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkGroup[$dataIndex], "AE" & $EWriteCount) ; Send Department _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkDepartment, "AF" & $EWriteCount) ; Send Supervisor _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Supervisors[$dataIndex], "AG" & $EWriteCount) ; Send Notes _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkNotes[$dataIndex], "AH" & $EWriteCount) ; Send Shift Number $tempStime = StringReplace($StartTimes[$dataIndex], ":", "") $tempEtime = StringReplace($EndTimes[$dataIndex], ":", "") _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, "W " & $tempStime & "-" & $tempEtime, "AJ" & $EWriteCount) $ACounter += 1 $EWriteCount += 1 ConsoleWrite(@CRLF & "Wednesday " & $Temp & @CRLF) EndIf Next $yesCount -= 1 $tW = "N" ElseIf $tR = "Y" Then For $Index = $sdate To $edate _DayValueToDate($Index, $iYear, $iMonth, $iDay) ; Finds the day based on its numerical value (1 = Sunday) If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 5 Then $Temp = $iYear & "-" & $iMonth & "-" & $iDay & "T" & "00" & ":" & "00" & ":" & "00" & ":" & "000" $ShiftDayR[$ACounter][$DayIndex] = $Temp _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $ShiftDayR[$ACounter][$DayIndex], "AI" & $EWriteCount) ; Send Name _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Formatted_Names[$dataIndex], "AA" & $EWriteCount) ; Send Start Time _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $StartTimes[$dataIndex], "AB" & $EWriteCount) ; Send End Time _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $EndTimes[$dataIndex], "AC" & $EWriteCount) ; Send Work Hours _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $HoursWorked[$dataIndex], "AD" & $EWriteCount) ; Send Work Group _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkGroup[$dataIndex], "AE" & $EWriteCount) ; Send Department _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkDepartment, "AF" & $EWriteCount) ; Send Supervisor _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Supervisors[$dataIndex], "AG" & $EWriteCount) ; Send Notes _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkNotes[$dataIndex], "AH" & $EWriteCount) ; Send Shift Number $tempStime = StringReplace($StartTimes[$dataIndex], ":", "") $tempEtime = StringReplace($EndTimes[$dataIndex], ":", "") _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, "R " & $tempStime & "-" & $tempEtime, "AJ" & $EWriteCount) $ACounter += 1 $EWriteCount += 1 ConsoleWrite(@CRLF & "Thursday " & $Temp & @CRLF) EndIf Next $yesCount -= 1 $tR = "N" ElseIf $tF = "Y" Then For $Index = $sdate To $edate _DayValueToDate($Index, $iYear, $iMonth, $iDay) ; Finds the day based on its numerical value (1 = Sunday) If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 6 Then $Temp = $iYear & "-" & $iMonth & "-" & $iDay & "T" & "00" & ":" & "00" & ":" & "00" & ":" & "000" $ShiftDayF[$ACounter][$DayIndex] = $Temp _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $ShiftDayF[$ACounter][$DayIndex], "AI" & $EWriteCount) ; Send Name _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Formatted_Names[$dataIndex], "AA" & $EWriteCount) ; Send Start Time _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $StartTimes[$dataIndex], "AB" & $EWriteCount) ; Send End Time _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $EndTimes[$dataIndex], "AC" & $EWriteCount) ; Send Work Hours _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $HoursWorked[$dataIndex], "AD" & $EWriteCount) ; Send Work Group _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkGroup[$dataIndex], "AE" & $EWriteCount) ; Send Department _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkDepartment, "AF" & $EWriteCount) ; Send Supervisor _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Supervisors[$dataIndex], "AG" & $EWriteCount) ; Send Notes _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkNotes[$dataIndex], "AH" & $EWriteCount) ; Send Shift Number $tempStime = StringReplace($StartTimes[$dataIndex], ":", "") $tempEtime = StringReplace($EndTimes[$dataIndex], ":", "") _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, "F " & $tempStime & "-" & $tempEtime, "AJ" & $EWriteCount) $ACounter += 1 $EWriteCount += 1 ConsoleWrite(@CRLF & "Friday " & $Temp & @CRLF) EndIf Next $yesCount -= 1 $tF = "N" ElseIf $tS = "Y" Then For $Index = $sdate To $edate _DayValueToDate($Index, $iYear, $iMonth, $iDay) ; Finds the day based on its numerical value (1 = Sunday) If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 7 Then $Temp = $iYear & "-" & $iMonth & "-" & $iDay & "T" & "00" & ":" & "00" & ":" & "00" & ":" & "000" $ShiftDayS[$ACounter][$DayIndex] = $Temp _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $ShiftDayS[$ACounter][$DayIndex], "AI" & $EWriteCount) ; Send Name _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Formatted_Names[$dataIndex], "AA" & $EWriteCount) ; Send Start Time _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $StartTimes[$dataIndex], "AB" & $EWriteCount) ; Send End Time _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $EndTimes[$dataIndex], "AC" & $EWriteCount) ; Send Work Hours _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $HoursWorked[$dataIndex], "AD" & $EWriteCount) ; Send Work Group _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkGroup[$dataIndex], "AE" & $EWriteCount) ; Send Department _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkDepartment, "AF" & $EWriteCount) ; Send Supervisor _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Supervisors[$dataIndex], "AG" & $EWriteCount) ; Send Notes _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkNotes[$dataIndex], "AH" & $EWriteCount) ; Send Shift Number $tempStime = StringReplace($StartTimes[$dataIndex], ":", "") $tempEtime = StringReplace($EndTimes[$dataIndex], ":", "") _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, "S " & $tempStime & "-" & $tempEtime, "AJ" & $EWriteCount) $ACounter += 1 $EWriteCount += 1 ConsoleWrite(@CRLF & "Saturday " & $Temp & @CRLF) EndIf Next $yesCount -= 1 $tS = "N" Else ;Error Nothing equals "Y" ConsoleWrite(@CRLF & "Error Nothing equals 'Y'" & @CRLF) EndIf $DayIndex += 1 $LoopCount += 1 WEnd $Array_Index += 1 $dataIndex += 1 $dCount += 1 WEnd MsgBox($MB_ICONINFORMATION, "Scheduler_Bot", "Finished Sending Data", 2) EndFunc  
    • rudi
      By rudi
      Hello,
      I face an Excel related error right after doing an _Excel_BookOpen. This is an EXCEL worksheet with filters defined, quite simple sheet. Once a week at Friday there is a scheduled task running on the file server "printing" the content to a PDF file for documentation puposes. As this is running 100% unattended it's a show stopper, if such dialog boxes show up.
       
      Already when I did that script a month ago I faced the issue, that to dialog boxes showed up telling something about a "name conflict", once for "_FilterDatabase", and a 2nd time for "PrintingArea" (maybe _PrintingArea).

       
      When some new name is entered, the script is going on with the PDF creation. But the autoit script is hanging with the _Excel_BookOpen, so I would need to start a 2nd. script to look for such bogus name conflict message boxes.
       
      While trying do track down what's going on in detail, the issue vanished again, I answerd the two boxes with "xxxx" and "yyyy" for new fiel names, the file seems to have been saved by me myself without intention (or automatically by EXCEL.AU3?)
       
      several other postings point into the direction of "this is an Excel Bug", seems to be not strictly Autoit related.
       
      Facts:
      Windows 7 Pro x64 Office 2010 SP2 32bit Localization = German (Win & Office) Autoit v3.3.14.5 Excel Workbook with three sheets without any Macros: "TBx Projektliste.xlsx", just 82 kByte  
      Export-Excel-to-PDF-Projektstatus.au3
       
      Any suggestions howto take care, that these "name conflicts" cannot occure?
    • Burgs
      By Burgs
      Greetings,
        I seem to be having a problem trying to insert values into an array in excess of the size of the array (its Ubound value).  I thought the command would simply 'ReDim' the array in order to add another value...however that does not seem to be happening.  My code is as below:
       
      ;**SET DYNAMIC ARRAY DIMENSIONS... $vValue = Int($_STRUCTURE_LEVEL - 1) ;seek the '$_HIERARCHY' level that is one 'previous' to the 'current' value...! $iStart = 0 ;set to begin search from element "0" in array... Do $_Files_Located = _Arraysearch($_HIERARCHY, $vValue, $iStart) if Int($_Files_Located) <> -1 Then $iEnd = 1 For $_RIGGING = 0 to Ubound($_LINE_DETAIL3) - 1 _ArrayInsert($_STRUCTURES, $_Files_Located + $iEnd, String($_LINE_DETAIL3[$_RIGGING])) _ArrayInsert($_HIERARCHY, $_Files_Located + $iEnd, Int($_STRUCTURE_LEVEL)) _ArrayInsert($_INFERIOR_TMPLS, $_Files_Located + $iEnd, Int(-1)) $iEnd += 1 ;increment EACH ITERATION... Next ;Next $_RIGGING EndIf ;'$_Files_Located' NOT "-1"...value for previous '$_STRUCTURE_LEVEL' ;was located in '$_HIERARCHY' array... $iStart += (Ubound($_LINE_DETAIL3) + 1) ;increment the offset index element position to begin the next search... ;"+ 1" to INCLUDE the 'parent' ('searched') UNIT...! Until $_Files_Located == -1 ;end loop when previous '$_STRUCTURE_LEVEL' is NOT found in '$_HIERARCHY' array... ;**  
        This code routine works perfectly fine except when the 'searched' value ($_Files_Located) happens to be the final element position in the searched array...how can I modify this routine so that the final additions at the end of the array(s) are made?  I thank in advance for any replies. 
       
    • DarkFingers1337
      By DarkFingers1337
      Hey,
      what is more efficient, using multiple arrays or one multidimensional array?
       
      ; Hamster data structure using multiple arrays Global $HamsterCount = 6 Global $HamsterX[$HamsterCount] Global $HamsterY[$HamsterCount] ;giving each hamster an x and y position For $i=0 To $HamsterCount-1 $HamsterX[$i] = 0 $HamsterY[$i] = 0 Next ; The same hamster data structure using one multidimensional array Global $HAMSTER_COUNT = 6 Global Enum $HAMSTER_X, _ $HAMSTER_Y, _ $HAMSTER_MAX Global $Hamsters[$HAMSTER_COUNT][$HAMSTER_MAX] ;giving each hamster an x and y position For $i=0 To $HAMSTER_COUNT-1 $Hamsters[$i][$HAMSTER_X] = 0 $Hamsters[$i][$HAMSTER_Y] = 0 Next  
    • ahha
      By ahha
      I think this is a very basic question, but I'm stumped after trying to solve it for weeks.  The program below illustrates the issue.  I have several instances of Excel open, each instance having several books open, each book with several sheets.  I'm able to list all this information, however I can't seem to figure out the sheet and workbook for a user selected range.  Any hints appreciated because at this point as I feel like a blind squirrel looking for a nut
       
      #AutoIt3Wrapper_run_debug_mode=Y #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> #include <Debug.au3> ;Illustrate issue I'm having. For a user seletecd range (possibly multiple $oWorkbooks open with multiple sheets), I need to determine the Excel application object of the selected cells and the sheet ;I need $oWorkbook, $WorkSheet, $Range ;Simulate issue - in real world user may have opened Excel and I have no knowledge of the object $oExcel1 = _Excel_Open() ;open first instance _Excel_BookNew($oExcel1) ;workbook with 3 sheets _Excel_BookNew($oExcel1) ;another workbook in same instance with 3 sheets $oExcel2 = _Excel_Open(Default, Default, Default, Default, True) ;open second instance _Excel_BookNew($oExcel2) ;workbook with 3 sheets _Excel_BookNew($oExcel2) ;another workbook in same instance with 3 sheets $oExcel3 = _Excel_Open(Default, Default, Default, Default, True) ;open third instance _Excel_BookNew($oExcel3) ;workbook with 3 sheets _Excel_BookNew($oExcel3) ;another workbook in same instance with 3 sheets ;now here's what I know without a priori knowledge of the objects ;the workbook names are unigue - that is there will never be a Book1 in any but one of the instances or filename (i.e. single open instance of a particular file) $aWorkBooks = _Excel_BookList() ;get an array of all workbooks open ;Success: a two-dimensional zero based array with the following information: ;col 0 - Object of the workbook ;col 1 - Name of the workbook/file ;col 2 - Complete path to the workbook/file If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error listing workbooks.", "@error = '" & @error & "'" & @CRLF &"@extended = '" & @extended & "'") _DebugArrayDisplay($aWorkBooks, "List of all workbooks open. Col 0 = Object, Col 1 = workbook name, Col 2 = full filename path") ;at this point we have the Object associated with the book name but no full filename path as not saved yet ;now list the sheets for each Object Workbook For $i = 0 to UBound($aWorkBooks, $UBOUND_ROWS) - 1 ;0 based $aWorkSheets = _Excel_SheetList($aWorkBooks[$i][0]) ;Col 0 = Workbook object ;Success: a two-dimensional zero based array with the following information: ; 0 - Name of the worksheet ; 1 - Object of the worksheet If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error listing Worksheets.", "@error = '" & @error & "'" & @CRLF & "@extended = '" & @extended & "'") _ArrayDisplay($aWorkSheets, "$aWorkSheets for $aWorkBooks[" & $i & "]") Next MsgBox($MB_SYSTEMMODAL + $MB_OK, "Info", "Select a range in any Excel instance, any Workbook, and any sheet. Then click OK.") ;I have spent weeks trying to figure this out. Looked at Water's UDF (excellent tight code) and got nothing using a default. All need $oExcel ;********** all this is attempts to get it and they all failed ;********** ;from this: https://www.autoitscript.com/autoit3/docs/functions/ObjGet.htm ;found a possible clue in comment "Error Getting an active Excel Object. <------- **ACTIVE** - so try it Local $oDefaultActiveExcelObject = ObjGet("", "Excel.Application") ; Get an existing Excel Object If @error Then MsgBox($MB_SYSTEMMODAL, "DEBUG", "Error Getting an active Excel Object. Error code: " & Hex(@error, 8)) Else MsgBox($MB_SYSTEMMODAL, "DEBUG", "Success - we got an active Excel Object") EndIf ;Now I have the object so get the rest of the info. We could check this instance against the opened ones. ;hard coded for testing. If $oDefaultActiveExcelObject = $oExcel1 Then MsgBox($MB_SYSTEMMODAL, "DEBUG", "$oExcel1 is the active Excel Object") Else If $oDefaultActiveExcelObject = $oExcel2 Then MsgBox($MB_SYSTEMMODAL, "DEBUG", "$oExcel2 is the active Excel Object") Else If $oDefaultActiveExcelObject = $oExcel3 Then MsgBox($MB_SYSTEMMODAL, "DEBUG", "$oExcel3 is the active Excel Object") Else MsgBox($MB_SYSTEMMODAL, "DEBUG", "ERROR - I have no idea what the active Excel Object is.") EndIf EndIf EndIf ;go ahead and get information MsgBox($MB_SYSTEMMODAL, "DEBUG", "$oDefaultActiveExcelObject.ActiveWorkbook.Name = '" & $oDefaultActiveExcelObject.ActiveWorkbook.Name & "'") ; <<<<<<<<<<<<<------------ this picked the wrong one. **So it looks like each instance has an active workbook.** ;At this point I'm really stumped. I probably should submit to the experts. ;I need to find $oExcel, $oWorkbook, $vWorkSheet, for the user selected range because I want to use ;$vRange = _Excel_RangeRead($oWorkbook, $vWorksheet, $oExcel.Selection.Address) ;this returns absolute like $D$3:$E$5 UNLESS it's a single cell then it returns only single absolute like $E$2 $vRange = _Excel_RangeRead("Book4", "Sheet2", "C2") ;this returns absolute like $D$3:$E$5 UNLESS it's a single cell then it returns only single absolute like $E$2 MsgBox(0, "Info", "The name of the active sheet is '" & $oExcel1.ActiveSheet.Name & "'") ;still need application object $oExcel1 MsgBox($MB_SYSTEMMODAL, "Info", "$vRange = '" & $vRange & "'") ;knowing $oExcel instance might be helpful ;$vRange = $oExcel.Selection.Address ;this returns absolute like $D$3:$E$5 UNLESS it's a single cell then it returns only single absolute like $E$2 ;I need to know the $oExcel ;I don't think I can use _Excel_BookAttach in any way as I need to know in advance a string, a filename, or an instance ;Au3Info not showing any distinctions - I'm stuck. MsgBox($MB_SYSTEMMODAL, "Info", "Pause before exit.") Exit  
×