LOOKUP SCRIPT to display excel result from username 1

[FISKO]

Hi all.
I am looking for a way to use input a username in an input box that will then use the username add it to a specific row/column and excel cell and then display the result in a popup box that the user has to aknowledge by clicking ok before they can continue with their work.
The situation is this: I have a pupil mark book that I wish to ensure that the pupils can access their marks easily without having to "see" all of my markbook, any ideas? Thanks.

Only the GOOD die young,
Me? I,m here forever!

 

[markdmac]

Not sure I understand the reason you want this, but your request is simple enough.

userstring = InputBox("Enter your name","User Name")
Set x = CreateObject("Excel.Application")
x.Workbooks.Add
x.Visible = True
x.Cells(1,1) = "User Name"
x.Cells(2,1) = userstring

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

 

[FISKO]

Thanks for the start, I tried the script and it produced an excel sheet with USERNAME in row one, column one, with the actual input name below. How do I progress this to query a specific Excel sheet and row/column that already exists, Using the inputed name.
Thanks again Mark

Only the GOOD die young,
Me? I'm here forever!

 

[markdmac]

Use GetObject(,"Excel.Application") instead of CreateObject("Excel.Application")

Note the comma is intentional. This will grab a currently open Excel file. You can iterate through the rows to find the value you are looking for and determine the row. Depending on how the user is accessing this, I would pull their user name dynamically from their login so you could eliminate the need to prompt them for it.

I do this kind of thing all the time for my customers. You should be able to figure it out fairly easily with what I have already given you.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

 

[FISKO]

Thanks for your reply, trying to sort it now.
Is it possible to grab from a closed workbook?

Only the GOOD die young,
Me? I'm here forever!

 

[markdmac]

Sort of, you need to open it first to be able to read from it.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

 

[EBGreen]

You can connect to the Excel workbook using ODBC and run queries to get your data as well.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[markdmac]

You can connect to the Excel workbook using ODBC and run queries to get your data as well.

But don't get too used to that, that has been deprecated by Microsoft. It won't work on a 64bit OS and beyond.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

 

[FISKO]

Hi EBGreen, I am still looking to use a VB script to get data from an excel workbook cell and disply it in some form of popup output box.
Thanks

Only the GOOD die young,
Me? I'm here forever!

 

[EBGreen]

But don't get too used to that, that has been deprecated by Microsoft. It won't work on a 64bit OS and beyond.

Thanks for the info.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[FISKO]

Mmmm...not going well, I'll sleep on it..and review tommorrow.
Thanks

Only the GOOD die young,
Me? I'm here forever!

 

[FISKO]

Brain dead... I just cannot seem to get it.
It seems so simple. all i want is to access excell read a known cell and display it using a script.
arggg! Any ideas
thanks

Only the GOOD die young,
Me? I'm here forever!

 

[tsuji]

[tt]excelfile="d:\test\abc.xls"
set y=x.workbooks.open(excelfile).worksheets(1) 'suppose sheet 1
data1=y.cells(1,1) 'etc etc
[/tt]

 

[FISKO]

Hi this fails to run with:Object required'x' on line 2

Only the GOOD die young,
Me? I'm here forever!

 

[tsuji]

x is what used and defined in previous member advice.

 

[FISKO]

oops, sorry not thinking.
Now testing

Only the GOOD die young,
Me? I'm here forever!

 

[FISKO]

Ah ha, Ok this script now opens a workbook, I now need it to use the name from the input box to be used in some way to target a cell in a the spreadsheet and out put this to a popup box.
Thanks

Only the GOOD die young,
Me? I'm here forever!

 

[tsuji]

The forum's help-seekers lead no hand of help-givers. It is the contrary of how this forum works. What you need is a down-to-earth honest basic study, e-tutorial type or books.

 

[FISKO]

Thanks for that...



work still in progress.

Only the GOOD die young,
Me? I'm here forever!

 

[FISKO]

Still looking for an answer to this. On the face of it it seems easy enough.
Enter a name in an INPUT BOX use this to find the row number (always 1st column) add this to a known column number to then select that cell. copy this cell and display it in a message box.Ie:-
INPUT fred (this is found in column 1 and translated to the row number 6 add this to a known column D to get D6 then use tis to copy the cell and display it in a msgbox.
The select copy close workbook I can do but the rest...
Still researching.

Only the GOOD die young,
Me? I'm here forever!