Live Chat & Podcast at 1:00PM Eastern on Sunday!
There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
Search
Business Applications
Tag Cloud
access acer asus bios bsod computer crash desktop driver drivers error ethernet excel freeze gaming gpu hard drive hardware hdmi internet laptop malware memory monitor motherboard netgear network printer problem ram registry router security slow software sound trojan ubuntu 11.10 uninstall usb video virus vista wifi windows windows 7 windows 7 32 bit windows 7 64 bit windows xp wireless
Search
Search for:
Tech Support Guy Forums > Software & Hardware > Business Applications >
Solved: To retrieve data from an excel file and to change it using VBA

Reply  
Thread Tools
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
13-Aug-2009, 07:29 AM #16
Bharath, do you intend putting data in to the "SampleBook" Worksheets in the Time Slots provided?
As that would probably be the best place to look for Nurses Currently booked.
nynamyna's Avatar
Junior Member with 27 posts.
 
Join Date: Aug 2009
13-Aug-2009, 12:51 PM #17
Hi Anthony

Sorry I saw your post just now........only four details (surgeon name, surgery code, surgery type, equpiment) will be saved in the time slots available in the 'samplebook'.............as of now only two details will be saved in the but we can add that later.......all the other details entered will be saved in the excel sheet called 'Input file for patient details.xls' in order to print them easily.............

Thanks
Bharath
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
14-Aug-2009, 09:05 AM #18
The Patient report Does Not include the Time Slot(s) of the Operation. It would need to to try and Calculate the Nurses in use over a given period.
It may be better to add it to the Input Worksheet, but not have it shown, say in Column X or AA.
This is going to take VBA to do the Calculation wherever it is stored and it won't be easy to do. It will have to take in to consideration any "overlapping" time slots and exclude them.
__________________
OBP
I do not give up easily
nynamyna's Avatar
Junior Member with 27 posts.
 
Join Date: Aug 2009
14-Aug-2009, 10:08 AM #19
Hi Tony

wat you say is right but...The code already does not allow to schedule two surgeries at the same time .....try choosing same start time , duration and OR it will say that the OR is already occupied......

I have a sugesstion can we do like the following:

In the 'Input file for # of nurses.xls' we can add additional columns saying 'remaining # of nurses' and 'remaining # of equipments" we can populate both the columns with the same number in 'total number of nurses' and 'total number of equipments column' .....after that if we schedule a surgery by choosing a particular date in the drop down of the userform then it should read the 'remaining number of nurses' column and 'remaining number of equipments' column of the same date in the excel sheet (input file for number of nurses.xls).....so the remaining number of nurses and equipments columns should get updated according to the number of nurses scheduled (for eg:If there are a total of 10 nurses and 10 equipments then populate all the columns with 10 ........ if two nurses and two equipments are scheduled then the remaining number of nurses and equipments on that particular date should get reduced from 10 to 8........ so again if surgery is scheduled on the same date then it should read the column 'remaning number of nurses' and 'remaining number of equipments' of the same date which will have only 8 nurses and equipments.......
I have attached the modified excel file ' Input file for # of nurses with remaining'

I got this idea .....I dont know how far it is easy to execute......tell me your opinion

Thanks
Bharath
Attached Files
File Type: xls input%20file%20for%20# of nurses with remaining.xls (49.5 KB, 35 views)
nynamyna's Avatar
Junior Member with 27 posts.
 
Join Date: Aug 2009
14-Aug-2009, 10:27 AM #20
after keep on reducing if there is 0 remaining nurses and equipments and if someone asks for 1 or 3 nurses then a message box should open saying 'there is no nurses left for the scheduled date please choose an other date'......
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
17-Aug-2009, 06:39 AM #21
Bharath, that is the sort of thing that I was talking about. I don't think that will work though, I will take a look today and see how it goes. The problem will be that it is not the Nurses Remaining for a Day, but for a Time Period. i.e. Nurses booked for 8:00 to 10:00 should then be available for 11:00 or do they only carry out 1 operation per day?
__________________
OBP
I do not give up easily
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
17-Aug-2009, 09:13 AM #22
OK, this works, it means copying columns Z to AB when the Month is selcted.
It now puts the "days" nurses in the cell Z1 and stores the "used" nurses in column AA against the times booked.
Attached Files
File Type: xls sampleBook1 latest.xls (110.5 KB, 31 views)

Last edited by OBP; 17-Aug-2009 at 09:24 AM..
nynamyna's Avatar
Junior Member with 27 posts.
 
Join Date: Aug 2009
17-Aug-2009, 11:51 AM #23
Hi Tony

Thank you very much for the code.....I didn't even think about the time problem you said....Thanks for telling me the problem....your code does exactly what you said......I have a small doubt whether to put total number of nurses available at all time fixed or not? you have fixed it as 9 at all time right....I will think about that alone and get back to you ...........

Thank you
Bharath
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
17-Aug-2009, 12:17 PM #24
Bharath, it is not fixed, it is updated from the other Workbook when you enter the Month, unless something is already there, which it wouldn't be if you had your 365 sheets.
Delete it and you will see what I mean.
nynamyna's Avatar
Junior Member with 27 posts.
 
Join Date: Aug 2009
17-Aug-2009, 12:59 PM #25
No what I meant is that for all the time slots the total number of nurses will be the same initially right....tats wat I meant........


your code is perfect but I need some changes to be done.....I do not know how to do it

instead of the additional excel file 'Input for the # of nurses.xls' I would go with your idea of putting them by the side of all the 365 sheets but I do not know how to put those in all the 365 sheets. so that we can delete the combobox1(month) and combobox2(date) from the userform1 because it would be unecessary......

the nurses have a turnover time of half an hour (if there are a total of 9 nurses and if three nurses are used from 7 to 10 then the remaining should be 6 for next half an hour that is till 10.30)

Thanks
Bharath
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
17-Aug-2009, 01:15 PM #26
Well you could have a Combo to select the nurses quantity and put that in Cell Z1 by having Z1 bound to the combo.
When you say you don't know how to put it on the 365 sheets, do you mean the Times and Formula?
nynamyna's Avatar
Junior Member with 27 posts.
 
Join Date: Aug 2009
17-Aug-2009, 01:23 PM #27
ya I mean the times and the column headings 'nurses used' 'nurses available' 'total nurses available' so that it is automatically saved in all the 365 workshets......and that turnover problem......
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
17-Aug-2009, 01:28 PM #28
You can copy the data from my sheet, highlight the first Excel worksheet tab, hold down the shift key and move to the last worksheet, that should highlight them all Paste the data in to cell Z1 and it should paste in them all.
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
17-Aug-2009, 01:32 PM #29
Or
Right-click a sheet tab, and then click Select All Sheets on the shortcut menu.
nynamyna's Avatar
Junior Member with 27 posts.
 
Join Date: Aug 2009
17-Aug-2009, 01:41 PM #30
thanks....I thought we need to write a code......if you help me to add that turnover time of half an hour then the problem is solved......I realy thank you for the help.......
Reply

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who want to help you solve your computer problems. See our Welcome Guide to get started.
Thread Tools



Facebook Facebook Twitter Twitter TechGuy.tv TechGuy.tv Mobile TSG Mobile
You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 02:08 AM.
Copyright © 1996 - 2011 TechGuy, Inc. All rights reserved.

Powered by Cermak Technologies, Inc.