Excel and vbs

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Excel and vbs

Logged in as: Guest

arrSession:exec spGetSession 2,2,53851

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Excel and vbs
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1] 2 3 4 next > >>

Message

<< Older Topic Newer Topic >>

Excel and vbs - 11/27/2007 10:54:18 PM

Stefgr

Posts: 24
Score: 0
Joined: 11/27/2007
Status: offline

Anyone can help me ? I am new in vbs but trying :)

I have an excel with 2 colums . A colum we have telephone number , B colum we have some info.

I trying to make a vbs script that search a specific telephone number and keep in memory ( if WScript.Quit(ID) means that ) the info of that telephone.
I have made a script but it doesn't work :

Dim ex
Dim I
DIM ID

Set ex=CreateObject("Excel.Application")
ex.Workbooks.Open "C:\Log.xls"    'Open file
For I = 1 To 2000     <---- i put 1 to 2000 because didn't know how to search only the writable cells it might be 60-100 records is it another way to search automatic colum A so it find like 69 records and search only in this records ?

If ex.ActiveSheet.Range("A" & I).Value = "+30123456" Then      <---here is the telephone number that we want to search from colum A
ID = ex.ActiveSheet.Range("B" & I).Value
End If
Next
ex.Quit
WScript.Quit(ID)

Thank you.

Post #: 1

RE: Excel and vbs - 11/27/2007 11:41:12 PM

morpheus83uk

Posts: 379
Score: 0
Joined: 8/21/2006
Status: offline

Hello,

I have tested your Code and it will fine a line number for you...

I have slightly modified your code so let me know if you think its appropriate:

Unfortunatly I cant remeber how to search until the end of records but I am sure a quick google search would help you with this... If you have any problems then I am sure that you could just post back and we can have a further look into this for you.

Many Thanks

James

(in reply to Stefgr)

Post #: 2

RE: Excel and vbs - 11/27/2007 11:58:20 PM

Stefgr

Posts: 24
Score: 0
Joined: 11/27/2007
Status: offline

ex.Visible = True <-- will open excel so i remove it dont want to open excel.

Is it possible better to take the info and create a .txt file that it has the info of the specific telephone ?

(in reply to Stefgr)

Post #: 3

RE: Excel and vbs - 11/28/2007 3:33:55 AM

morpheus83uk

Posts: 379
Score: 0
Joined: 8/21/2006
Status: offline

Hello,

Thats correct either that or you can just set it to FALSE which will do teh same thing.

Quote:

Is it possible better to take the info and create a .txt file that it has the info of the specific telephone ?

Yes you can do indeed...

The FileSystemObject should be able to help you out... if you have a look at the OpenTextFile Menthod it should be fine.

Hope this helps

James

(in reply to Stefgr)

Post #: 4

RE: Excel and vbs - 11/28/2007 5:16:05 AM

Stefgr

Posts: 24
Score: 0
Joined: 11/27/2007
Status: offline

Thank i made this but :

Dim ex
Dim I
DIM ID
Set ex = CreateObject("Excel.Application")
ex.Workbooks.Open "C:\Log.xls"    'Open file
For I = 1 To 2000    ' <---- i put 1 to 2000 because didn't know how to search only the writable cells it might be 60-100 records is it another way to search automatic colum A so it find like 69 records and search only in this records ?
ex.Visible = False
If ex.ActiveSheet.Range("A" & I).Value = "+30123456" Then   '   <---here is the telephone number that we want to search from colum A
ID = ex.ActiveSheet.Range("B" & I).Value
ID = instance.SaveTextFile(test.txt, unformatted)
End If
Next
ex.Quit
WScript.Quit(ID)

can you help make it?
Thanks

(in reply to morpheus83uk)

Post #: 5

RE: Excel and vbs - 11/28/2007 7:47:40 AM

morpheus83uk

Posts: 379
Score: 0
Joined: 8/21/2006
Status: offline

hello,

I hope this gives you a helping hand:

Basically what it will do is open up the text file for you or create it if its not there and set it for appening writes the information in there and then it closes it at the end. I will leave you with it to have a play with.

Many Thanks

James

(in reply to Stefgr)

Post #: 6

RE: Excel and vbs - 11/29/2007 12:37:58 AM

Stefgr

Posts: 24
Score: 0
Joined: 11/27/2007
Status: offline

Ok i made this :

Set ObjFSO = CreateObject("Scripting.FileSystemObject")
Const ForWriting = 2
Set objFile = objFSO.OpenTextFile("c:\test.txt", ForWriting)
Dim ex
Dim I
DIM ID
Set ex = CreateObject("Excel.Application")
ex.Workbooks.Open "C:\Log.xls"    'Open file
For I = 1 To 2000    ' <---- i put 1 to 2000 because didn't know how to search only the writable cells it might be 60-100 records is it another way to search automatic colum A so it find like 69 records and search only in this records ?
If ex.ActiveSheet.Range("A" & I).Value = "2" Then   '   <---here is the telephone number that we want to search from colum A
ID = ex.ActiveSheet.Range("B" & I).Value
objFile.Write ID
objFile.Close
End If
Next
TxtFile.Close
ex.Quit
WScript.Quit(ID)

Is working ok get the info and write it into text.But i have some problems.The excel hang up.it doesn't close it so when i run it with different phones i get the result in text but i have many excel processes.And i got errors when i kill the processes like :

- Object required 'TxtFile'
- The remote server machine does not exist or is unavailable :'ActiveSheet.Range(...).Value'

** Test.txt must exist otherways there is an error
morpheus any solution ?

< Message edited by Stefgr -- 11/29/2007 12:42:38 AM >

(in reply to morpheus83uk)

Post #: 7

RE: Excel and vbs - 11/29/2007 1:39:04 AM

morpheus83uk

Posts: 379
Score: 0
Joined: 8/21/2006
Status: offline

Hello,

Try this:

txtfile.close was erroring because it was not set anywhere in your code.

In regards to the test.txt error...

Set objFile = objFSO.OpenTextFile("c:\test.txt", ForWriting)

would need to have been changed to

Set objFile = objFSO.OpenTextFile("c:\test.txt", ForWriting, True)

Which basically means that if the text file is not there create it...

I hope this helps

Many Thanks

James

(in reply to Stefgr)

Post #: 8

RE: Excel and vbs - 11/29/2007 7:37:52 AM

ziminski

Posts: 79
Score: 2
Joined: 1/8/2006
Status: offline

I use the following to kill excel: (yes it is a sledge hammer, but works)
KillExcel()
'##########################
Function KillExcel
strComputer = "."
Set objWMIService = GetObject("winmgmts:" & _
         "{impersonationLevel=Impersonate}!\\" _
              & strComputer & "\root\cimv2")
Set objWMIService = GetObject("winmgmts:" _
   & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colProcessList = objWMIService.ExecQuery _
   ("Select * from Win32_Process Where Name = 'EXCEL.EXE'")
For Each objProcess in colProcessList
IF objProcess.Name = "EXCEL.EXE" Then
   objProcess.Terminate()
'Wscript.Echo oblProcess.Name &" killed"
End If
Next
End Function
'##########################

(in reply to morpheus83uk)

Post #: 9

RE: Excel and vbs - 11/29/2007 8:08:21 AM

ziminski

Posts: 79
Score: 2
Joined: 1/8/2006
Status: offline

for used range you can use:
With ex.ActiveSheet
For I = 1 To .UsedRange.Rows.Count
....
....
....
...
end with

< Message edited by ziminski -- 11/29/2007 8:09:36 AM >

(in reply to ziminski)

Post #: 10

RE: Excel and vbs - 11/30/2007 12:29:25 AM

Stefgr

Posts: 24
Score: 0
Joined: 11/27/2007
Status: offline

So puting these together we have :

Set ObjFSO = CreateObject("Scripting.FileSystemObject")
Const ForWriting = 2
Set objFile = objFSO.OpenTextFile("c:\test.txt", ForWriting, True)
Dim ex
Dim I
DIM ID
Set ex = CreateObject("Excel.Application")
ex.Workbooks.Open "C:\Log.xls"    'Open file
For I = 1 To 2000    ' <---- i put 1 to 2000 because didn't know how to search only the writable cells it might be 60-100 records is it another way to search automatic colum A so it find like 69 records and search only in this records ?
If ex.ActiveSheet.Range("A" & I).Value = "2" Then   '   <---here is the telephone number that we want to search from colum A
ID = ex.ActiveSheet.Range("B" & I).Value
objfile.WriteLine ID
End If
Next
objfile.Close
ex.Quit
Function KillExcel
strComputer = "."
Set objWMIService = GetObject("winmgmts:" & _
        "{impersonationLevel=Impersonate}!\\" _
             & strComputer & "\root\cimv2")
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colProcessList = objWMIService.ExecQuery _
("Select * from Win32_Process Where Name = 'EXCEL.EXE'")
For Each objProcess in colProcessList
IF objProcess.Name = "EXCEL.EXE" Then
objProcess.Terminate()
'Wscript.Echo oblProcess.Name &" killed"
End If
Next
End Function
WScript.Quit

End its working now how i can put the ziminski code ..For I = 1 To .UsedRange.Rows.Count(rows.count is for rows ? i want colums so it will be colums.count ?) in the code. I tried but failed.

(in reply to ziminski)

Post #: 11

RE: Excel and vbs - 11/30/2007 2:00:00 AM

hamboy

Posts: 94
Score: 6
Joined: 7/11/2005
Status: offline

you have to specify the active sheet.

ActiveSheet.UsedRange.Columns.Count

(in reply to Stefgr)

Post #: 12

RE: Excel and vbs - 12/6/2007 8:31:36 PM

Stefgr

Posts: 24
Score: 0
Joined: 11/27/2007
Status: offline

when i put this :

Set ObjFSO = CreateObject("Scripting.FileSystemObject")
Const ForWriting = 2
Set objFile = objFSO.OpenTextFile("c:\test.txt", ForWriting, True)
Dim ex
Dim I
DIM ID
Set ex = CreateObject("Excel.Application")
ex.Workbooks.Open "C:\Log.xls"    'Open file
With ex.ActiveSheet
For I = 1 To ActiveSheet.UsedRange.Columns.Count     ' <---- i put 1 to 2000 because didn't know how to search only the writable cells it might be 60-100 records is it another way to search automatic colum A so it find like 69 records and search only in this records ?
If ex.ActiveSheet.Range("A" & I).Value = "2" Then   '   <---here is the telephone number that we want to search from colum A
ID = ex.ActiveSheet.Range("B" & I).Value
objfile.WriteLine ID
End If
Next
objfile.Close
ex.Quit
Function KillExcel
strComputer = "."
Set objWMIService = GetObject("winmgmts:" & _
       "{impersonationLevel=Impersonate}!\\" _
            & strComputer & "\root\cimv2")
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colProcessList = objWMIService.ExecQuery _
("Select * from Win32_Process Where Name = 'EXCEL.EXE'")
For Each objProcess in colProcessList
IF objProcess.Name = "EXCEL.EXE" Then
objProcess.Terminate()
'Wscript.Echo oblProcess.Name &" killed"
End If
Next
End Function
WScript.Quit

It says that there is an error at 18 line , Syntax error, code : 800A03EA

(in reply to hamboy)

Post #: 13

RE: Excel and vbs - 12/6/2007 10:26:56 PM

ehvbs

Posts: 2200
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi Stefgr,

(1) Use proper indentation to see where an "End With" is missing

(2) Don't mix main level code and Functions/Subs

(3) Use the VBScript Docs to find the sentence

"If a Function procedure has no arguments, its Function statement must include an empty set of parentheses"

and think about it.

(in reply to Stefgr)

Post #: 14

RE: Excel and vbs - 12/6/2007 10:59:42 PM

Stefgr

Posts: 24
Score: 0
Joined: 11/27/2007
Status: offline

I forgot End With i put it :

Set ObjFSO = CreateObject("Scripting.FileSystemObject")
Const ForWriting = 2
Set objFile = objFSO.OpenTextFile("c:\test.txt", ForWriting, True)
Dim ex
Dim I
DIM ID
Set ex = CreateObject("Excel.Application")
ex.Workbooks.Open "C:\Log.xls"    'Open file
With ex.ActiveSheet
For I = 1 To ActiveSheet.UsedRange.Columns.Count
If ex.ActiveSheet.Range("A" & I).Value = "2" Then
ID = ex.ActiveSheet.Range("B" & I).Value
objfile.WriteLine ID
End If
Next
End With
objfile.Close
ex.Quit
Function KillExcel
strComputer = "."
Set objWMIService = GetObject("winmgmts:" & _
      "{impersonationLevel=Impersonate}!\\" _
           & strComputer & "\root\cimv2")
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colProcessList = objWMIService.ExecQuery _
("Select * from Win32_Process Where Name = 'EXCEL.EXE'")
For Each objProcess in colProcessList
IF objProcess.Name = "EXCEL.EXE" Then
objProcess.Terminate()
'Wscript.Echo oblProcess.Name &" killed"
End If
Next
End Function
WScript.Quit

Ok i think the problem should be the :

With ex.ActiveSheet
For I = 1 To ActiveSheet.UsedRange.Columns.Count
If ex.ActiveSheet.Range("A" & I).Value = "2" Then
ID = ex.ActiveSheet.Range("B" & I).Value

and the ActiveSheet.UsedRange.Columns.Count    . I want to check only the A column. Anyone can help with this i have read some guids but couldn't understand what is wrong or how to change the code to work.

Thanks

(in reply to ehvbs)

Post #: 15

RE: Excel and vbs - 12/7/2007 1:53:36 AM

morpheus83uk

Posts: 379
Score: 0
Joined: 8/21/2006
Status: offline

Hello,

Give this code ago:

This should give you what you need without the kill excel filnction you have it closes excel perfectly it writes the ID into the text file and it also does a ROW count opposed to a column count which is why it was not working and counting the rows as before you used 2000 now it will count the rows for you.

Hope this helps

Many Thanks

James

< Message edited by morpheus83uk -- 12/7/2007 3:41:16 AM >

(in reply to Stefgr)

Post #: 16

RE: Excel and vbs - 12/17/2007 12:33:15 AM

Stefgr

Posts: 24
Score: 0
Joined: 11/27/2007
Status: offline

GReat Thank you very much this script worked perfectly :)

I have some other questions more difficult if you can help me .

We have this script :

Set ObjFSO = CreateObject("Scripting.FileSystemObject")
Const ForWriting = 2
Set objFile = objFSO.OpenTextFile("c:\test.txt", ForWriting, True)
Dim ex, I, ID
Set ex = CreateObject("Excel.Application")
ex.Workbooks.Open "C:\Log.xls"    'Open file
With ex.ActiveSheet
For I = 1 To ex.ActiveSheet.UsedRange.Rows.Count
If ex.ActiveSheet.Range("A" & I).Value = "2" Then
ID = ex.ActiveSheet.Range("B" & I).Value
objfile.WriteLine ID
End If
Next
End With
objfile.Close
ex.Quit
WScript.Quit

This Script Check Log.xls column A to search and find the number 2. If it finds it , it writes the info of column B in a test.txt file.

If we have an excel A column the surname , B column the name and we want to write the column C into a file. But We are searching with a surname name method.

I should be like this ? :

Set ObjFSO = CreateObject("Scripting.FileSystemObject")
Const ForWriting = 2
Set objFile = objFSO.OpenTextFile("c:\test.txt", ForWriting, True)
Dim ex, I, ID
Set ex = CreateObject("Excel.Application")
ex.Workbooks.Open "C:\Log.xls"    'Open file
With ex.ActiveSheet
For I = 1 To ex.ActiveSheet.UsedRange.Rows.Count
If ex.ActiveSheet.Range("A"+"B" & I).Value = "surname name" Then     <----Check This
ID = ex.ActiveSheet.Range("C" & I).Value
objfile.WriteLine ID
End If
Next
End With
objfile.Close
ex.Quit
WScript.Quit

(in reply to morpheus83uk)

Post #: 17

RE: Excel and vbs - 12/17/2007 12:58:55 AM

morpheus83uk

Posts: 379
Score: 0
Joined: 8/21/2006
Status: offline

Hello,

On a brief look at the code I think but I have not tested this should work for you...

Let me know if you get any errors etc... like I say I havent yet tested it...

< Message edited by morpheus83uk -- 12/17/2007 1:01:51 AM >

(in reply to Stefgr)