1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: To retrieve data from an excel file and to change it using VBA

Discussion in 'Business Applications' started by nynamyna, Aug 11, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. nynamyna

    nynamyna Thread Starter

    Joined:
    Aug 10, 2009
    Messages:
    28
    Hi everybody

    I am new to this forum.....I have a very basic knowledge of VBA .....So please bare with me.....

    I am designing a tool to effectively schedule the surgeries...

    I have an excel file in which if I click 'click here to schedule a surgery' button then a userform will open ....In that userform date will be chosen and the number of nurses required for a surgery is entered......I have another excel file in which the maximum number of nurses for a day is already saved (Input file for # of nurses.xls)....What I need is ....for eg: If the maximum number of nurses available for a day is only 10 then if I try to schedule more than 10 nurses for a particular day(for eg: one surgery may require 5 nurses, another may require 4 and the third may require 3 at a same time but different operating rooms..) then a message box should open saying 'there is no enough # of nurses available on the chosen date'

    I have attached a sample file along with the additional excel files .......

    Thanks
    Bharath
     

    Attached Files:

  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    Bharath, welcome to the forum.
    It would be a bit easier if the Input file for # of nurses.xls and Input file for patient details were other Worksheets in the same Workbook, rather than completely separate workbooks.
    To assist you further we would need to know where you are going to store the # of Nurses and it would be much easier to calculate if you also have a cell containing the Total Number of Nurses aleady booked for a particular day.

    Have you considered using Access for this application?
     
  3. nynamyna

    nynamyna Thread Starter

    Joined:
    Aug 10, 2009
    Messages:
    28
    Hi OBP

    Thanks for your reply.......Originally the main excel file itself has 365 sheets so they are asking for separate workbooks in order to easily access it and print it out......As now the # of nurses for each day is saved in the excel file called 'Input file for patient details.xls' under a separate column called '# of nurses'......Those people need the application only in excel so access cannot be used.......In the excel file attached (Input file for # of nurses.xls) I have already scheduled the total number of nurses for january......I am attaching it again

    Thank you very much for your help

    Bharath
     

    Attached Files:

  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    So your form is going to have to Identify the correct Worksheet for the Date Selected?
    Each Sheet will need the total number of nurses already booked?
     
  5. nynamyna

    nynamyna Thread Starter

    Joined:
    Aug 10, 2009
    Messages:
    28
    what you say is right........My userform already identifies the correct work sheet according to the date chosen and saves the data entered in an excel file called ' Input file for patient details' .......similarly it should identify the correct worksheet for a specific date chosen retrieve the total number of nurses from it.......Yes each sheet will need the total number of nurses already booked for a particulaar date....If the total number of nurses is not entered in the excel then it should say 'Nurses are not scheduled so please schedule them first'
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    Bharath, if your knowledge of VBA is very basic, how have you produced the code that you have already, as it is pretty good?
     
  7. nynamyna

    nynamyna Thread Starter

    Joined:
    Aug 10, 2009
    Messages:
    28
    That was a good question........I asked a person's help.....He used to tell me everything.....Right now he is busy so I couldn't ask his help......tats why I am seeking help.......Moreover the time given is also very less....I need to show some improvements each week ..Tats y I ask help online rather than studying from the basic.....Now I am also learning VBA from basic and also showing some improvements tats y I chose this way......
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    OK, I am not so quick with Excel VBA as I hardly ever use it, as I work mostly in Access. So I will have to post something tomorrow.
    Maybe Zack Barresse will come along and do it much quicker and more proffessionally.
     
  9. nynamyna

    nynamyna Thread Starter

    Joined:
    Aug 10, 2009
    Messages:
    28
    HI OBP

    Thank you very much for your help..... I will wait till tomorrow....No problem for me.......Should I contact Zack Barresse or he himself will reply to this thread.......
     
  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    If he is available he might see it and respond. You could try asking him to take a look.
    If not I will post something tomorrow.
     
  11. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    I have the first part., select a day & month & then enter a value in the # Nurses field.
    Enter day 13 and August.
     

    Attached Files:

  12. nynamyna

    nynamyna Thread Starter

    Joined:
    Aug 10, 2009
    Messages:
    28
    Hi Anthony

    Thanks for the immediate response........so what you sent me retrieves the total number of nurses on the scheduled date and says that through a message box........

    I have an other doubt in the following macro

    '
    ' Macro2 Macro
    ' Macro recorded 12/08/2009 by Anthony Osborn
    Dim monthname As String, wbInput As Workbook, wsInput As Worksheet, wbdata As Workbook
    Set wbdata = ThisWorkbook
    Set wbInput = Workbooks.Open(ThisWorkbook.Path & "\" & cstrFILE_INPUT)
    Set wsInput = wbInput.Sheets(ComboBox1.Text)
    Range("I1:O3").Select
    With Selection
    .MergeCells = False
    End With
    monthname = Format(ActiveCell.Value, "mmmm")
    MsgBox monthname
    With Selection
    .MergeCells = True
    End With
    nonurses = Cells(1, 1)
    MsgBox nonurses & " " & monthname
    Exit Sub
    '
    End Sub

    If I choose a date and a month for which the total # of nurses is already scheduled......... then it retrieves the total number of nurses and pops up a window with the total number of nurses..........But If i have not entered anything for the total number of nurses for a month in the ' input for the # of nurses' excel sheet then according to the above macro....... if there is no nurses scheduled for a month then a message box should open saying no nurses and month name ........but it is not happening ...

    If I run the above macro seperately it gives run time error I don't know why.....I made sure that all the files are in the same directory but also it gives a 'run time error 1004'......


    Thanks
    Bharath
     
  13. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    Bharath, sorry, that was just me playing around looking at the data on the Worksheet rather than the user form, so you can delete that.
     
  14. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    We now need to look at the number of Nurses already booked for the chosen period(s) and compare that to the number available.
    But that will probably have to be for tomorrow.
     
  15. nynamyna

    nynamyna Thread Starter

    Joined:
    Aug 10, 2009
    Messages:
    28
    I just confirmed it tats all....so as of now the code retrieves the total number of nurses and pops up a window with the total number of nurses right.....I ll chat with you tmro ....thanks

    Thanks
    Bharath
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/851527

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice