Photo Gallery Member List Search Calendars FAQ Ticket List Log Out


RE: 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 >> RE: Excel and vbs
  Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks! 		Page: <<   < prev  1 2 [3] 4   next >   >>
Login
Message << Older Topic   Newer Topic >>
RE: Excel and vbs - 12/18/2007 10:08:30 AM   
  ebgreen


Posts: 4613
Score: 31
Joined: 7/12/2005
Status: offline 		Ok, Morph, I went back and looked at the thread. I think I understand the issue better...maybe. Let me think about it.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to ebgreen)
 
 
Post #: 41
 
RE: Excel and vbs - 12/18/2007 10:21:02 AM   
  morpheus83uk

 

Posts: 315
Score: 0
Joined: 8/21/2006
Status: offline 		Thanks I ust cant get my head around it its just really odd results...

(in reply to ebgreen)
 
 
Post #: 42
 
RE: Excel and vbs - 12/18/2007 7:36:58 PM   
  Stefgr

 

Posts: 24
Score: 0
Joined: 11/27/2007
Status: offline 		I hope i am not a problem for what i ask....:(

ebgreen if you want to check an example check the excel that i have given you : http://img409.imageshack.us/my.php?image=excelbp8.jpg
 

(in reply to morpheus83uk)
 
 
Post #: 43
 
RE: Excel and vbs - 12/18/2007 7:45:31 PM   
  Stefgr

 

Posts: 24
Score: 0
Joined: 11/27/2007
Status: offline 		morpheus83uk i tried your last code and it works (for start) but its looping again and again and again for any surname

(in reply to Stefgr)
 
 
Post #: 44
 
RE: Excel and vbs - 12/18/2007 10:11:03 PM   
  morpheus83uk

 

Posts: 315
Score: 0
Joined: 8/21/2006
Status: offline 		Hey,

For any surname? as I get diffrent results thats why I am all confused... I have tried modifying it that much I can make it loop contunualy and build a massive text file or I can get it to do nothing or I can get it to write ALL the blanks or anything which is filled in but never quite the correct criteria which you require...  I think I have it working just ned to tweak it a little bit more for it to do what you require...

Any more head on this would be good :)

Thanks

James

(in reply to Stefgr)
 
 
Post #: 45
 
RE: Excel and vbs - 12/18/2007 10:49:54 PM   
  Stefgr

 

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

i have this list : http://rapidshare.com/files/77622736/total_abs_list.XLS.html

And tried your last script

And i got this results : http://rapidshare.com/files/77622917/test.txt.html

(in reply to morpheus83uk)
 
 
Post #: 46
 
RE: Excel and vbs - 12/18/2007 11:03:28 PM   
  morpheus83uk

 

Posts: 315
Score: 0
Joined: 8/21/2006
Status: offline 		Well its giving essentially the same results... just plain odd which dont seem to met what I have set!

If I can get it working on my test code I will try it on your spreadsheet and see if I get the correct results and then post for you to test and confirm...

Thanks for that

James

(in reply to Stefgr)
 
 
Post #: 47
 
RE: Excel and vbs - 12/18/2007 11:11:35 PM   
  Stefgr

 

Posts: 24
Score: 0
Joined: 11/27/2007
Status: offline 		Thank you very much!!

(in reply to morpheus83uk)
 
 
Post #: 48
 
RE: Excel and vbs - 12/19/2007 1:13:52 AM   
  ebgreen


Posts: 4613
Score: 31
Joined: 7/12/2005
Status: offline 		For some reason it won't let me download from those links. Regardless, I have created a test xls that mirrors the image that you posted earlier. I think we need a quick reset. What is the goal of this code? What information are you trying to get out of the xls and why? What criteria indicate a row that you want to get the info from?

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to Stefgr)
 
 
Post #: 49
 
RE: Excel and vbs - 12/19/2007 1:21:37 AM   
  Stefgr

 

Posts: 24
Score: 0
Joined: 11/27/2007
Status: offline 		click on the link , scroll down and press free , write the code that yu see and press download and yu will d.l the files.

I change a little the script like that :

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:\Log1.xls"    'Open file
With ex.ActiveSheet
'Range = ex.ActiveSheet.Range("A:B:E").Select
For I = 1 To ex.ActiveSheet.UsedRange.Rows.Count 
If ex.ActiveSheet.Range("A" & I).Value = "surname _
   And ex.ActiveSheet.Range("B" & I).Value = "names" Then
If ex.ActiveSheet.Range("E" & I).Value = "phone" Then
ID = ex.ActiveSheet.Range("C" & I).Value
ID2 = ex.ActiveSheet.Range("D" & I).Value 
objfile.WriteLine "TEst : " & ID & " Test 2 : " & ID2
End if
End if
Next 
End With
objFile.Close
ex.Quit
WScript.Quit

This will compare the surname and name and also will check in E column the phone number. I put also the phonenumber because sometime the surname and the name might be the same.

What i am trying to do is to check the surname and name and now also the phone and ouput the infos of some courses or other info to a .txt .

So far this code works , but we are trying to see if it is possible with this xls to output the information of a specific person that might have more than one course or other info.

Thanks you

(in reply to ebgreen)
 
 
Post #: 50
 
RE: Excel and vbs - 12/19/2007 2:18:10 AM   
  ebgreen


Posts: 4613
Score: 31
Joined: 7/12/2005
Status: offline 		Try 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:\Log1.xls"    'Open file
bGetData = False
With ex.ActiveSheet
'Range = ex.ActiveSheet.Range("A:B:E").Select
For I = 1 To ex.ActiveSheet.UsedRange.Rows.Count  
  If (ex.ActiveSheet.Range("B" & I).Value = "APOSTOLIDIS" And ex.ActiveSheet.Range("C" & I).Value = "FOIVOS" And ex.ActiveSheet.Range("I" & I).Value = "6945757419") Or (bGetData And ex.ActiveSheet.Range("B" & I).Value = "") Then
  ID = ex.ActiveSheet.Range("C" & I).Value
  ID2 = ex.ActiveSheet.Range("D" & I).Value 
  'objfile.WriteLine "TEst : " & ID & " Test 2 : " & ID2
  bGetData = True
 Else
  bGetData = False
 End if
Next 
End With
objFile.Close
ex.Quit
WScript.Quit

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to Stefgr)
 
 
Post #: 51
 
RE: Excel and vbs - 12/19/2007 2:47:59 AM   
  morpheus83uk

 

Posts: 315
Score: 0
Joined: 8/21/2006
Status: offline 		Hi,

Well that seems to work for me... :) I did a couple of true false things but it did not turn out properly...

Question just for my own understanding here...

bGetData = False

Then the line..

bGetData And ex.ActiveSheet.Range("B" & I).Value = ""

What would this actually do? and what is bGetData??

Sorry for this probably stupid question (maybe just been looking my my code for so long in frustration) but I just dont seem to follow it...

James

(in reply to ebgreen)
 
 
Post #: 52
 
RE: Excel and vbs - 12/19/2007 2:51:02 AM   
  ebgreen


Posts: 4613
Score: 31
Joined: 7/12/2005
Status: offline 		Basicly bGetData is a state variable. So whenever we find the person that we are interested in we set it to true. Then as long as the line has the user name we want OR the state vriable is true and there isn't a user name on the line, then we collect the data. The other important line is in the else clause. So if the line has user name information but it isn't the right user, we set the state variable to false. That way we don't collect data from other users.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to morpheus83uk)
 
 
Post #: 53
 
RE: Excel and vbs - 12/19/2007 2:53:54 AM   
  Stefgr

 

Posts: 24
Score: 0
Joined: 11/27/2007
Status: offline 		...THANK you its working i have tried itand do some examples and its working perfect. hmm i have to read about the bgetdata.

I have change a little the code (for the xls) :

Set ObjFSO = CreateObject("Scripting.FileSystemObject")
Const ForWriting = 2
Set objFile = objFSO.OpenTextFile("test.txt", ForWriting, True)
Dim ex, I, ID
Set ex = CreateObject("Excel.Application")
ex.Workbooks.Open "C:\Log1.xls"    'Open file
bGetData = False
With ex.ActiveSheet
'Range = ex.ActiveSheet.Range("A:B:E").Select
For I = 1 To ex.ActiveSheet.UsedRange.Rows.Count  
If (ex.ActiveSheet.Range("B" & I).Value = "ZAFEIRI" And ex.ActiveSheet.Range("C" & I).Value = "ELENI" And ex.ActiveSheet.Range("I" & I).Value = "6978258767") Or (bGetData And ex.ActiveSheet.Range("B" & I).Value = "") Then
IDD = ex.ActiveSheet.Range("D" & I).Value
IDE = ex.ActiveSheet.Range("E" & I).Value
IDF = ex.ActiveSheet.Range("F" & I).Value
IDG = ex.ActiveSheet.Range("G" & I).Value
IDH = ex.ActiveSheet.Range("H" & I).Value
objfile.WriteLine "Course : " & IDD & " Absences' Limit : " & IDE & " Total Absences : " & IDF & " Excused Absences : " & IDG & " Unexcused Absences : " & IDH
bGetData = True
Else
bGetData = False
End if
Next 
End With
objFile.Close
ex.Quit
WScript.Quit

and now it outputs perfect what each person course has.
But i checked if i put a wrong number or surname or name it can't find it in excel and it hangs up.So i tryed to make this :

Set ObjFSO = CreateObject("Scripting.FileSystemObject")
Const ForWriting = 2
Set objFile = objFSO.OpenTextFile("test.txt", ForWriting, True)
Dim ex, I, ID
Set ex = CreateObject("Excel.Application")
ex.Workbooks.Open "C:\Log1.xls"    'Open file
bGetData = False
With ex.ActiveSheet
'Range = ex.ActiveSheet.Range("A:B:E").Select
For I = 1 To ex.ActiveSheet.UsedRange.Rows.Count  
If (ex.ActiveSheet.Range("B" & I).Value = "ZAFEIRI" And ex.ActiveSheet.Range("C" & I).Value = "ELENI" And ex.ActiveSheet.Range("I" & I).Value = "6978258767") Or (bGetData And ex.ActiveSheet.Range("B" & I).Value = "") Then
IDD = ex.ActiveSheet.Range("D" & I).Value
IDE = ex.ActiveSheet.Range("E" & I).Value
IDF = ex.ActiveSheet.Range("F" & I).Value
IDG = ex.ActiveSheet.Range("G" & I).Value
IDH = ex.ActiveSheet.Range("H" & I).Value
objfile.WriteLine "Course : " & IDD & " Absences' Limit : " & IDE & " Total Absences : " & IDF & " Excused Absences : " & IDG & " Unexcused Absences : " & IDH
bGetData = True
Else
bGetData = False
objfile.WriteLine "Data Not Found"   <-----This one , but i am getting some errors
End if
Next 
End With
objFile.Close
ex.Quit
WScript.Quit

(in reply to ebgreen)
 
 
Post #: 54
 
RE: Excel and vbs - 12/19/2007 2:55:28 AM   
  morpheus83uk

 

Posts: 315
Score: 0
Joined: 8/21/2006
Status: offline 		Ah right cool thanks now I understand it!

Probably staring at the code too long makes lots of sense now so if I come across something else I will be using that!

Thanks for the explination and thanks for the help with the code much appreciated!

James :)

(in reply to ebgreen)
 
 
Post #: 55
 
RE: Excel and vbs - 12/19/2007 2:56:12 AM   
  ebgreen


Posts: 4613
Score: 31
Joined: 7/12/2005
Status: offline 		So you want it to write Data Not Found if the user is not found anywhere in the file?

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to Stefgr)
 
 
Post #: 56
 
RE: Excel and vbs - 12/19/2007 3:07:45 AM   
  Stefgr

 

Posts: 24
Score: 0
Joined: 11/27/2007
Status: offline 		Set ObjFSO = CreateObject("Scripting.FileSystemObject")
Const ForWriting = 2
Set objFile = objFSO.OpenTextFile("test.txt", ForWriting, True)
Dim ex, I, ID
Set ex = CreateObject("Excel.Application")
ex.Workbooks.Open "C:\Log1.xls"    'Open file
bGetData = False
With ex.ActiveSheet
'Range = ex.ActiveSheet.Range("A:B:E").Select
For I = 1 To ex.ActiveSheet.UsedRange.Rows.Count  
If (ex.ActiveSheet.Range("B" & I).Value = "XELIOTIa" And ex.ActiveSheet.Range("C" & I).Value = "KATERINA" And ex.ActiveSheet.Range("I" & I).Value = "") Or (bGetData And ex.ActiveSheet.Range("B" & I).Value = "") Then
IDD = ex.ActiveSheet.Range("D" & I).Value
IDE = ex.ActiveSheet.Range("E" & I).Value
IDF = ex.ActiveSheet.Range("F" & I).Value
IDG = ex.ActiveSheet.Range("G" & I).Value
IDH = ex.ActiveSheet.Range("H" & I).Value
objfile.WriteLine "Course : " & IDD & " Absences' Limit : " & IDE & " Total Absences : " & IDF & " Excused Absences : " & IDG & " Unexcused Absences : " & IDH
bGetData = True
Else
bGetData = False
objfile.WriteLine " Data Not Found "
End if
Next 
End With
objFile.Close
ex.Quit
WScript.Quit

Tried to run this i write wrong the surname (was Xelioti and i put Xeliotia) (also i change the name or the phone) and it output the  Data Not Found put it outputs for every row so i have a txt filled with Not found

(in reply to ebgreen)
 
 
Post #: 57
 
RE: Excel and vbs - 12/19/2007 3:11:21 AM   
  ebgreen


Posts: 4613
Score: 31
Joined: 7/12/2005
Status: offline 		But what you want is just one Not Found right?

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to Stefgr)
 
 
Post #: 58
 
RE: Excel and vbs - 12/19/2007 3:21:46 AM   
  Stefgr

 

Posts: 24
Score: 0
Joined: 11/27/2007
Status: offline 		a text ya not found or data not found contact us ya , just a simple text

(in reply to ebgreen)
 
 
Post #: 59
 
RE: Excel and vbs - 12/19/2007 3:26:03 AM   
  ebgreen


Posts: 4613
Score: 31
Joined: 7/12/2005
Status: offline 		Set ObjFSO = CreateObject("Scripting.FileSystemObject")
Const ForWriting = 2
Set objFile = objFSO.OpenTextFile("test.txt", ForWriting, True)
Dim ex, I, ID
Set ex = CreateObject("Excel.Application")
ex.Workbooks.Open "C:\Log1.xls"    'Open file
bGetData = False
bUserNotFound = True
With ex.ActiveSheet
'Range = ex.ActiveSheet.Range("A:B:E").Select
For I = 1 To ex.ActiveSheet.UsedRange.Rows.Count  
 If (ex.ActiveSheet.Range("B" & I).Value = "XELIOTIa" And ex.ActiveSheet.Range("C" & I).Value = "KATERINA" And ex.ActiveSheet.Range("I" & I).Value = "") Or (bGetData And ex.ActiveSheet.Range("B" & I).Value = "") Then
  IDD = ex.ActiveSheet.Range("D" & I).Value
  IDE = ex.ActiveSheet.Range("E" & I).Value
  IDF = ex.ActiveSheet.Range("F" & I).Value
  IDG = ex.ActiveSheet.Range("G" & I).Value
  IDH = ex.ActiveSheet.Range("H" & I).Value
  objfile.WriteLine "Course : " & IDD & " Absences' Limit : " & IDE & " Total Absences : " & IDF & " Excused Absences : " & IDG & " Unexcused Absences : " & IDH
  bGetData = True
  bUserNotFound = False
 Else
  bGetData = False
 End if
Next 
End With
If bUserNotFound Then
objfile.WriteLine " Data Not Found "
End If
objFile.Close
ex.Quit
WScript.Quit

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to Stefgr)
 
 
Post #: 60
 
 
Page:  <<   < prev  1 2 [3] 4   next >   >>
 
 

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.
All Forums >> [Scripting] >> WSH & Client Side VBScript >> RE: Excel and vbs Page: <<   < prev  1 2 [3] 4   next >   >>
Jump to:





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts




Forum Software © ASPPlayground.NET Advanced Edition 2.5.5 ANSI