Jump to content
ShawnW

Need help with File Encoding

Recommended Posts

ShawnW

I have a script that takes a large excel file, pulls out and reorganizes certain information I need, and spits out a trimmed down csv file which I uses to upload the information on my website. Some of this information contains characters with accents or em dashes. By default it would create a csv file in ANSI which I then uploaded but had to tell my website import system it was windows-1252 in order for it to look correct.

This was all working fine except now I need to add in a non-breaking space and non-breaking hyphen into parts of my output. At first I tried using ChrW(0xA0) and ChrW(0x2011) as replacements. A quick test in the console looked correct, however opening the csv output in notepad++ showed the space correctly but a ? for the hyphen and the file was still encoded as ANSI. I tried to view it as UTF-8 instead but this just made the space appear as xAO and also other characters appeared that way like my em dashes appeared as x97 and another symbol as xA7 etc.

If I instead do a convert to UTF-8 from notepad++ then those problems go away except the hyphen still displays as ?. I then noticed on the page I linked for the non-breaking hyphen it lists the UTF-8 hex as 0xE2 0x80 0x91 (e28091). I was unsure how to enter this in autoit but several things i tried all failed to get the hyphen inserted.

I need a way to get both the space and hyphen added correctly as either ANSI or UTF-8, but if it is UTF-8 then I need a way to convert all of the other data I extracted from the excel file.

I've included a test excel file with a single line and test script to create a csv demonstrating the problem.

test.xlsx

test.au3

Share this post


Link to post
Share on other sites
Subz

What about:

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

$excelFile = FileOpenDialog("Select test file", @DesktopDir, "Excel files (*.xls*)")

$oExcel = _Excel_Open(False, False, False, False, True)
$oWorkbook = _Excel_BookOpen($oExcel, $excelFile, True, False)
$table = _Excel_RangeRead($oWorkbook, Default, Default, 1, True)
_Excel_BookClose($oWorkbook)

$table[0][0] &= ". pp." & ChrW(0xA0) & $table[0][1] & ChrW(0x2014) & $table[0][2]
_ArrayColDelete($table, 2)
_ArrayColDelete($table, 1)

$sTable = _ArrayToString($table, ",")
$hFileOpen = FileOpen(@DesktopDir & "\test.csv", 258)
FileWrite($hFileOpen, $sTable)
FileClose($hFileOpen)

 

Share this post


Link to post
Share on other sites
ShawnW

Thanks for the suggestion.

The first problem there is that 0x2014 is the Em Dash that although present in the excel file data, is not what i need when I add the hyphen in that line. I need the shorter dash like on the keyboard except one that is treated as part of the word when wrapping. I'm thinking it is 0x2011 but I cannot get that to display. In researching the dashes I should honestly be using an En Dash (0x2013) for showing a range of numbers, but that breaks in wrapping, and I didn't get to set the requirements for this, I just have to come up with the solution.

Secondly, FileWrite on the array does add commas for the csv but that is all it does. It does not add quotes around cells that contain a comma already, and if it did, it wouldn't escape existing quotes either. I didn't have a line like this in my example because I did not consider a different way of creating the csv, but I do have lines that would require a more accurate csv creation.

Share this post


Link to post
Share on other sites
Subz

What happens if you use the following after Excel has created the file?  Not sure about the hyphen, if I get a chance will look into it further.

$hFileOpen = FileOpen(@DesktopDir & "\test.csv", 257)
FileClose($hFileOpen)

 

Share this post


Link to post
Share on other sites
ShawnW

That didn't seem to change the encoding. I did play with it some more though based on this idea. It seems neither that nor writing to the file in that method changed it to UTF8. I did find that if i read it, then open it in overwrite mode it and write it then it changes to UTF8.

$str = FileRead(@DesktopDir & "\test.csv")
$hFileOpen = FileOpen(@DesktopDir & "\test.csv",130)
FileWrite($hFileOpen, $str)
FileClose($hFileOpen)

But that still leaves the problem of the hyphen.

Share this post


Link to post
Share on other sites
Subz

Sorry was in a rush to get out the door this morning, have you tried uploading the file using ChrW(0x2011)?  If you open it in Excel the spreadsheet renders it correctly, in Notepad it will show special characters because not all fonts support ChrW(0x2011) for example Sans-Serif.

Share this post


Link to post
Share on other sites
jchd

I highly recommend DejaVu Sans Mono to be used in Notepad++ (and Scite as well). It covers a large range of Unicode and is particularly readable for programming and general text edit. Both mentionned codepoints show correctly, among many others.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
ShawnW
15 hours ago, Subz said:

Sorry was in a rush to get out the door this morning, have you tried uploading the file using ChrW(0x2011)?  If you open it in Excel the spreadsheet renders it correctly, in Notepad it will show special characters because not all fonts support ChrW(0x2011) for example Sans-Serif.

If try and upload it still shows as '?'. Also, I don't get the same result after opening in Excel 2016. Running on test file and opening in Excel shows the '?' on both the UTF8 and ANSI versions.

I tried something simpler and copied the actual hyphen and just added it in string form. It looks correct in the scite editor, and if I output it to the console when running it shows correctly there as well. Yet after Excel writes it to the csv it breaks, and no matter where I open it, or what font I use, it shows as a '?'.

It seems the problem is in the way excel makes the csv, I may just do a more complex csv manual creation building on your 1st example. It seems the display problem i had with the hyphen after writing the file without excel was indeed the font problem you refer to. If I can't do it that way though I may just have to fight every OCD bone in my body and add it as the html code &#x2011; since it is only ever going to be displayed on a webpage that way.

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

    • SlackerAl
      By SlackerAl
      I have an issue when starting Excel with the following code
      #include <Excel.au3> #include <GUIConstantsEx.au3> Opt("GUIOnEventMode", 1) GUICreate("Excel Test", 600, 440) GUISetOnEvent($GUI_EVENT_CLOSE, "MenuExit") GUISetState(@SW_SHOW) ; Create application object Local $oExcel = _Excel_Open() If @error Then Exit MsgBox(0, "Excel UDF: _Excel_RangeFind Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ; sit here forever with an option to react every 10ms While 1 Sleep(10) WEnd Exit Func MenuExit() GUIDelete() Exit EndFunc If the Excel is a standard install, everything is OK. If Excel has the Kutools add-in (https://www.extendoffice.com/product/kutools-for-excel.html) installed and active the excel process runs (and is visible in task manager until killed), but it never displays. Disabling the add-in restores normal functionality.
      If I add some additional code to interact with the excel application then still nothing happens if the add-in is active. However, if Excel is started first and then the AutoIt code is run, it is able to interact with the Excel session as normal.
      Summary: The Excel add-in Kutools prevents excel being started with the _Excel_Open() command from AutoIt. Any AutoIt side work-arounds for this?
    • Morphice
      By Morphice
      Hello , 
      I am new to autoIT, I am wondering if someone could guide me in the correct path for this program. Attached are the steps for the program as well as what I currently have. Any help is greatly appreciated . Thank You. 
      * workbooks\sheets will be organized on per level basis , 1st sheet lvl 1 ,2nd sheet lvl 2 etc or workbook 1 = level 1, workbook 2 = level 2 etc. 
      ; = comments and reminders 
      #include <MsgBoxConstants.au3> #include <EditConstants.au3> #include<excel.au3> #include<Array.au3> Global Const $PatientLookupX = 320 Global Const $PatientLookupY = 64 ; down 1 and enter Global Const $PatientTextBoxX = 410 Global Const $PatientTextBoxY = 217 ; click , Ctrl + V , Enter Global Const $PHMhubX = 512 Global Const $PHMhubY = 613 ;click down 1 enter button Global Const $HealthRiskAssesmetX = 40 Global Const $HealthRiskAssesmetY = 162 Global Const $AddnewAssesmentX = 168 Global Const $AddnewAssesmentY = 98 Global Const $SelectAssesmentX = 342 Global Const $SelectAssesmentY = 98 ; Down 7 and enter Risk score new Global Const $EmptyAnswerBarX = 465 Global Const $EmptyAnswerBarY = 145 Global Const $LowriskpreventionX = 716 Global Const $LowriskpreventionY = 324 Global Const $MediumriskPreventionX = 716 Global Const $MediumriskPreventionY = 352 Global Const $HighriskPreventionX = 716 Global Const $HighriskPreventionY = 377 Global Const $CatatrosphicPreventionX = 714 Global Const $CatatrosphicPreventionY = 399 Global Const $ClosebuttonX = 1167 Global Const $ClosebuttonY = 666 Global Const $SaveRiskButtonX = 1161 Global Const $SaveRiskButtonY = 692 Global Const $ExitCPScreenX = 1339 Global Const $ExitCPScreenY = 8 Global Const $ExitpatientHubX = 1000 Global Const $ExitpatientHubY = 79 Global Const $sleepMod = 2 Global Const $sleepVal = 5000*$sleepMod Global Const $sleepLow = 200*$sleepMod Global Const $sleepMed = 1000*$sleepMod Global Const $sleepHigh = 3500*$sleepMod ;Function Open excel , read account number in column A ;------------------------------------------------------------------------------------------------------------------------------------------------------- HotKeySet("{ESC}","stopbaby") Func _WinWaitActivate($title,$text,$timeout=0) $hWnd = WinWait($title,$text,$timeout) If Not WinActive($title,$text) Then WinActivate($title,$text) WinWaitActive($title,$text,$timeout) EndFunc $i=0 While $i <=2 $i = $i+1 Local $Open_excel = _Excel_Open() Local $File_path = "D:\AutoIT\Risk_Test.xlsx" Local $Open_workbook = _Excel_BookOpen($Open_excel,$File_path) WinActivate($Open_workbook) Local $Read_account_number = _Excel_RangeRead($Open_workbook,default,"A" &$i) _Excel_Close($Open_excel,False) WEnd ;--------------------------------------------------------------------------------------------------------------------------------------------------- Func NavtoSearch() WinActivate(eClinicalWorks (Garcia,Erick) Sleep($sleepMed) MouseClick("",693,77) Send("!p") ; shortcut for patient menu Send("{DOWN}") ; down 1 send ("{Enter}") ; patient lookup Sleep($sleepMed) ;paste account number How would I do this??? Send("{Enter}") ;Once patient is found, + enter = takes you to patient hub Sleep($sleepMed) Next NavtoPHMHub() ;------------------------------------------------------------------------------------------------------------------------------------------------------- Func NavtoPHMHub() MouseClick("",$PHMhubX,$PHMhubY) Sleep($sleepLow) Send("{DOWN}") Send("{ENTER}") ;takes you to Care Plan HUB Next NavtoRiskScore() ;------------------------------------------------------------------------------------------------------------------------------------------------------- Func NavtoRiskScore() MouseClick("",$HealthRiskAssesmetX,$HealthRiskAssesmetY) ;Clicks on HealthRisk assesment Sleep($sleepLow) MouseClick("",$AddnewAssesmentX,$AddnewAssesmentY) ; Click addnew assesment Sleep($sleepLow) MouseClick("",$SelectAssesmentX,$SelectAssesmentY) ;click select assesment tab Sleep($sleepLow) Send("{DOWN 7}") Send("{ENTER}") Sleep($sleepLow) MouseClick("",$EmptyAnswerBarX,$EmptyAnswerBarY) ;Click on Empty answer bar Sleep($sleepLow) MouseClick("",$LowriskpreventionX,$LowriskpreventionY) ; selects Risk Score, Change for other types 1-6 Next NavtoNextPatient() Func NavtoNextPatient MouseClick("",$ClosebuttonX,$ClosebuttonY) MouseClick("",$SaveRiskButtonX,$SaveRiskButtonY) MouseClick("",$ExitCPScreenX,$ExitCPScreenY) MouseClick("",$ExitpatientHubX,$ExitpatientHubY) EndFunc ;Function should loop back to excel sheet, copy next account number, activate eclinicalworks, and repeat the steps ;--------------------------------------------------------------------------------------------------------------------------------------------------------- Func stopbaby() exit EndFunc Best Regards,
      Morphice
      steps for program.docx
    • SlackerAl
      By SlackerAl
      Running the first example of _Excel_RangeFind from the help file (note I have added the version MsgBox and changed the path to _Excel1.xls)
      #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> MsgBox(0, "Version", @AutoItVersion) ; Create application object and open an example workbook Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\_Excel1.xls") If @error Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error opening workbook '" & @ScriptDir & "\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oExcel) Exit EndIf ; ***************************************************************************** ; Find all occurrences of value "37000" (partial match) ; ***************************************************************************** Local $aResult = _Excel_RangeFind($oWorkbook, "37000") If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Error searching the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Find all occurrences of value '37000' (partial match)." & @CRLF & "Data successfully searched.") _ArrayDisplay($aResult, "Excel UDF: _Excel_RangeFind Example 1", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment") I have also created a simple new Excel file "_Excel1.xls" in my script area and added "37000" to one cell.
      I generate the error:

      I arrived at this after generating the same error within my code. I'm using AutoIt version 3.3.14.2
      Any thoughts?
       
       
       
    • Ibet
      By Ibet
      Hey all, 
      Ending day 2 of learning AutoIt, and I'm stumped. I wrote an extremely rudimentary script simulating keystrokes for reading/copying values from one excel spreadsheet and pasting them into another spreadsheet, line by line. It works, but it doesn't use any of the Excel UDFs and was just sloppy. So, I'm trying to re-write it using some Excel UDFs to not only optimize the script, but to also learn how to use the Excel UDFs. If the answer is in a help file, please explain as I'm sometimes having problems understanding the examples in the help files.
      I'm getting the error: 
      "C:\Users\johndoe\Desktop\AutoIt Test\AutoIt_Read spreadsheet 1 - write spreadsheet 2-version2.au3" (25) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.: MsgBox(0,"Test","Test",$SourceEntry[1]) MsgBox(0,"Test","Test",^ ERROR >Exit code: 1 Time: 1.804 Here is the code:
      #include<Array.au3> #include<Excel.au3> ;-------------------Read from Source--------------------------- Local $oExcel_Source = _Excel_Open() Local $sWorkbook = "C:\Users\johndoe\Desktop\AutoIt Test\AutoIt_Testing_SOURCE.xlsx" Local $oWorkbook = _Excel_BookOpen($oExcel_Source,$sWorkbook) Local $SourceRow = 3 ;--eventually will be used to iterate through the rows, one at a time Local $SourceEntry[5] = _Excel_RangeRead($oWorkbook,Default,"A"&$SourceRow&":E"&$SourceRow) _ArrayDisplay($SourceEntry, "1D Display") ;--Displays array values correctly MsgBox(0,"Test","Test",$SourceEntry[1]) ;--Gives error, for any index in the array I want to make sure I can read the values of the array individually, before I try putting them into another document. This is because I've got to add some checks against the values already existing in the destination spreadsheet before any manipulation. I've spent the last hour or more googling that error and reading multiple posts where that error is meaning many different things, so unsure EXACTLY what the problem is. Would greatly appreciate a fix and/or explanation as well as patience with my noob-ness.
      Thanks in advance
    • 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  
×