ThomasQ Posted July 27, 2009 Share Posted July 27, 2009 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! Link to comment Share on other sites More sharing options...
enaiman Posted July 27, 2009 Share Posted July 27, 2009 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 :) Link to comment Share on other sites More sharing options...
ThomasQ Posted July 27, 2009 Author Share Posted July 27, 2009 Great, many Thanks!!! Link to comment Share on other sites More sharing options...
ThomasQ Posted August 4, 2009 Author Share Posted August 4, 2009 (edited) Triple post Edited August 4, 2009 by ThomasQ Link to comment Share on other sites More sharing options...
ThomasQ Posted August 4, 2009 Author Share Posted August 4, 2009 (edited) Quadruple Post Edited August 4, 2009 by ThomasQ Link to comment Share on other sites More sharing options...
ThomasQ Posted August 4, 2009 Author Share Posted August 4, 2009 (edited) double post Edited August 4, 2009 by ThomasQ Link to comment Share on other sites More sharing options...
ThomasQ Posted August 4, 2009 Author Share Posted August 4, 2009 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! Link to comment Share on other sites More sharing options...
Juvigy Posted August 4, 2009 Share Posted August 4, 2009 _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. Link to comment Share on other sites More sharing options...
ThomasQ Posted August 4, 2009 Author Share Posted August 4, 2009 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! Link to comment Share on other sites More sharing options...
picaxe Posted August 4, 2009 Share Posted August 4, 2009 $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 Link to comment Share on other sites More sharing options...
ThomasQ Posted August 7, 2009 Author Share Posted August 7, 2009 (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 August 7, 2009 by ThomasQ Link to comment Share on other sites More sharing options...
picaxe Posted August 7, 2009 Share Posted August 7, 2009 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 Link to comment Share on other sites More sharing options...
ThomasQ Posted August 7, 2009 Author Share Posted August 7, 2009 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!! Link to comment Share on other sites More sharing options...
Manjish Posted August 7, 2009 Share Posted August 7, 2009 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 Link to comment Share on other sites More sharing options...
ThomasQ Posted August 7, 2009 Author Share Posted August 7, 2009 (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 August 7, 2009 by ThomasQ Link to comment Share on other sites More sharing options...
Manjish Posted August 7, 2009 Share Posted August 7, 2009 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 Link to comment Share on other sites More sharing options...
picaxe Posted August 7, 2009 Share Posted August 7, 2009 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. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now