Network Automation Software We are currently migrating this forum
over to our HelpSystems domain. Please
post all new threads in our new
HelpSystems Community Portal.
Post to the HelpSystems Forum
You are not currently logged on. You must be logged on in order to post. Log on
Or Create a new account
AutoMate Discussion
Decrease font size
Increase font size
Topic Title: Excel - Obtaining Row and Column # by Searching Excel for Predefined Text
Topic Summary: Excel - Easy Question
Created On: 06/18/2007 07:12 PM
Status: Read Only
Linear : Threading : Single : Branch
Search Topic Search Topic
Topic Tools Topic Tools
View similar topics View similar topics
View topic in raw text format. Print this topic.
 06/18/2007 07:12 PM
User is offline View Users Profile Print this message

Author Icon
audir8
Student (5-19 posts)

Posts: 10
Joined: 05/19/2007

My goal is to obtain row and column location numbers (Ex: Row: 2 & Column 4) from an Excel worksheet based on a text search using Automate.

 1.  I need a method to search for the location of a predefined number in an excel spreadsheet. Once Automate finds this number, the cell containing this number needs to be selected.

2.  Then, row and column # of the cell containing the value will be returned to  separate variables one to store the row value and one to store the column value.

 

I am grateful for your expedient help.



 Category Survey
 06/19/2007 05:54 AM
User is offline View Users Profile Print this message

Author Icon
Bart van Lagen
Supreme Guru (1000+)

Posts: 1400
Joined: 04/25/2005

I once developed a task for this (see attached code).

Important are these variables in the task:
strStringToSearch (string to search for)
strEXCELFile (name and path of the Excel document)
intWorksheetNumber (number of the worksheet of the Excel workbook to scan)

This task populates a string: strResults, containing all the locations of the search item. For example: A,1;B,30

Have luck.

 Category Survey



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)


-------------------------
Bart van Lagen
ICT Department.
St Jansdal Hospital
Harderwijk, Netherlands
Automate 7.1.1.0 / BPA 7.1.1.0
Windows 2003SP2SRV
 06/30/2007 05:33 PM
User is offline View Users Profile Print this message

Author Icon
audir8
Student (5-19 posts)

Posts: 10
Joined: 05/19/2007

Works like a charm, for Microsoft Excel 2003.

Microsoft Excel 2000 comes back with a reference error. Is the path different for 2000?

'#Reference {00020813-0000-0000-C000-000000000046}#1.4#0#C:\Program Files\Microsoft Office\Office10\EXCEL.EXE#Microsoft Excel 10.0 Object Library

Thanks.



 Category Survey
 07/01/2007 12:25 AM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

For Office 2000 it is version 9.0

'#Reference {00020813-0000-0000-C000-000000000046}#1.3#0#C:\Program Files\Microsoft Office\Office\EXCEL9.OLB#Microsoft Excel 9.0 Object Library

You can specify both references to v10.0 and v9.0 for compatibility. Although it issues a warning in the basic IDE, it does not fail the Automate step

You can also get rid of any references by modifying somewhat the script like this

'Set appExcel = New Excel.Application
Set appExcel = CreateObject("Excel.Application")

'Set firstCell = wshExcel.Cells.Find(what:=strStringToSearch, lookat:=xlWhole, searchdirection:=xlPrevious)
Set firstCell = wshExcel.Cells.Find(what:=strStringToSearch, lookat:=1, searchdirection:=2)

where the commented statements are the ones Bart used with references
while I modified them in order not to invoke the references.

 Category Survey
AutoMate 6 version:
Windows version:


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4


Edited: 07/01/2007 at 12:54 AM by JeanDelfosse
 07/01/2007 07:05 PM
User is offline View Users Profile Print this message

Author Icon
audir8
Student (5-19 posts)

Posts: 10
Joined: 05/19/2007

One one Win XP Machine, the script works great. On another it does not. The other machine has only the strict microsoft office (word and excel) installed. I wonder when you install Microsoft office, if there is a package that includes scripting support that must be installed?

Automate stops on the Script step with a status message at the bottom. "Starting script" and then just hangs.




 Category Survey



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)


Edited: 07/01/2007 at 08:39 PM by audir8
 07/01/2007 11:23 PM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

You can easly check if Office includes the scripting support : in this case there is an entry in the registry
HKEY_CLASSES_ROOT\Excel.Application
that shows the CurVer and CLSID

In order to determine where the script hangs, you could execute it directly from the basic IDE (green triangle Run button, once you have added some features (variables values and msgboxes) as I did below

In case Excel scripting support is not installed, I think there are options to install it later in the Office install package. But it may depend on the very nature of that package.

 Category Survey
AutoMate 6 version:
Windows version:



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4


Edited: 07/01/2007 at 11:28 PM by JeanDelfosse
 07/02/2007 07:15 AM
User is offline View Users Profile Print this message

Author Icon
Bart van Lagen
Supreme Guru (1000+)

Posts: 1400
Joined: 04/25/2005

Thx Jean for supporting this item. In my enthousiasm I forgot not everybody is running 2003!

 Category Survey


-------------------------
Bart van Lagen
ICT Department.
St Jansdal Hospital
Harderwijk, Netherlands
Automate 7.1.1.0 / BPA 7.1.1.0
Windows 2003SP2SRV
 07/02/2007 11:14 AM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

Hi Bart!
that's a pleasure.
I appreciated your solution, and was pleased to elaborate on it.

 Category Survey
AutoMate 6 version:
Windows version:


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 07/11/2007 05:42 PM
User is offline View Users Profile Print this message

Author Icon
audir8
Student (5-19 posts)

Posts: 10
Joined: 05/19/2007

Great. Now within the VB Script the location of the file that I need to browse is stored in Automate as a variable strEXCELFile and it is initialized to:

C:\PJM\%Format(DateAdd('d',1, Now()),'yyyymmdd')%-da.csv

How do I convert from a static location
strEXCELFile = "c:\temp\MyExcel.xls"
to a dynamic filename location
strEXCELFile = "C:\PJM\%Format(DateAdd('d',1, Now()),'yyyymmdd')%-da.csv"
in VB Script without getting an error.

By the way, this works outside of VB script, however, when I put it in the script, It does not recognize the % %.

Thanks.



 Category Survey
 07/11/2007 11:35 PM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

Audir8,
Inside a vb script, the % signs cause errors since they are Automate expressions delimiters.

strEXCELFile = "C:\PJM\" & Format(DateAdd("d",1, Now()),"yyyymmdd") & " -da.csv"

will do it.

Notice I also replaced the single quotes (accepted by Automate expressions but not by vb script) by double quotes.

But you could as well declare strEXCELFile outside of the basic script as an Automate variable, and just use it inside the basic script. But doing so would prevent you from testing the basic script using the IDE run button.

 Category Survey
AutoMate 6 version:
Windows version:


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 10/20/2012 01:03 PM
User is offline View Users Profile Print this message

Author Icon
AndrewMorgan
Student (5-19 posts)

Posts: 9
Joined: 06/17/2010

Can this work in Automate 9?

 Category Survey
 10/20/2012 01:03 PM
User is offline View Users Profile Print this message

Author Icon
AndrewMorgan
Student (5-19 posts)

Posts: 9
Joined: 06/17/2010

Can this work in Automate 9?

 Category Survey
 10/20/2012 11:40 PM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

Andrew,
I don't know exactly what you mean with "this", but since it is basic scripting, there is no reason why it should not work as well in AM9.

 Category Survey
AutoMate 6 version:
Windows version:


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 10/25/2012 06:49 AM
User is offline View Users Profile Print this message

Author Icon
AndrewMorgan
Student (5-19 posts)

Posts: 9
Joined: 06/17/2010

Thanks!

 Category Survey
 10/25/2012 09:22 AM
User is offline View Users Profile Print this message

Author Icon
AndrewMorgan
Student (5-19 posts)

Posts: 9
Joined: 06/17/2010

I tried running the script, but ran into the following error: [Main](Step 5) Script error: "Expecting a valid data type (eg. Integer)." at script line 6

Am I doing something wrong?

 Category Survey
 10/25/2012 10:57 AM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

Andrew,
it should be nice seeing your exact steps.
Could you post them ? (use Attach code feature in the "reply" dialog)

 Category Survey
AutoMate 6 version:
Windows version:


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 10/25/2012 01:14 PM
User is offline View Users Profile Print this message

Author Icon
AndrewMorgan
Student (5-19 posts)

Posts: 9
Joined: 06/17/2010

John
C:\Users\Intern3\Dropbox\Marketing\LinkedIn\Pending Files\Atlanta.xlsx



Sub Main

Dim firstCell As Object, nextCell As Object
Dim strFirstloc As String

Dim appExcel As Excel.Application
Dim wbkExcel As Excel.Workbook
Dim wshExcel As Excel.Worksheet

Set appExcel = CreateObject("Excel.Application")
appExcel.Visible = False
Set wbkExcel = appExcel.Workbooks.Open( strEXCELFile)
Set wshExcel = wbkExcel.Worksheets( intWorksheetNumber)

wshExcel.Application.Range( "B2").Select
strCurLoc = wshExcel.Application.ActiveCell.Address
strColRef = Mid( strCurLoc, 2, 1)
strRowRef = Mid( strCurLoc, 4, 1)

Set firstCell = wshExcel.Cells.Find(what:=strStringToSearch, lookat:=1, searchdirection:=2)
If firstCell Is Nothing Then
strResults = ""
Else
Set nextCell = wshExcel.Cells.FindNext(after:=wshExcel.Range(firstCell.Address))
strResults = Split( nextCell.Address, "$")(1) & "," & Split( nextCell.Address, "$")(2)
Do While firstCell.Address <> nextCell.Address
Set nextCell = wshExcel.Cells.FindNext(after:=wshExcel.Range(nextCell.Address))
strResults = strResults & ";" & Split( nextCell.Address, "$")(1) & "," & Split( nextCell.Address, "$")(2)
Loop
End If

appExcel.ActiveWorkbook.Close
appExcel.Quit

End Sub



 Category Survey
 10/25/2012 01:15 PM
User is offline View Users Profile Print this message

Author Icon
AndrewMorgan
Student (5-19 posts)

Posts: 9
Joined: 06/17/2010

John
C:\Users\Intern3\Dropbox\Marketing\LinkedIn\Pending Files\Atlanta.xlsx



Sub Main

Dim firstCell As Object, nextCell As Object
Dim strFirstloc As String

Dim appExcel As Excel.Application
Dim wbkExcel As Excel.Workbook
Dim wshExcel As Excel.Worksheet

Set appExcel = CreateObject("Excel.Application")
appExcel.Visible = False
Set wbkExcel = appExcel.Workbooks.Open( strEXCELFile)
Set wshExcel = wbkExcel.Worksheets( intWorksheetNumber)

wshExcel.Application.Range( "B2").Select
strCurLoc = wshExcel.Application.ActiveCell.Address
strColRef = Mid( strCurLoc, 2, 1)
strRowRef = Mid( strCurLoc, 4, 1)

Set firstCell = wshExcel.Cells.Find(what:=strStringToSearch, lookat:=1, searchdirection:=2)
If firstCell Is Nothing Then
strResults = ""
Else
Set nextCell = wshExcel.Cells.FindNext(after:=wshExcel.Range(firstCell.Address))
strResults = Split( nextCell.Address, "$")(1) & "," & Split( nextCell.Address, "$")(2)
Do While firstCell.Address <> nextCell.Address
Set nextCell = wshExcel.Cells.FindNext(after:=wshExcel.Range(nextCell.Address))
strResults = strResults & ";" & Split( nextCell.Address, "$")(1) & "," & Split( nextCell.Address, "$")(2)
Loop
End If

appExcel.ActiveWorkbook.Close
appExcel.Quit

End Sub



 Category Survey
 10/25/2012 02:42 PM
User is offline View Users Profile Print this message

Author Icon
AndrewMorgan
Student (5-19 posts)

Posts: 9
Joined: 06/17/2010

I think I may have pasted that wrong...here is the right version in the right format...

 Category Survey



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
Statistics
18258 users are registered to the AutoMate Discussion forum.
There are currently 0 users logged in.
The most users ever online was 5551 on 01/08/2018 at 11:11 AM.
There are currently 2554 guests browsing this forum, which makes a total of 2554 users using this forum.

FuseTalk Enterprise Edition v4.0 - © 1999-2020 FuseTalk Inc. All rights reserved.

Sitemap Network Automation Software Blog