Sign in to follow this  
Followers 0
ThomasQ

Best way to use same script on multiple XLS files

17 posts in this topic

Hi Everyone!

Recently I made a script, which copies data from Excel Cells into webbased forms. Since I have to input a lot of Excels files, I was wondering what the best way is to automaticly use the same script for different Excel files..

The obvious way is to use standard names ( For example 1.xls to 10.xls) and then just copy paste the sript and change the file names to open with the _ExcelBookOpen command, but there must be some better way to use the same script, maybe on all the XLS files in a specific folder?

Help a n00b out!!

Thanks in advance!

Share this post


Link to post
Share on other sites



Your friend will be _FileListToArray >_<

READ about this function to understand my solution:

#Include <File.au3>
$XLS_files = _FileListToArray("your folder with excel files", "*.xls", 1)
For $i = 1 To $XLS_files[0]
    ;do whatever you need to do here for every file
Next

SNMP_UDF ... for SNMPv1 and v2c so far, GetBulk and a new example script

wannabe "Unbeatable" Tic-Tac-Toe

Paper-Scissor-Rock ... try to beat it anyway :)

Share this post


Link to post
Share on other sites

Great, many Thanks!!!

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

Triple post

Edited by ThomasQ

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

Quadruple Post

Edited by ThomasQ

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

double post

Edited by ThomasQ

Share this post


Link to post
Share on other sites

Well, I read about it, tried different options, but I'm still having some problems

#include <excel.au3>

#Include <File.au3>

#include <IE.au3>

$oIE = _IECreate ("http://Login.com")

$XLS_files = _FileListToArray("C:\Documents and Settings\Admin\Desktop\Projects\Sheets\Made", "*.xls", 1)

For $i = 1 To $XLS_files[0]

$oExcel =_Excelbookopen ($i)

$val = _ExcelReadCell($oExcel, "D5")

$Vrij = _ExcelReadCell($oExcel, "A11")

ConsoleWrite($val & @CRLF)

$oForm = _IEFormGetObjByName ($oIE, "searchForm")

$oText = _IEFormElementGetObjByName ($oForm, "value")

_IEFormElementSetValue ($oText, $val)

Etc..

Obviously, the script won't open the file. I used to use the $oExcel = _ExcelBookOpen("C:\Documents and Settings\Admin\Desktop\Projects\TestCleanSheet.xls",1,False) command, to open an excel file..

The question I got is; How do I open the xls file, using the array command? I don't want them all to open an once, but one at a time, run the script, close it, and then another.

Many Thanks in advance!

Share this post


Link to post
Share on other sites

_FileListToArray returns only the file names. You need to concatenate the full path to the filename string.

Also your "for" loop is completely wrong. Read more in the help file and check out the examples.

Share this post


Link to post
Share on other sites

Ok, read the help file some more, but it didnt give me a definive awnser.

For opening the XLS files, should it be something like $oExcel =_Excelbookopen ("C:\Documents and Settings\Admin\Desktop\Projects\Sheets\Made\$i)>? so $i is the variable, and will change every time after the Next command?

and, for the looping bit, For $i = 1 To $XLS_files[0], it's wrong? I do understand the looping bit, but how do I specify when to stop ($XLS_files(0))? Doen't it count down till there are no more XLS files left with this command? The number of files in the dir will be different everytime, so i can't specify the number of steps on forehand.

Thanks in advance!

Share this post


Link to post
Share on other sites

$oIE = _IECreate("http://Login.com")
$sFolder = "C:\Documents and Settings\Admin\Desktop\Projects\Sheets\Made"
$XLS_files = _FileListToArray($sFolder, "*.xls", 1)
For $i = 1 To $XLS_files[0]
    $oExcel = _ExcelBookOpen($sFolder & "\" & $XLS_files[$i])
    $val = _ExcelReadCell($oExcel, "D5")
    $Vrij = _ExcelReadCell($oExcel, "A11")
    ConsoleWrite($val & @CRLF)
    $oForm = _IEFormGetObjByName($oIE, "searchForm")
    $oText = _IEFormElementGetObjByName($oForm, "value")
    _IEFormElementSetValue($oText, $val)
    ;Etc..
Next

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

Thanks Pickaxe!

I just implemnted your piece of code in my script. It doesn't work completely though. It doesn't open or acces Excel. I put the $oIE = _IECreate("http://Login.com") als in the For.. To Loop, and it does seem to open as many times as there are excel files in the folder!

$sFolder = "C:\Documents and Settings\Admin\Desktop\Projects\Sheets\Made"

$XLS_files = _FileListToArray($sFolder, "*.xls", 1)

For $i = 1 To $XLS_files[0]

$oExcel = _ExcelBookOpen($sFolder & "\" & $XLS_files[$i])

$oIE = _IECreate("http://Login.com")

$val = _ExcelReadCell($oExcel, "D5")

$Vrij = _ExcelReadCell($oExcel, "A11")

ConsoleWrite($val & @CRLF)

$oForm = _IEFormGetObjByName($oIE, "searchForm")

$oText = _IEFormElementGetObjByName($oForm, "value")

_IEFormElementSetValue($oText, $val)

;Etc..

Next

Anyone any idea?? Many thanks from a big beginner!!

Edited by ThomasQ

Share this post


Link to post
Share on other sites

Does the console show anything for $val. Try without IE functions. This works for me

#Include <Excel.au3>
#Include <File.au3>
;~ $oIE = _IECreate("http://Login.com")
;~ $sFolder = "C:\Documents and Settings\Admin\Desktop\Projects\Sheets\Made"
$sFolder = @DesktopDir
$XLS_files = _FileListToArray($sFolder, "*.xls", 1)
For $i = 1 To $XLS_files[0]
    $oExcel = _ExcelBookOpen($sFolder & "\" & $XLS_files[$i])
    $val = _ExcelReadCell($oExcel, "D5")
    ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $val = ' & $val & @crlf & '>Error code: ' & @error & @crlf) ;### Debug Console
    $Vrij = _ExcelReadCell($oExcel, "A11")
    ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $Vrij = ' & $Vrij & @crlf & '>Error code: ' & @error & @crlf) ;### Debug Console
;~     $oForm = _IEFormGetObjByName($oIE, "searchForm")
;~     $oText = _IEFormElementGetObjByName($oForm, "value")
;~     _IEFormElementSetValue($oText, $val)
;~     ;Etc..
Next

Share this post


Link to post
Share on other sites

Thanks! Tried the new code, but

$Val keeps returning as 0, it should be a 10 digit ID number. It won't open any excel file as well.. Anybody any idea?

Thanks in advance!!

Share this post


Link to post
Share on other sites

i tried pickaxe's code..Its working just the way it should!! Dunno what's the problem!!


[font="Garamond"]Manjish Naik[/font]Engineer, Global Services - QPSHoneywell Automation India LimitedE-mail - Manjish.Naik@honeywell.com

Share this post


Link to post
Share on other sites

#15 ·  Posted (edited)

Nope, still no succes..

It just opens the site, with no Excel in sight..

Also, what exactly do you mean with I shouldn't use _IE functions? I have to use them in the script, so it's not really an option not to include them.. Running this script I get this error:

C:\Documents and Settings\Admin\Desktop\Projects\Scripts\tryout.au3 (8) : ==> Subscript used with non-Array variable.:

For $i = 1 To $XLS_files[0]

For $i = 1 To $XLS_files^ ERROR

#Include <Excel.au3>

#Include <File.au3>

#include <IE.au3>

$oIE = _IECreate("http://www.google.com")

$sFolder = "C:\Documents and Settings\Admin\Desktop\Projects\Sheets\Made"

$sFolder = @DesktopDir

$XLS_files = _FileListToArray($sFolder, "*.xls", 1)

For $i = 1 To $XLS_files[0]

$oExcel = _ExcelBookOpen($sFolder & "\" & $XLS_files[$i])

$val = _ExcelReadCell($oExcel, "D5")

ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $val = ' & $val & @crlf & '>Error code: ' & @error & @crlf) ;### Debug Console

$Vrij = _ExcelReadCell($oExcel, "A11")

ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $Vrij = ' & $Vrij & @crlf & '>Error code: ' & @error & @crlf) ;### Debug Console

Next

Also, if I use the _IEcreate command inside the loop with this code, I get syntax errors, it doesn't open the site as many times as there are XLS files in the folder, like it did with pickaxe's first version.

This is the last step before I can start working with my script, so someone PLeease help a n00b out >_< Many Thanks!!

Edited by ThomasQ

Share this post


Link to post
Share on other sites

Nope, still no succes..

It just opens the site, with no Excel in sight..

Also, what exactly do you mean with I shouldn't use _IE functions? I have to use them in the script, so it's not really an option not to include them.. Running this script I get this error:

C:\Documents and Settings\Admin\Desktop\Projects\Scripts\tryout.au3 (8) : ==> Subscript used with non-Array variable.:

For $i = 1 To $XLS_files[0]

For $i = 1 To $XLS_files^ ERROR

#Include <Excel.au3>

#Include <File.au3>

#include <IE.au3>

$oIE = _IECreate("http://www.google.com")

$sFolder = "C:\Documents and Settings\Admin\Desktop\Projects\Sheets\Made"

$sFolder = @DesktopDir

$XLS_files = _FileListToArray($sFolder, "*.xls", 1)

For $i = 1 To $XLS_files[0]

$oExcel = _ExcelBookOpen($sFolder & "\" & $XLS_files[$i])

$val = _ExcelReadCell($oExcel, "D5")

ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $val = ' & $val & @crlf & '>Error code: ' & @error & @crlf) ;### Debug Console

$Vrij = _ExcelReadCell($oExcel, "A11")

ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $Vrij = ' & $Vrij & @crlf & '>Error code: ' & @error & @crlf) ;### Debug Console

Next

Also, if I use the _IEcreate command inside the loop with this code, I get syntax errors, it doesn't open the site as many times as there are XLS files in the folder, like it did with pickaxe's first version.

This is the last step before I can start working with my script, so someone PLeease help a n00b out >_< Many Thanks!!

Hi, a simple and basic question.. Did u put any excel files on desktop?? Coz if there are no excel files on desktop, it will give an error as you are seeing.. See pickaxe just gave u an example, by puting $folder = @desktopdir.. U can change it to the path where u have stored ur excel files.. Then u won't get any errors..


[font="Garamond"]Manjish Naik[/font]Engineer, Global Services - QPSHoneywell Automation India LimitedE-mail - Manjish.Naik@honeywell.com

Share this post


Link to post
Share on other sites

Also, what exactly do you mean with I shouldn't use _IE functions? I have to use them in the script, so it's not really an option not to include them.. Running this script I get this error:

What I said was "try without IE functions". Since you had excel problems then the easiest troubleshooting approach is to get the excel code working first.

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
Sign in to follow this  
Followers 0