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...
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.
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
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?
What I meant is that you have helped a lot already - the added functionality would be great, but not essential.
Use this code -
'TUBES SENTAndWorkbooks.Open SentPath & ThisWorkbook.Worksheets(1).Range("B" & i).Value & ".csv"
'TUBES RECEIVED
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.Workbooks.Open RecPath & ThisWorkbook.Worksheets(1).Range("B" & i).Value & "*.xlsx"
Do let me know if you need the added functionality...will have to make a few code changes.
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
This works a dream! Thank you very much!
I think any more functionality would just confuse me when I'm using it! Thanks again!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks