Jump to content

Copy & Paste from Excel In a Loop


Recommended Posts

HI! ... this is a big one (at least for me) :sweating:

You guys previously helped me copy the used range in column A and paste them into a Website one at a time in a loop. Cool! Now, for another function, I have 2 columns, A and B, and two input boxes in the Website. I'm having a hard time replicating the loop for the 2 columns. :frantics:

This is how I'm opening the Excel workbook (copied from the previous function that only had 1 column). I need to also get the used range in column B.

Func OpenExcelForCopy()

    Global $aBBTableData
    Global $oExcel = _Excel_Open()
    Global $oWorkbook = _Excel_BookOpen($oExcel, $ChosenFileName, Default, True, True)
    $oExcel.Sheets("CopyCourses").Activate
;~ Get all used cells in column A:A
    Global $aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("CopyCourses").Usedrange.Columns("A:A"))
;~ Duplicate the $aSearchItems Array
    Global $aSearchResult = $aSearchItems
;~ Loop through the array starting at 0 until the end of the array which is (Ubound($aSearchItems) - 1)
    For $i = 0 To UBound($aSearchItems) - 1
        $aSearchResult[$i] = SearchCourseForCopy($aSearchItems[$i])
    Next
    _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")

    Finished()
EndFunc   ;==>OpenExcelForCopy

Then we eventually get here. I don't think anything needs to change here but I'm not sure. This is where I paste the data from Column A into an input field (which is a search tool in a website). If the search is good, then we get to the tricky part...

;~ OK, we logged in and we searched for a course. Lets COPY it!
Func CopyCourseBegin()
    Local $sResult
    $iSearchIndex = _ArraySearch($aBBTableData, "Course ID", 0, 0, 0, 1, 1, 0)

;~  If the course was not found, do this.
    If $iSearchIndex = -1 Then
;~      MsgBox(4096, "Search Error", "Item not found")
        $sResult = "Source Not Found"
        _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")
;~  Now go back to the Excel sheet and search for the next one.

;~ If the course was found, begin the COPY process.
    Else
        For $i = 0 To UBound($aSearchItems) - 1
            $aSearchResult[$i] = CopyCourseNow($aSearchItems[$i])
        Next
        $sResult = "Copied"
        _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")
    EndIf
    Return $sResult
EndFunc   ;==>CopyCourseBegin

This is the "tricky part" where I'm confused. I can copy and paste what's in column A just fine, but I can't manage to replicate it for column B. I need to paste whats in Column B into "destinationCourseId"

;~ The course search was successful. COPY the course now.
Func CopyCourseNow($_sSearchResult)

;~  Navigate to the course copy page.
    _IENavigate($oIE, $urlBBCourseCopy)

;~  Copy the SOURCE course ID from the Excel sheet

;~ Paste whats copied from column A into the Source Course ID text box
    Local $oForm = _IEGetObjByName($oIE, "selectCourse")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "sourceCourseId")
    _IEFormElementSetValue($oSearchString, $_sSearchResult)

;~ Paste whats copied from column B into the Destination Course ID text box  ?!?!?!?!
    Local $oForm = _IEGetObjByName($oIE, "selectCourse")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "destinationCourseId")
    _IEFormElementSetValue($oSearchString, $_sSearchResult)
    
 ;~ Just exit cause im stuck :(   
    _Exit()

EndFunc   ;==>CopyCourseNow

After I paste the data from column A into "sourceCourseId" and column B into "destinationCourseId", I'll make it do some stuff. Then I need it to loop around until the used ranges in column A & B is finished.

Does the entire code need to change now that there's two columns?

 

 

Link to post
Share on other sites

The array would become a 2d array $aSearchItems

$aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("CopyCourses").Usedrange.Columns("A:B"))

You can then obtain that information in a loop as follows:

For $i = 0 To Ubound($aSearchItems) - 1
    ;~ Column 0
    ConsoleWrite($aSearchItems[$i][0] & @CRLF)
    ;~ Column 1
    ConsoleWrite($aSearchItems[$i][1] & @CRLF)
    $aSearchResult[$i] = CopyCourseNow($aSearchItems[$i][0], $aSearchItems[$i][1])
Next

For CopyCourseNow function use something like:

;~ The course search was successful. COPY the course now.
Func CopyCourseNow($_sourceCourseId, $_destinationCourseId)
Local $sResult
;~  Navigate to the course copy page.
    _IENavigate($oIE, $urlBBCourseCopy)

;~  Copy the SOURCE course ID from the Excel sheet

;~ Paste whats copied from column A into the Source Course ID text box
    Local $oForm = _IEGetObjByName($oIE, "selectCourse")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "sourceCourseId")
    _IEFormElementSetValue($oSearchString, $_sourceCourseId)

;~ Paste whats copied from column B into the Destination Course ID text box  ?!?!?!?!
    Local $oForm = _IEGetObjByName($oIE, "selectCourse")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "destinationCourseId")
    _IEFormElementSetValue($oSearchString, $_destinationCourseId)
;~ .... Do stuff
Return $sResult
EndFunc   ;==>CopyCourseNow

You may need to post all your code, as the code is disjointed.

Edited by Subz
Link to post
Share on other sites

Yeaa I didn't do it right.. I'm getting errors with my SearchCourseForCopy function and CopyCourseNow function. It says its being called by a previous line with 2 args.

I'm unsure how to properly use functions like "CopyCourseNow($aSearchItems[$i])", like how it has arguments and stuff.. :huh2:

Here's everything

;~  The user clicked the "Copy Courses" button. This function will execute now.
Func _LoginForCopy()
;~  Disable the "Function" buttons again to prevent multiple processes.
    GUICtrlSetState($DeleteCourse, $GUI_DISABLE)
    GUICtrlSetState($CopyCourse, $GUI_DISABLE)
    GUICtrlSetState($CourseFunction3, $GUI_DISABLE)
    $CopyReady = MsgBox(52, "Ready to copy courses", "At any time, you can..." & @CRLF & "   • press the Pause key to pause the script (-__-)" & @CRLF & "   • press the F4 key to kill the app (x__x)" & @CRLF & @CRLF & "Shall we begin?  (o__o)")
    If $CopyReady == 6 Then
        GUICtrlSetBkColor($CopyCourse, 0x32CD32)
;~  Open a IE window and navigate to the login page.
        Global $oIE = _IECreate($urlBBLogin)
        _IELoadWait($oIE)
;~  Recognize the form on this page (login input boxes).
        Local $oForm = _IEFormGetObjByName($oIE, "login")
        Local $oTextLogin = _IEFormElementGetObjByName($oForm, "user_id")
        Local $oTextPass = _IEFormElementGetObjByName($oForm, "password")
;~  Enter the Automation user credentials into the form.
        _IEFormElementSetValue($oTextLogin, $bbUsername)
        _IEFormElementSetValue($oTextPass, $bbPassword)
;~  Click the Login button.
        _IEFormSubmit($oForm)
        _IELoadWait($oIE)
;~  Begin the next function. Go there.
        OpenExcelForCopy()
;~ If the user selects "No" then go back.
    Else
        GUICtrlSetState($DeleteCourse, $GUI_ENABLE)
        GUICtrlSetState($CopyCourse, $GUI_ENABLE)
        GUICtrlSetState($CourseFunction3, $GUI_DISABLE)
    EndIf
EndFunc   ;==>_LoginForCopy

;~  OK were logged in. Now copy the used range  in column A and column B from the Excel workbook.
Func OpenExcelForCopy()
    Global $aBBTableData
    Global $oExcel = _Excel_Open()
    Global $oWorkbook = _Excel_BookOpen($oExcel, $ChosenFileName, Default, True, True)
    $oExcel.Sheets("CopyCourses").Activate
;~ Get all used cells in columns A and B <---- it this right?
    Global $aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("CopyCourses").Usedrange.Columns("A:B"))
;~ Duplicate the $aSearchItems Array
    Global $aSearchResult = $aSearchItems
;~ Loop through the array starting at 0 until the end of the array which is (Ubound($aSearchItems) - 1)
    For $i = 0 To UBound($aSearchItems) - 1
    $aSearchResult[$i] = SearchCourseForCopy($aSearchItems[$i])
    Next
    For $i = 0 To UBound($aSearchItems) - 1
;~ Column 0
        ConsoleWrite($aSearchItems[$i][0] & @CRLF)
;~ Column 1
        ConsoleWrite($aSearchItems[$i][1] & @CRLF)
        $aSearchResult[$i] = SearchCourseForCopy($aSearchItems[$i][0], $aSearchItems[$i][1])
;~ Were going to put the results of our work into Column C
    Next
    _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")
    Finished()
EndFunc   ;==>OpenExcelForCopy

;~  Now that were logged in, navigate to the course search page.
Func SearchCourseForCopy($aSearchItems[$i][0], $aSearchItems[$i][1])
    _IENavigate($oIE, $urlBBCourseSearch)
    _IELoadWait($oIE)
;~  Change the search criteria to "Course ID"
    _bbCourseSearchCategoryChange("Course ID")
;~ Paste whats copied from Column A into the search text box and click submit
    Local $oForm = _IEGetObjByName($oIE, "courseManagerFormSearch")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "courseInfoSearchText")
    _IEFormElementSetValue($oSearchString, $_sSearchResult)
    _IEFormSubmit($oForm)
    _IELoadWait($oIE)
;~ Lets see what we got from the search
    Local $oBBTable = _IETableGetCollection($oIE, 2)
    $aBBTableData = _IETableWriteToArray($oBBTable)
;~  _ArrayDisplay($aBBTableData)
    Return CopyCourseBegin()
EndFunc   ;==>SearchCourseForCopy

;~ OK, we seacrhed for Column A...
Func CopyCourseBegin()
    Local $sResult
    $iSearchIndex = _ArraySearch($aBBTableData, "Course ID", 0, 0, 0, 1, 1, 0)
;~  If the search was bad, go down to the next row in Column A.
    If $iSearchIndex = -1 Then
;~      MsgBox(4096, "Search Error", "Item not found")
        $sResult = "Source Not Found"
        _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")
;~  Now go back to the Excel sheet and search for the next one.

;~ If the search was good, begin the COPY process.
    Else
        For $i = 0 To UBound($aSearchItems) - 1
            $aSearchResult[$i] = CopyCourseNow($aSearchItems[$i])
        Next
        $sResult = "Copied"
        _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")
    EndIf
    Return $sResult
EndFunc   ;==>CopyCourseBegin

;~ The course search was successful. COPY now.
Func CopyCourseNow($_sourceCourseId, $_destinationCourseId)
    Local $sResult
;~  Navigate to the course copy page.
    _IENavigate($oIE, $urlBBCourseCopy)
;~ Paste whats copied from column A into the SOURCE Course ID text box
    Local $oForm = _IEGetObjByName($oIE, "selectCourse")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "sourceCourseId")
    _IEFormElementSetValue($oSearchString, $_sourceCourseId)
;~ Paste whats copied from column B into the DESTINATION Course ID text box  ?!?!?!?! <--- Need Help Here. I can only get Column A data
    Local $oForm = _IEGetObjByName($oIE, "selectCourse")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "destinationCourseId")
    _IEFormElementSetValue($oSearchString, $_destinationCourseId)
;~ .... Do stuff

    MsgBox(0, "?", "Did it work?")
    Return $sResult

EndFunc   ;==>CopyCourseNow

 

Please let me know if any more info is needed. Thank you again for your support

Edited by XinYoung
Simplified the code
Link to post
Share on other sites
On 1/10/2019 at 2:16 PM, Subz said:

The array would become a 2d array $aSearchItems

$aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("CopyCourses").Usedrange.Columns("A:B"))

You can then obtain that information in a loop as follows:

For $i = 0 To Ubound($aSearchItems) - 1
    ;~ Column 0
    ConsoleWrite($aSearchItems[$i][0] & @CRLF)
    ;~ Column 1
    ConsoleWrite($aSearchItems[$i][1] & @CRLF)
    $aSearchResult[$i] = CopyCourseNow($aSearchItems[$i][0], $aSearchItems[$i][1])
Next

For CopyCourseNow function use something like:

;~ The course search was successful. COPY the course now.
Func CopyCourseNow($_sourceCourseId, $_destinationCourseId)
Local $sResult
;~  Navigate to the course copy page.
    _IENavigate($oIE, $urlBBCourseCopy)

;~  Copy the SOURCE course ID from the Excel sheet

;~ Paste whats copied from column A into the Source Course ID text box
    Local $oForm = _IEGetObjByName($oIE, "selectCourse")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "sourceCourseId")
    _IEFormElementSetValue($oSearchString, $_sourceCourseId)

;~ Paste whats copied from column B into the Destination Course ID text box  ?!?!?!?!
    Local $oForm = _IEGetObjByName($oIE, "selectCourse")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "destinationCourseId")
    _IEFormElementSetValue($oSearchString, $_destinationCourseId)
;~ .... Do stuff
Return $sResult
EndFunc   ;==>CopyCourseNow

You may need to post all your code, as the code is disjointed.

 

Theres a couple functions between OpenExcelForCopy() and CopyCourseNow(). If I do this, they get skipped 

 

Link to post
Share on other sites

OK I cleaned up the code a bit more so its easier to read

;~  This is where I am trying to copy the used range in column A and column B from Excel
Func OpenExcelForCopy()
    Global $aBBTableData
    Global $oExcel = _Excel_Open()
    Global $oWorkbook = _Excel_BookOpen($oExcel, $ChosenFileName, Default, True, True)
    $oExcel.Sheets("CopyCourses").Activate
;~ Get all used cells in columns A and B <---- it this right?
    Global $aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("CopyCourses").Usedrange.Columns("A:B"))
;~ Duplicate the $aSearchItems Array
    Global $aSearchResult = $aSearchItems
;~ Loop through the array starting at 0 until the end of the array which is (Ubound($aSearchItems) - 1)
    For $i = 0 To UBound($aSearchItems) - 1
    $aSearchResult[$i] = SearchCourseForCopy($aSearchItems[$i])
    Next
    For $i = 0 To UBound($aSearchItems) - 1
;~ Column 0
        ConsoleWrite($aSearchItems[$i][0] & @CRLF)
;~ Column 1
        ConsoleWrite($aSearchItems[$i][1] & @CRLF)
        $aSearchResult[$i] = SearchCourseForCopy($aSearchItems[$i][0], $aSearchItems[$i][1])
;~ Were going to put the results of our work into Column C
    Next
    _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")
    Finished()
EndFunc   ;==>OpenExcelForCopy

;~  Then I go to IE and paste whats copied from column A into a search tool
Func SearchCourseForCopy($aSearchItems[$i][0], $aSearchItems[$i][1])
    Local $oForm = _IEGetObjByName($oIE, "courseManagerFormSearch")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "courseInfoSearchText")
    _IEFormElementSetValue($oSearchString, $_sSearchResult)
    _IEFormSubmit($oForm)
    _IELoadWait($oIE)
;~ Lets see what we got from the search
    Local $oBBTable = _IETableGetCollection($oIE, 2)
    $aBBTableData = _IETableWriteToArray($oBBTable)
    Return CopyCourseBegin()
EndFunc   ;==>SearchCourseForCopy

;~ If the search in IE was bad, move onto the next row in Excel. If it was good, do the CopyCourseNow function
Func CopyCourseBegin()
    Local $sResult
    $iSearchIndex = _ArraySearch($aBBTableData, "Course ID", 0, 0, 0, 1, 1, 0)
    If $iSearchIndex = -1 Then
        $sResult = "Source Not Found"
        _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")
    Else
        For $i = 0 To UBound($aSearchItems) - 1
            $aSearchResult[$i] = CopyCourseNow($aSearchItems[$i])
        Next
        $sResult = "Copied"
        _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")
    EndIf
    Return $sResult
EndFunc   ;==>CopyCourseBegin

;~ The search was good
Func CopyCourseNow($_sourceCourseId, $_destinationCourseId)
    Local $sResult
;~ Paste whats copied from column A into the SOURCE text box
    Local $oForm = _IEGetObjByName($oIE, "selectCourse")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "sourceCourseId")
    _IEFormElementSetValue($oSearchString, $_sourceCourseId)
;~ Paste whats copied from column B into the DESTINATION text box  ?!?!?!?! <--- Need Help Here. Not working. Cant paste the data from column B
    Local $oForm = _IEGetObjByName($oIE, "selectCourse")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "destinationCourseId")
    _IEFormElementSetValue($oSearchString, $_destinationCourseId)
    
;~ .... Do stuff

    Return $sResult

EndFunc   ;==>CopyCourseNow

 

 

 

 

 

Link to post
Share on other sites

 

On 1/10/2019 at 2:16 PM, Subz said:

You can then obtain that information in a loop as follows:

For $i = 0 To Ubound($aSearchItems) - 1
    ;~ Column 0
    ConsoleWrite($aSearchItems[$i][0] & @CRLF)
    ;~ Column 1
    ConsoleWrite($aSearchItems[$i][1] & @CRLF)
    $aSearchResult[$i] = CopyCourseNow($aSearchItems[$i][0], $aSearchItems[$i][1])
Next

 

I'm trying to implement this. I think I can get rid of the SearchCourseForCopy and CopyCourseBegin functions but I'm getting this

==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:

Edited by XinYoung
Link to post
Share on other sites

See where the issue is ($aSearchResult, is a duplicate of $aSearchItems so you would also need to use $aSearchItems[$i][0]), however you could write it as follows:
nb: Its best practice not to declare variables in the Global scope within a function

Global $aBBTableData, $oExcel, $oWorkbook, $aSearchItems, $aSearchResult[0]
Func OpenExcelForCopy()
    $oExcel = _Excel_Open()
    $oWorkbook = _Excel_BookOpen($oExcel, $ChosenFileName, Default, True, True)
    $oExcel.Sheets("CopyCourses").Activate
;~ Get all used cells in columns A and B <---- it this right?
    $aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("CopyCourses").Usedrange.Columns("A:B"))
;~ Create the $aSearchResult array
    ReDim $aSearchResult[UBound($aSearchItems)]
    
;~ Loop through the array starting at 0 until the end of the array which is (Ubound($aSearchItems) - 1)
    For $i = 0 To UBound($aSearchItems) - 1
;~ Column 0
        ConsoleWrite($aSearchItems[$i][0] & @CRLF)
;~ Column 1
        ConsoleWrite($aSearchItems[$i][1] & @CRLF)
        $aSearchResult[$i] = CopyCourseNow($aSearchItems[$i][0], $aSearchItems[$i][1])
;~ Were going to put the results of our work into Column C
    Next
    _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")
    Finished()
EndFunc   ;==>OpenExcelForCopy

 

Edited by Subz
Link to post
Share on other sites

Works fine for me, see test below:

#include <Array.au3>
#include <Excel.au3>
Global $aBBTableData, $oExcel, $oWorkbook, $aSearchItems, $aSearchResult[0]
$ChosenFileName = @ScriptDir & "\Test.xlsx"
OpenExcelForCopy()
Func OpenExcelForCopy()
    $oExcel = _Excel_Open()
    $oWorkbook = _Excel_BookOpen($oExcel, $ChosenFileName, Default, True, True)
    $oExcel.Sheets("CopyCourses").Activate
;~ Get all used cells in columns A and B <---- it this right?
    $aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("CopyCourses").Usedrange.Columns("A:B"))
;~ Create the $aSearchResult array
    ReDim $aSearchResult[UBound($aSearchItems)]

;~ Loop through the array starting at 0 until the end of the array which is (Ubound($aSearchItems) - 1)
    For $i = 0 To UBound($aSearchItems) - 1
;~ Column 0
        ConsoleWrite($aSearchItems[$i][0] & @CRLF)
;~ Column 1
        ConsoleWrite($aSearchItems[$i][1] & @CRLF)
        $aSearchResult[$i] = CopyCourseNow($i, $aSearchItems[$i][0], $aSearchItems[$i][1])
;~ Were going to put the results of our work into Column C
    Next
    _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")
;~     Finished()
EndFunc   ;==>OpenExcelForCopy

Func CopyCourseNow($iIndex, $_sSearch, $sSearch1)
    Return Mod($iIndex, 2) = 0 ? "Odd" : "Even"
EndFunc

 

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
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Sachs
      I have a few questions:
      BACKGROUND
      I am using Oracle Application Testing Suite's OpenScript 13 (Eclipse IDE), Oracle Java 6, and AutoIt.
      My goal is to access a given Internet Explorer browser window (`$ieTitle`), send the keystroke of "shift-control-s" for "Save As" functionality to be invoked, so that I can download a (PDF) file to a given location ($fileName).
      The code within the script `DownloadPdfFile.au3` is
      $ieTitle = $CmdLine[1] $ieControl = "AVL_AVView31" $fileName = $CmdLine[2] ControlFocus($ieTitle, "", $ieControl) ControlSend($ieTitle, "", $ieControl, "+^s") ; Save as dialog $winTitle = "Save As" ; wait for Save As window WinWait($winTitle) ; activate Save As window If Not WinActive($winTitle) Then WinActivate($winTitle) ControlFocus($winTitle,"","Edit1") ControlSetText($winTitle,"","Edit1",$fileName) Sleep(2000) ControlClick($winTitle,"","Button3") Exit 0 So I compiled it with SciTE-Lite (32-bit Version 4.4.6 , creating `DownloadPdfFile.exe`, and so within my Java code, I have
      String command = autoItExePath + " " + scriptPath + " \"" + winTitle + "\" " + directoryPath.toFile().toString() + "\\Form9Report" + sdf_ddmmmyyyy.format(new Date()) + ".pdf"; try { Process process = Runtime.getRuntime().exec(command); process.waitFor(); } catch (Exception e) { logger.error("Exception " + e.getMessage(), e); } The output would be like `C:\Program Files (x86)\AutoIt3\AutoIt3.exe C:\...\AutoItScripts\DownloadPdfFile.au3 "https://****.com/****.exe?temp_id=**** - Internet Explorer" C:\...\Report05Apr2022.pdf"` which does run without the $cmdLine successfully. 
      When executed by Java, I see in the taskbar an icon, which I right-click has "[Check] Script Paused" and "Exit".
      Questions:
      (1) How do I unpause the script?
      (2) How do I avoid having the script paused?
      Any help is appreciated.
    • By goku200
      I have an Autoit script that lists files from a folder into an array list. Is there a way to separate the filenames by an underscore and include the id, version, name and date into separate columns in Excel.
      Example of filename:
      12345_v1.0_TEST Name [12345]_01.01.2022.html
      12345 would be in one column
      v1.0 would be in another column
      TEST Name [12345] would be in another column
      01.01.2022 would be in another column
      .html would be in another column
      Note: filenames always change each day.
      Here is my code that lists the files into column C and then writes the column Headers into Column D, E, F, G. Just need some help with separating them into columns by the _ delimiter
       
    • By SkysLastChance
      I am having a issue of whenever I try to _Excel_RangeWrite a formula that references another workbook I am getting an error @4 and @extended -2147352567

      #include <Excel.au3> #include <MsgBoxConstants.au3> Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookNew($oExcel) _Excel_RangeWrite($oWorkBook,Default,"=IF(D2=D1,"",VLOOKUP(D2,'J:\Temporary Files\FolderName\FileName.xlsx'!$A:$B,2,0))","W2",False) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "String successfully written.") I am not a 100% sure, but I am guessing this is because I have "" and '' in the formula. However, I am not sure how this can best be resolved. 
       
       
    • By SkysLastChance
      I am trying to autofill a range. I am getting stuck and I don't understand what I am doing wrong. 
      My goal is to auto fill some formulas that are next to a pivot table in columns A-C. 
      _Excel_RangeWrite($oNewWorkBook,Default,"2000","D3") _Excel_RangeWrite($oNewWorkBook,Default,"=(B3-D3)","E3") _Excel_RangeWrite($oNewWorkBook,Default,"100","F3") _Excel_RangeWrite($oNewWorkBook,Default,"=(C3-F3)","G3") $oNewWorkbook.ActiveSheet.Range("D3:G3").Select With $oNewWorkbook .Selection.AutoFill(.Range("D3:G77"),0) EndWith The data is not auto filling. 
      Hoping someone can point me in the right direction. 
    • By Steviep
      Hi all,
      I've been using the following code for many years for the sole purpose of tracking my app usage via Google Analytics:
      ;GOOGLE ANALYTICS $AppStatsName = @ScriptName $GA = _IECreate("https://mywebserver/apps/stats/" & $AppStatsName & ".html", 0, 0, 0, 0) The app simply calls a blank .html page on my webserver which only contains the GA tracking code.
      The $iVisible parameter is set to "0" in my case, which means the IE browser is invisible to the user.
      I noticed in Windows 11, the URL is called in the Edge browser and is not invisible.
      So, I wonder if anyone out there has any suggestions on calling a URL invisibly on any operating system?
      Thanks!
×
×
  • Create New...