getting data from another workbook

[insanity66]

I have an excel spread sheet that my nurses in the field fill out and send to me. They are all saved with the patients name and date as the date. I want to be able to transfer their info into my main worksheet. Is there any vba code that can do this?

Thanks,
Dr. Keper

[JBeaucaire]

Writing it is not that complex if your book names match your master file properly.

Post up your master file and sample of the nurse's files with naming/date convention clearly matched between the two. We'll see what we can do.

Do you want this to be a "pick a file" macro or a "process every file in the folder automatically" macro?

Click GO ADVANCED and use the paperclip icon to post up your workbooks.

[insanity66]

Thanks for your help.
The file referral tracker is the main spreadsheet. Right now it is just a draft and I have been fooling around with macros to see if I could get it to work.

The Nurse form that I recieve is the same as the inserted Referral sheet, but I will attach them both.

[insanity66]

Writing it is not that complex if your book names match your master file properly.

Post up your master file and sample of the nurse's files with naming/date convention clearly matched between the two. We'll see what we can do.

Do you want this to be a "pick a file" macro or a "process every file in the folder automatically" macro?

Click GO ADVANCED and use the paperclip icon to post up your workbooks.

I was trying it to make it be a pick a file macro

[JBeaucaire]

So, it appears the name/date of the sheets has no bearing.

You want the nurse's form data to be imported into your Insert Referral sheet and then parsed into the DATA sheet with the Insert_Record() macro you already have?

Or something else?

Please think this through from start to finish...how/where is the incoming data supposed to land in your sheet, what happens next...and where do we finish up? What about the 3 fields at the top of Sheet2?

[insanity66]

The Nurse form will come in and I want to take that info off of that form and put it in the referral tracker form with those 3 fields, which we add that information upon receipt of the referral. All the info will then be tracked in the Data sheet of the referral tracker.

The nurses will send us a copy of the form saved as the patients name and date. We get alot of these referrals so the file name is changing every time.

I know I probally do not need all the worksheet. I was attempting to do a simple copy and past macro that did not work.

Am I being clear on what I am asking?

[JBeaucaire]

No you're not. The referral tracker workbook has multiple sheets. I am asking for specifics.

Incoming data from nurse sheet comes into exactly where?
Its own sheet?
The "Insert Referral" sheet?
Sheet2?

The missing fields at the top of Sheet2, are they important?
If so, where do those answers come from?


Think this through from start to finish, step by step, what should go where, then what should happen next, then what, then what? When are we done?

[insanity66]

I apologize for not being clear, and thanks for your patience and help.


I had the insert referral and imported tabs just as a way for copying and pasting so those can be deleted or disregarded.

Essentially, I want the nurse data coming in from the nurse form to the Data Sheet in the referral tracker workbook to fit the corresponding fields on that worksheet.
I then want the fields that are on top of sheet2 (referral tracker workbook) to be place on the same line of the referral in the corresponding column
The fields at the top of sheet2 are important. It is information that we will add to the tracker after we received the referral.

If I can get those task done it would be great!

[JBeaucaire]

Looking at the layout of your sheet, I would think:

1) click "import" icon (paperclip?)
2) select file
3) file data is imported to Sheet2 row 12 and file is marked as "imported" in the file so attempts to import it again are warned.
4) Fill in the missing fields at the top of Sheet2
5) Press "Archive Referral" icon and the data is transferred to the DATA sheet permanently.

[insanity66]

Looking at the layout of your sheet, I would think:

1) click "import" icon (paperclip?)
2) select file
3) file data is imported to Sheet2 row 12 and file is marked as "imported" in the file so attempts to import it again are warned.
4) Fill in the missing fields at the top of Sheet2
5) Press "Archive Referral" icon and the data is transferred to the DATA sheet permanently.

That would be even better then what I had in mind. Love the mark the file as imported idea.

[insanity66]

Have you had Any Luck?

[JBeaucaire]

Sorry, lost track of this thread altogether. Thanks for the PM.

In this new sheet
1) Click on the Paperclip icon to import a new form.
2) The file selected will be imported and marked as "imported". If you attempt to import it again, you will be reminded of this, but you can still opt to import it again anyway.
3) The Archive Referral button will warn you if you fail to fill out the three fields on Sheet2 properly.
4) The Sheet2 will show a gray line if there is currently no sheet being evaluated. After you click the Paperclip icon and import a new form, this will change helping emphasize the data has been imported properly and is ready to be archived.

EDIT:
5) The sheet will now warn if imported sheet is in wrong format.

[insanity66]

Awsome Thanks you very much.

In the future If I want to add more then the 3 field what code do I need to change?

[JBeaucaire]

What 3 field?

[insanity66]

Sorry the 3 input cell on sheet2
accepted or rejected
reason
cmm

Is it just a matter of changing or adding some code if in the future I wanted to expand and ad more info.

[JBeaucaire]

Ah, just click on the cell and choose DATA > VALIDATION. You'll see the list in there, just add to it.

========
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

[insanity66]

Im sorry I guess I am not being clear again. In the future if I want to add another input cell to Sheet 2 to be copied to the Date sheet after the nurse referral is imported.

So I import the nurse data sheet and then enter the info on sheet 2 such as Accepted or rejected, Reason for rejection, CMM. What if I wanted to add another input cell here such as case manager or something in the future. Will it copy that info to the Data sheet or do I need to add some code?

[JBeaucaire]

The ARCHIVE REFERRAL macro is copying row 12 as a whole and pasting it into your DATA sheet. So anything on row 12 will get archived.

If you add cells at the top of Sheet2, make sure you put spots for that data to appear in row 12, too, just like you've currently done for the existing data.

The only code that might need to be added would be if the new cells were mandatory. I've coded the Accept/Reject field and the CMM field as mandatory, which is why you get an error message if you try to archive with those fields empty. Did you notice that yet?

The rejection reason field is only mandatory when the accept/reject field is "Rejection". So that part of the code might need adding to. You can look in the code and see what we've done already, you may be able to tweak/expand that on your own.

Also, the part of the code that clears the fields at the top, you would have to add the new fields in as fields to be cleared at the end of the archival macro.

[insanity66]

Nevermind I figured it out. I just need to refernce it into row 12

Thanks for all of your help.

[JBeaucaire]

Good stuff. Be sure to mark this thread solved as shown prior. (post #16)