Jump to content

Recommended Posts

Posted

Hi everyone, I have a script that I'm using that when run on a pc creates a folder on a remote share with an Excel file under it. The Excel file contains serial numbers of all software on the computer, I need to write another sctipt that runs through the root folder and in to the subfolders, polls all Excels looking for say a cell that contains Adobe and the cell next to it contains the serial number. Get that information and write it out to a single Excel called adobe.xls. I am not an expert in scripting but think that it might be able to be done using an array? Any pointers, much appriciated.

Posted

The UDF will create a nice recursive list of Excel files for you.

Then loop through the files in the array and search for the info you need.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Posted

Thanks for the pointer..

I have started off with the code below but it seems to always return a value of 0 even though the cell has data in it.

#include <RecFileListToArray.au3>
#include <Excel.au3>
$sDir = @ScriptDir
;List all xls files to array.
$aFiles = _RecFileListToArray($sDir, "*.xls", 1, 1, 0, 2)
For $i = 1 To $aFiles[0]
$sCellValue = _ExcelReadCell($aFiles[$i], 1, 2)
MsgBox(0, "", "Cell Value is: " & @CRLF & $sCellValue, 2)
Next
  • Moderators
Posted

Remo1075,

Go and read the Help file for the _Excel* functions. :D

In this case it seems that you need to use _ExcelBookOpen first and then use the return value from that in _ExcelReadCell. And do not forget to use _ExcelBookClose either once you have the data from that file! :oops:

M23

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see my UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Posted

Hi, thanks for your reply. I still get 0 return value in the msgbox when I add _ExcelBookOpen and _ExcelBookClose, because I'm dealing with possibly hundereds of excel sheets idealy I dont want the script to physically open an instance of Excel everytime which is what _ExcelBookOpen and _ExcelBookClose is doing. I am still facing an issue of not being able to read a cell value from an array of loaded excel sheets.

Thanks for posting..

[/i]
[i][i]#include <RecFileListToArray.au3>
#include <Excel.au3>[/i][/i]
[i][i]$sDir = @ScriptDir[/i][/i]
[i][i];List all xls files to array.
$aFiles = _RecFileListToArray($sDir, "*.xls", 1, 1, 0, 2)[/i][/i]
[i][i]For $i = 1 To $aFiles[0][/i][/i]
[i][i]_ExcelBookOpen($aFiles[$i])
Sleep(200)
$sCellValue = _ExcelReadCell($aFiles[$i], 2, 1)
_ExcelBookClose($aFiles[$i])[/i][/i]
[i][i]MsgBox(0, "", "Cell Value is: " & @CRLF & $sCellValue, 2)[/i][/i]
[i][i]Next[/i][/i]

[i]
Posted

Try this:

#include <RecFileListToArray.au3>
#include <Excel.au3>
$sDir = @ScriptDir
;List all xls files to array.
$aFiles = _RecFileListToArray($sDir, "*.xls", 1, 1, 0, 2)
For $i = 1 To $aFiles[0]
    $oExcel = _ExcelBookOpen($aFiles[$i])
    Sleep(200)
    $sCellValue = _ExcelReadCell($oExcel, 2, 1)
    _ExcelBookClose($oExcel)
    MsgBox(0, "", "Cell Value is: " & @CRLF & $sCellValue, 2)
Next

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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.
×
×
  • Create New...