VBA - is this possible?

[KieranO]

Good day,

Being particulary new to vba i'm unsure whether this is doable or not. My situation is i have a report each week i have to complete - i gather data from 2 spreadsheets that are on sharepoint, format it and add them to my own, i then sort/filter the data and take secions of it (Top 5 highest/lowest) and copy them into a email to send.

I have the middle bit set up to a point that in my spreadsheet i get the data myself, put it on a specific sheet and my code formats the data, creates a new sheet for me and copies the data over to it. So my question is do you think its possible to automate the start and the end? breaking each step down it would consist of:

• Accessing the sharepoint folder - I could potentially map the drive for this
• Organising the folder newest first - this folder is updated daily so on the day of the report i would pick the newest file
• Opening that file and copying the data i need (which is always in the same cells) over to my spreadsheet
• My code would then format it as it already does.
• Then, in a perfect world it would copy it over to a email (outlook) however this isnt a big deal.

Any advice would be appreciated

Enjoy your day

 

[Micron]

Consider msoFileDialogFilePicker for 1st two points and the first part of third bullet (opening the selected file). Seems like you already have the copying/formatting code so you could integrate or call that as a separate sub. There are tons of existing code examples on how to send sheets or workbooks in an Outlook email.
HTH

 

[KieranO]

Consider msoFileDialogFilePicker for 1st two points and the first part of third bullet (opening the selected file). Seems like you already have the copying/formatting code so you could integrate or call that as a separate sub. There are tons of existing code examples on how to send sheets or workbooks in an Outlook email.
HTH

Thankyou for giving me a direction to look into, i appreciate your help

 

[Micron]

You're welcome. Give it your best shot!