Macro/VBA needed for opening excel file

[tcw1986]

Hi,

I need to create a macro and I have no idea what I'm doing using VBA and recording doesn't really work. Can anyone help??

To help, I've attached some files. The first file (Macro template) is the file I want data pasted into. The second and third files contain the data I need.

I think I will need several macros but I want to be able to look at a cell in the macro template file (in this case cells B8, B47 and B87) which has my "Pack ID" and then open the second file which has the data. The data needs to be sorted in alphabetical order (currently A01, C01, E01 etc etc, needs to be A01, A02, A03 etc). and then paste just the data (column B) into the 'tube sent' column in the original file. It then does the same for the second data file and pastes into the 'tube received' column.

I have no experience of VBA, so I really don't know how to open the area I need to edit the VBA or enter any VBA or anything. Recording a macro at the moment does everything I want but is limited to only opening the file I use while recording the macro, where I want it to open the file specific to B8, B47, B87 etc.

cheers

[Kyle123]

Why not just rename your tube positions to those in your file i.e put a 0 in and use VLookup?

[tcw1986]

I'm wanting to search my hard drive in a specific folder for the file names in B8, B47 and B87. VLookup wouldn't do this I don't believe

[arlu1201]

Is this resolved?

[tcw1986]

No I have not been able to resolve this.

[arlu1201]

Please provide a few examples of the files names that will contain the data for Tube Received and Tube Sent and where will both these file names be saved in the macro template?

[tcw1986]

Hi,

An example filename would be XR00000008.xlsx. The file will be saved in a specific file location and the only data in that file would be the list of XR.... files. eg. C:\Documents and Settings\Tubes Sent or C:\Documents and Settings\Tubes Received

Is that what you were wanting to know?

Thanks,
Toby

[arlu1201]

In the macro template, would you be putting the entire path in any cell or just the filename? If the filename, in which cell would it be?

[tcw1986]

Hi,

It would only be the filename that would be put into the cell, I want the Macro to find that file on the harddrive based on whats in the cell.
Its actually multiple cells to be honest, I want to put the filename in the cell next to 'Rack ID' (so B9, B48, B88 etc etc) and have a button next to each of these seperate cells, which you press and accesses the macro to find the filename and populate the table below. If it's not possible to do this for multiple cells, it would just be B9 and then I'd have to use a new spreadhseet for each file I want to locate.

Cheers,
Toby

[arlu1201]

We lost the files that you attached due to some small issue in the upgrade. Can you please attach the files again?

[tcw1986]

Hi,

That's ok, I've re-attached the files.

Toby

[arlu1201]

I just needed one last clarification before i work on your issue (I know its been a while, but surely i should have a good solution this time).
You would be inputting the file name for tube Sent in B8 and for tube received in B9 right?

[tcw1986]

Hi,

Sorry for not responding sooner.

No this is not the case. The filename will only be in B8. The two files (tubes sent and tubes received spreadsheets) will be saved in two seperate folders on the hard drive, but will have the same name. This means for the tubes sent data, it will look specifically in 1 folder for that file name and will look specificially in the other folder for the file name for the tubes received data.

B9 is used for another piece of data which irrelevant.

Thanks,
Toby

[arlu1201]

I apologize for the delay. I have got the code ready for you. Try this.

Please Login or Register  to view this content.

Note: You can have as many pack IDs as you want in this one file, you just need to ensure that one pack id is placed 40 rows below the other (as i have programmed it this way). In this report, I inserted a row before the 2nd pack id, so that the 2nd pack id is at row 48, since the 1st one is on row 8.

Do let me know if you face any issues.

[tcw1986]

Thanks so much for your post and your help. I am a complete novice at this sort of thing though, and have no idea where to input this information. Once the information is in, how do I get it to act or will it work automatically?

Thanks again for your help and I really appreciate the work you have put into it.

Toby

[arlu1201]

Copy the code. Open your excel file, press Alt+F11. You will get a blank code window. On the left hand side, you will find Microsoft Excel Objects. Right click and insert -> Module. Add the code in there. Go to your main excel page. Go to View -> Macros. Run the macro that will show up in the list. Or you could insert a picture on the page and right click on it -> Assign Macro. Click on the button when you want the macro to run.

[tcw1986]

Thanks for that!
Also, if I wanted to change the file path in which the Tubes Sent and Tubes Received folders were to for example Documents and Settings\Shipments, would I simply change the following:

SentPath = "D:\Documents and Settings\DWS\DWS\Tubes Sent\"
RecPath = "D:\Documents and Settings\DWS\DWS\Tubes Received\"
'SentPath = "C:\Documents and Settings\Tubes Sent\"
'RecPath = "C:\Documents and Settings\Tubes Received\"

to

SentPath = "D:\Documents and Settings\Shipments\DWS\DWS\Tubes Sent\"
RecPath = "D:\Documents and Settings\Shipments\DWS\DWS\Tubes Received\"
'SentPath = "C:\Documents and Settings\Shipments\Tubes Sent\"
'RecPath = "C:\Documents and Settings\Shipments\Tubes Received\"

I ask only as I just tried this in case of change in the future and it was unable to locate the D:\Documents and settings....

Thanks

[arlu1201]

Oops. I had changed the file path at my end while i was testing the code. All you need is this -

Please Login or Register  to view this content.

Whenever there is a single quote (') before any line of code, that code is not processed.

[tcw1986]

That's great - makes sense!

Hopefully 1 last question. Will this macro work to open .csv files as well as .xls or .xlsx?

[arlu1201]

Yes, it depends on what you put as the file extension in the pack id field.

[tcw1986]

Ok, so when I run the macro, I have to specify the file type (eg NR00000008.csv or NR00000008.xlsx)? Can it not just recognise the file name without the extension? I ask because the tubes sent file is given to us in a .csv format and we save the returning file as a .xlsx file. Is there a way I can edit the code to look specifically for a certain file type?

Thanks again,
Toby

[arlu1201]

Yes, you can. Just change this line from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Change it in the TUBES Received section as well.

[tcw1986]

You are such a great help, thanks. Two more questions and hopefully I will be sorted as it is working a dream!

1. It appears the .csv file (Tubes sent) that I have sent to me has a space before every tube name in column B, so the two don't exactly match when comparing using an IF formula. Is there a way to remove this space?

2. For the filename of the .xlsx file which I create, I want to put the date and our initials after the tube name (e.g. NR00000008_11JAN12_TW.xlsx) is this possible? Can I change that same bit of code you mentioned above to include this as obviously the date we write will always change as too might the initials??

Sorry for being such a pain, you've done a great job which I really really appreciate your help!!! Hopefully these will be my last questions!

Thanks again!

Toby

[arlu1201]

1. To remove the spaces, there is a little longer method of using the "Trim" function on both the files (Tubes Sent & Tubes Received) through code. Instead of that, you can use the trim function directly in your excel file. You can change your IF function in column E as follows -

Please Login or Register  to view this content.

and drag down.

2. I am a little confused here. You are talking about the filename in cell B8? Or do you want to save the file containing the macro with the same name as the tubes sent name?

[tcw1986]

Thats really good thanks, I'd never heard of the TRIM function before!

For the second part, it is a little confusing. At the moment, I put the filename into B8 and it searches the TUBES SENT and TUBES RECEIVED folders in my hard drive for the files with that same name (eg NR00000008).
However what I want to do, is keep the file name in the TUBES SENT folder the same, so it would be NR00000008.csv, but then in the TUBES RECEIVED folder, name my files with the date and initials in the filename, so it would be NR00000008_11JAN12_TW.xlsx - but I still just want to type NR000000008 into cell B8. Is that possible?

Thanks again,
Toby

[arlu1201]

If i am understanding your correctly, your Tubes received files will remain the same in the Tubes received folder. You will be saving your comparison file also in the Tubes Received folder with the new name right?

[tcw1986]

Not quite. the Tubes Sent files will remain the same in the Tubes Sent folder. I want to save the Tubes Received file in the new format (e.g. NR000000008_DDMMMYY_IN) in the Tubes Received folder - where DDMMMYY eg 11JAN12 and IN could be any two letters

[arlu1201]

So do you want to save the file after its being used by the macro? What i mean to say is, do you want to first ask the macro to open the file from the folder in the old name and then resave the file?

[tcw1986]

Ah I see what you mean. No I'd actually prefer the macro to open the file with the new name. So that means it is opening files with different names but with a common part (the NR######## section)

[arlu1201]

There would be multiple such files in the folder right? So the macro would need to identify the file. How about asking the user/you to open the file required instead of opening thru the macro since the macro wouldnt know which file to pick up?

[tcw1986]

There would be multiple files in the folder, but each one would be unique, the initial part of the filename, which is put into B8 on the spreadsheet the NR00000008 or NR000000018 would be unique, its just asking to open it with the added extension.

Are you not able to have a section in the code that says you are looking for NR00000008_##???##_??.xlsx or something like that for the Tubes Recieved section? So it is always goes by whatever is in B8 and adds on 11 characters after?

It is just the plan is to have a lot of different file names in this spreadsheet, so when using the macro, you can auto-populate say 50 of the tables, opening 50 files manually would take a lot longer...

[arlu1201]

Yes, we can automate it that way by putting a wild card after the initial NR...characters.
Since you say that you would want to auto-populate about 50 tables, i can help you with an additional functionality in the code. We can have a list of the 50 or more files in the macro file somewhere and ask the code to populate cell B8 one by one with the entries in that list, open the file, update the data, do any other required steps and then close the file. Then move to the next file in the list, etc.
Would this work for you? It will take a lil time for the macro to run, maybe a couple of minutes, but you will have all the steps automated.

[tcw1986]

I don't mind having it the way it is, I'm quite happy with the structure - if we can get it so there is a wildcard after the NR######## characters in the Tubes Recieved filename, that would be great and would be enough for all that I'm needing! - how would we go about doing that?

[tcw1986]

What I meant is that you have helped a lot already - the added functionality would be great, but not essential.

[arlu1201]

Use this code -
'TUBES SENT

Please Login or Register  to view this content.

And
'TUBES RECEIVED

Please Login or Register  to view this content.

You just have to put NR00000008 or whatever in Cell b8. For tubes sent, it will take the file as is with .csv extension. For Tubes received, it will add an asterisk after 008 for the remaining characters and the extension will be .xlsx.

Do let me know if you need the added functionality...will have to make a few code changes.

[tcw1986]

This works a dream! Thank you very much!

I think any more functionality would just confuse me when I'm using it! Thanks again!