RE: Excel and vbs

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

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 > >>

Message

<< Older Topic Newer Topic >>

RE: Excel and vbs - 12/17/2007 9:46:41 AM

morpheus83uk

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

Hello,

Give this code a go... see if it helps you any... I have returned back to the house now and had a proper look at the code and what your trying to do...

Hope this is what your after :)

Cheers

James

(in reply to morpheus83uk)

Post #: 21

RE: Excel and vbs - 12/17/2007 7:24:43 PM

Stefgr

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

It worked Great Thank you very much !!! u use if ... and .. great i didn't even pass my mind thank you.
And a last one if yu can help i have read some articles but i can;t even do it work.
So far we have the code :

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
'Range = ex.ActiveSheet.Range("A:B").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 = "Name" Then
ID = ex.ActiveSheet.Range("C" & I).Value
objfile.WriteLine ID
End If
Next
End With
objFile.Close
ex.Quit
WScript.Quit

that check A column /cell+ B column/cell and paste the info the C column/cell

Now if we have an excel like A column (cell A1) surname, B column (cell B1) name , C column (cell C1) info1.
                                       A column (Cell A2) nothing, B column (Cell B2) nothing, C column (C2) some info more about the person above
                                    A column (cell A3) surname, B column (cell B3) name, C column (cell C3) info2.
                                       A column (Cell A4) nothing, B column (Cell B4) nothing, C column (C4) some info more about the person above
                                    A column (Cell A5) nothing, B column (Cell B5) nothing, C column (C5) some info more about the person above
                                     ...etc...

The Excel might be like this :

http://img409.imageshack.us/my.php?image=excelbp8.jpg

The problem is how we can make the code so it read the surname and name and past the info of the cells (like the C1 + C2) ?
And in the other example the info of (C3+C4+C5) and etc.

If we put an id at start at Cell A (1,2,3,4,....) then surname and then name and then info. It will help ? But how it will understand what to copy to the file?

Thank you

< Message edited by Stefgr -- 12/17/2007 7:51:15 PM >

(in reply to morpheus83uk)

Post #: 22

RE: Excel and vbs - 12/17/2007 8:56:15 PM

morpheus83uk

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

Hello,

I am not quite sure what your after...

No matter what is in columns A and B you still want the information from column C? If this is the case why are we already geting the information from C based on if there is criteria in there?

Will the information say the phone numbr be changing which you will be searching for within the Columns? If so are you planning on opning the script each time to change that and then save and run it when an input box would be better?

Now we have the working code please could you have a go and then post your code to see whats going on and where the problem lies as its the only way to learn :)

Many Thanks

James

(in reply to Stefgr)

Post #: 23

RE: Excel and vbs - 12/17/2007 9:21:08 PM

Stefgr

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

Hello,

What i am saying is check the http://img409.imageshack.us/my.php?image=excelbp8.jpg

We have 3 ppl one in A2 , one in A3 and one in A6.(we dont care about the phone we will search with their surname and name)

Now for the person in A2 the code :

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("B:C").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" Then
ID = ex.ActiveSheet.Range("D" & I).Value
ID2 = ex.ActiveSheet.Range("E" & I).Value
objfile.WriteLine "Course: " & ID & " Abcenses Limit : " & ID2
End If
Next
End With
objFile.Close
ex.Quit
WScript.Quit

Is work Fine because that person has only one course and his info is only in one line D2,E2,F2,...

But the person at A3 has more courses.With the above code it will write in the file only one course that is in the same line.But under it has 2 more courses that will not display.I am trying to change the code so it will write on file and the other courses that might have a person.Is it possible ?

Thank you

< Message edited by Stefgr -- 12/17/2007 9:22:08 PM >

(in reply to morpheus83uk)

Post #: 24

RE: Excel and vbs - 12/17/2007 10:31:46 PM

morpheus83uk

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

Hello,

Thats much better I know what you are trying to achive now...

The code you have will work as stated... The thing is you have the code looking for a name and then getting the info from the various cells on the same row....

it might be easier to update the spreadshet with the name next to each course which would give you the results...

I dont know how to do this directly but I am sure if you do a google search it might help.

I will have another look when I am back in for you but if you could give it a go...

Cheers

James

(in reply to Stefgr)

Post #: 25

RE: Excel and vbs - 12/17/2007 11:17:35 PM

Stefgr

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

I tried copy paste like the same name and surname under the empty cells and its working ok i am getting the different infos.
But i am trying something more automatic . I check google we can use the command : ?

If Cell.Value = ........(something zero?) Then
'insert some comparison code here
End If
Next

(in reply to morpheus83uk)

Post #: 26

RE: Excel and vbs - 12/18/2007 3:04:30 AM

morpheus83uk

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

Hi,

Why not try what you already know works and then repeat it in the loop...

Like so...

It will run the First IF statement and write followed by the second IF statement and write and then it will loop around... so it will appear in numerical order...

I hope this helps

Many Thanks

James

(in reply to Stefgr)

Post #: 27

RE: Excel and vbs - 12/18/2007 4:55:37 AM

Stefgr

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

Ya but it isn't standar this. Like one person might have on course. The other one might have 5 courses , the other 2 courses

(in reply to morpheus83uk)

Post #: 28

RE: Excel and vbs - 12/18/2007 5:27:20 AM

morpheus83uk

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

Oh right sorry..

Erm... honestly I dont know a better way of doing this if someone else does then please dig me out of this :)

I will have a look around and see what I can find and I will have a play with the code and see what I can find out for you though... Google is the way forward..

James

(in reply to Stefgr)

Post #: 29

RE: Excel and vbs - 12/18/2007 5:42:40 AM

ebgreen

Posts: 4613
Score: 31
Joined: 7/12/2005
Status: offline

Sounds like a database would be a better solution for you.

_____________________________

"... 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 #: 30

RE: Excel and vbs - 12/18/2007 7:45:02 AM

morpheus83uk

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

Hello,

I was thinking along them lines...

As I have been playing arond I have found out I can neaten out the code which does the same thing which we had before:

However I cant seem to get my head around how I could get it to find a none needed name such as Smith Jon and then anything under that leave alone..

I was thing something like a Select Case or something But I wouldnt know how I would go about getting this done... Or I was thinking something like checking that if the next row is blank then write that one to and the next etc... until its hits something which does not meet the criteria...

I have tried such things like adding a counter to the code to try and get it to move to the next loop by saying I + 1 yet I get a type mismatch on the line I have the I + 1 and I cant seem to figure out how I can get it do work properly I just get errors... Would anyone have any ideas on this?

I am trying hard here not to be beat by this!!! Any pointers would be a help I will fight with the code myself if I have to I refuse to be beaten by some code!!!! grrr

Many Thanks

James

(in reply to ebgreen)

Post #: 31

RE: Excel and vbs - 12/18/2007 8:45:45 AM

morpheus83uk

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

Hello,

Please could someone throw me a bone here as I am about to launce the PC in a moment lol

I have the code:

However its giving me funny results...

I should have in the end result of the rows...

1-8 and 16-30 all in there however I end up with...

1-7 , 9 , 12 -14 , 21 -24

I am getting closer on this yet with these od results I cannot get my head around where the problem lies... could someone assist a little so I can complete this please?

Many Thanks

James

(in reply to morpheus83uk)

Post #: 32

RE: Excel and vbs - 12/18/2007 9:01:21 AM

ebgreen

Posts: 4613
Score: 31
Joined: 7/12/2005
Status: offline

Could you show us what the data looks like?

_____________________________

"... 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 #: 33

RE: Excel and vbs - 12/18/2007 9:05:50 AM

morpheus83uk

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

No problem :)

1 Surname Name  Course Name 1
2    Course Name 2
3    Course Name 3
4    Course Name 4
5    Course Name 5
6    Course Name 6
7    Course Name 7
8    Course Name 8
9 some othername Course Name 9
10    Course Name 10
11 some othername Course Name 11
12 some othername Course Name 12
13    Course Name 13
14    Course Name 14
15    Course Name 15
16 Surname Name  Course Name 16
17 Surname Name  Course Name 17
18 Surname Name  Course Name 18
19 Surname Name  Course Name 19
20 Surname Name  Course Name 20
21 Surname Name  Course Name 21
22    Course Name 22
23    Course Name 23
24    Course Name 24
25    Course Name 25
26 Surname Name  Course Name 26
27 Surname Name  Course Name 27
28 Surname Name  Course Name 28
29 Surname Name  Course Name 29
30 Surname Name  Course Name 30

This is my replication of the data I am using for test...

Many Thanks

James

< Message edited by morpheus83uk -- 12/18/2007 9:10:06 AM >

(in reply to ebgreen)

Post #: 34

RE: Excel and vbs - 12/18/2007 9:07:06 AM

ebgreen

Posts: 4613
Score: 31
Joined: 7/12/2005
Status: offline

that is how it is layed out? All one column?

_____________________________

"... 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 #: 35

RE: Excel and vbs - 12/18/2007 9:10:34 AM

morpheus83uk

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

No sory re posted it in some form or readable format.

Thanks

James

(in reply to ebgreen)

Post #: 36

RE: Excel and vbs - 12/18/2007 9:29:42 AM

ebgreen

Posts: 4613
Score: 31
Joined: 7/12/2005
Status: offline

Sorry to be a pain, but could you export it as a csv, open the csv in notepad then copy and paste that into a post? I just want to be sure that I am working with the same data that you are.

_____________________________

"... 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 #: 37

RE: Excel and vbs - 12/18/2007 9:40:24 AM

morpheus83uk

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

No problem...

here you go..

1,Surname,Name,Course Name,1
2,,,Course Name,2
3,,,Course Name,3
4,,,Course Name,4
5,,,Course Name,5
6,,,Course Name,6
7,,,Course Name,7
8,,,Course Name,8
9,some,other name,Course Name,9
10,,,Course Name,10
11,some,other name,Course Name,11
12,some,other name,Course Name,12
13,,,Course Name,13
14,,,Course Name,14
15,,,Course Name,15
16,Surname,Name,Course Name,16
17,Surname,Name,Course Name,17
18,Surname,Name,Course Name,18
19,Surname,Name,Course Name,19
20,Surname,Name,Course Name,20
21,Surname,Name,Course Name,21
22,,,Course Name,22
23,,,Course Name,23
24,,,Course Name,24
25,,,Course Name,25
26,Surname,Name,Course Name,26
27,Surname,Name,Course Name,27
28,Surname,Name,Course Name,28
29,Surname,Name,Course Name,29
30,Surname,Name,Course Name,30

Many Thanks

James

(in reply to ebgreen)

Post #: 38

RE: Excel and vbs - 12/18/2007 9:50:43 AM

ebgreen

Posts: 4613
Score: 31
Joined: 7/12/2005
Status: offline

Ok, I am confused as to why you say you should get 1-8 and 16-30. For instance 1
is:

1,Surname,Name,Course Name,1

but 2 is:

2,,,Course Name,2

So 1 has Surname and 2 does not. They would max different criteria so why would they both be in the result?

_____________________________

"... 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 #: 39

RE: Excel and vbs - 12/18/2007 9:51:52 AM

ebgreen

Posts: 4613
Score: 31
Joined: 7/12/2005
Status: offline

Could you define exactly when and what you would want in the result set?

_____________________________

"... 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)