Automate Excel Task VBA Coding? Please Help

Hi,

I have a folder on my Desktop which will constantly get excel files dropped inside it, all files will be in the same template, just different data within each file. I automatically want a formula to then be applied to each file that comes into this folder and then for this new file to be saved elsewhere and removed from this initial location.

2017-04-11_1015.png

As per the picture, the original file will be from Column A to C, I want the formula Column D to automatically be applied into this sheet as per shown in Red. Any help would be greatly appreciated.

Thank you so much.

Does this help?

Please Login or Register  to view this content.

Originally Posted by JOHN H. DAVIS

Does this help?

Please Login or Register  to view this content.

Hello,

Thanks for your prompt response, issue is I have no idea where to place that formula. I want to be able for that to apply to every new sheet which comes into that folder and then save into another folder. Hence, can you kindly assist me with this please.

Thanks.

Any further input please?

Ignore - mouse bounce.

What you want to do is not really Excels' forté... it can be done, but it means you have to have Excel running in order to watch the folder. It also means you need to have code running constantly which can impact on other work you are doing. There are ways to minimise that impact but that means the process may not be as regular or as efficient as you want ("...which will constantly get excel files dropped inside...").

If you must do this is Excel, then running it in a separate instance would be the ideal - you haven't mentioned anything about the process so no suggestion unless you explain the 'environment' this is to run in.

Thank you so much for your response, much appreciated.

I am happy to reduce the amount of time this runs in a day to improve efficiency etc. Couple times a day would be enough. Furthermore, this is just running on a standard desktop computer which will be getting used for other standard day to day tasks, like emails etc. The process we need to do is take these orders from our website via the download into a folder, manipulate them using an excel formula to determine shipping methods and then uploading it all to our shipping software which then creates these labels.

I look forward to a solution.

Thank you.

I am very grateful for all the time you put into that response, I followed your advise and did the following. See below, this shows the file/folder on the desktop:

Desktop.png

The file "Code" consists of the formula's as indicated in your previous post. The IN folder consists of the downloaded file which needs the formula to be implemented to after which it would be placed in the OUT folder.

The pictures below shows the first code that is entered. I did this by going to going to Developer/View Code and I entered it here.

First_Code.png

The below image shows the second code that you stated for me to enter. I clicked Insert/Class Module and entered it there. I thereafter saved the file as a Macro enabled file and closed it

2nd_Code.png

However, after all this. It still does not work, I must have obviously done something wrong. Can you please advise me. Apologies I am not that great with Excel. Thanks for all your support.

Using a tablet' but it seems you have put the workbook specific code in a worksheet module. Move it to the ThisWorkbook module. You will also have to save/close and reopen the workbook to run the code in the Open event (or run it manually, just trying to keep this simple).

Thanks once again for your reply, I have made this amendment and then pressed the run dialog on the sheet and also tried running the code from the Code editor section. However, nothing happens. Can you kindly look if I have labelled things correctly. I wonder if it has anything to do with the folder names I created and the paths. I did get the paths correct though as I copied them directly from the source.

Sorry once again for the million questions, I am just under a serious time pressure from work and need to get this solved. Any help will be much appreciated.

1st_Code.png

... and the mouse bounces again. Ignore

When comments are included (lines in green in the code editor), please at least read them...

Compare

Please Login or Register  to view this content.

with the changes you have made in your 2nd code.png (above).

Generally, pictures are useless (but there's always exceptions - this is one) - if you have issues with code then post the code, or preferably a sample workbook.

My apologies, thank you so much once again for all your help. I really appreciate this. It seems to work fine now. I need to make sure the "Code" Spreadsheet is open, otherwise this would not work.

You are a star! I may need some help with further formula, hopefully you can help with this.

I need to make sure the "Code" Spreadsheet is open

Of course you need to make sure it's open - otherwise how does the code run? Goes back to my initial comment that what you want to do "is not really Excels' forté"... I also mentioned that "running it in a separate instance would be the ideal" - I would strongly recommend you do that.

Thanks once again, sorry to sound daft, but what does running it in a separate instance mean?

Just start excel twice.. use 1 for your own work and the other for the scan code...

Because the scan code starts automatically and loads/unloads workbooks it could interfere with whatever you are doing so safer to keep it entirely separate.

Thank you.

Originally Posted by JOHN H. DAVIS

Does this help?

Please Login or Register  to view this content.

Can someone assist me, what does each section of this code mean?

Thanks

The guts of it is this section:

Please Login or Register  to view this content.

It's simply getting the last used row in Column C.

If you go to cell C1048576 and press End-Up Arrow the cursor will move to the first used cell it finds (working upwards - after all, it can't go down from the last row). This is the code quivalent of End-Up arrow and is used to determine the row number. This is then used to define the range in Col D that gets updated with the formula.

Thanks for that explanation, is this because Column C is the info that is being used to determine the formula in Columb D?

If so, what if there were two columns determining the formula. Ie. B and C. Would this formula change, if so, can you please assist me with what it would change to.

Thanks.

Column C was the only column referenced in your original post... If you want to calculate based on values in both Cols B & C then yes, of course the formula would change - but I've no idea what it would change to as I don;t know what calculation you want to apply.

Hello,

Thanks once again for all your help everyone, especially Cytop. I have now created all the logic behind what I need to do. I now need help to turn into the visual basic editor. In addition to the previous formula which moves the file from In Folder to Out Folder, I need the following done to the excel file. Previous IF Formula is not required, kindly assist me with the following ones:


UK Formula Order

1. Delete first 6 rows.

2. If shipping country (Column AX) does not equal GB, delete entire row

3. If column A equals +, delete the entire row

4. Set product weight (Column DF) on the last column, this will be uploaded: =IF(AND(BC8="Special Delivery",CC8<0.101),CC8,IF(AND(BC8="Special Delivery",CC8>0.1,CC8<1.001),"0.100",IF(AND(BC8="Special Delivery",CC8>1.001),"Ignore",IF(AND(BP8=1,CC8<0.101),CC8,IF(AND(BP8=1,CC8>0.1,CC8<1.001),0.1,IF(AND(BP8=1,CC8>1),"Ignore",IF(AND(BP8=2,CC8<0.101),CC8,IF(AND(BP8=2,CC8>0.1,CC8<1.001),0.1,IF(AND(BP8=2,CC8>1),"Ignore",IF(AND(BP8=3,CC8<1.001),CC8,IF(AND(BP8=3,CC8>1,CC8<10.001),1,IF(AND(BP8=3,CC8>10),"Ignore","Consult Veer"))))))))))))

5. Shipping Method (Column DE)
=IF(AND(BC8="Standard",BP8=1,C8<22.51),"RM 48 Letter",IF(AND(BC8="Standard",BP8=1,C8>22.5),"RM 48 Letter Signed For",IF(AND(BC8="Standard",BP8=2,C8<22.51),"RM 48 Large Letter",IF(AND(BC8="Standard",BP8=2,C8>22.5),"RM 48 Large Letter Signed For",IF(AND(BC8="Standard",BP8=3,C8<50.01),"RM 48 Packet",IF(AND(BC8="Standard",BP8=3,C8>50),"RM 48 Packet Signed For",IF(AND(BC8="Expedited",BP8=1),"RM 24 Letter Signed For",IF(AND(BC8="Expedited",BP8=2),"RM 24 Large Letter Signed For", IF(AND(BC8="Expedited",BP8=3),"RM 24 Packet Signed For",IF(AND(BC8="2nd Class",BP8=1,C8<22.51),"RM 48 Letter",IF(AND(BC8="2nd Class",BP8=1,C8>22.5),"RM 48 Letter Signed For",IF(AND(BC8="2nd Class",BP8=2,C8<22.51),"RM 48 Large Letter",IF(AND(BC8="2nd Class",BP8=2,C8>22.5),"RM 48 Large Letter Signed For",IF(AND(BC8="2nd Class",BP8=3,C8<50.01),"RM 48 Packet",IF(AND(BC8="2nd Class",BP8=3,C8>50),"RM 48 Packet Signed For",IF(AND(BC8="2nd Class Signed For",BP8=1),"RM 48 Letter Signed For",IF(AND(BC8="2nd Class Signed For",BP8=2),"RM 48 Large Letter Signed For", IF(AND(BC8="2nd Class Signed For",BP8=3),"RM 48 Packet Signed For",IF(AND(BC8="1st Class",BP8=1,C8<22.51),"RM 24 Letter",IF(AND(BC8="1st Class",BP8=1,C8>22.5),"RM 24 Letter Signed For",IF(AND(BC8="1st Class",BP8=2,C8<22.51),"RM 24 Large Letter",IF(AND(BC8="1st Class",BP8=2,C8>22.5),"RM 24 Large Letter Signed For",IF(AND(BC8="1st Class",BP8=3,C8<50.01),"RM 24 Packet",IF(AND(BC8="1st Class",BP8=3,C8>50),"RM 24 Packet Signed",IF(AND(BC8="1st Class Signed For",BP8=1),"RM 24 Letter Signed For",IF(AND(BC8="1st Class Signed For",BP8=2),"RM 24 Large Letter Signed For", IF(AND(BC8="1st Class Signed For",BP8=3),"RM 48 Packet Signed For",IF(AND(BC8="Royal Mail Second Class",BP8=1,C8<22.51),"RM 48 Letter",IF(AND(BC8="Royal Mail Second Class",BP8=1,C8>22.5),"RM 48 Letter Signed For",IF(AND(BC8="Royal Mail Second Class",BP8=2,C8<22.51),"RM 48 Large Letter",IF(AND(BC8="Royal Mail Second Class",BP8=2,C8>22.5),"RM 48 Large Letter Signed For",IF(AND(BC8="Royal Mail Second Class",BP8=3,C8<50.01),"RM 48 Packet",IF(AND(BC8="Royal Mail Second Class",BP8=3,C8>50),"RM 48 Packet Signed",IF(BC8="Special Delivery","Special Delivery","Consult Veer"))))))))))))))))))))))))))))))))))


Furthermore, I have also attached the CSV File where this needs to be worked on. I would be extremely grateful for all your help in doing this.

Thanks in advance.

Can someone please help me.

Thanks.

I have figured out point 1) 2) 3). I just need help on 4 and 5 Please. Thanks.

Thanks a lot for your help!