Advise on using multiple instances of network files based on customers

[reubendayal]

Hi All,

I have a macro we use to copy several document templates based on the company we are assisting. So for now it has worked just fine by using several IF statements based on each company. Here's an example:

If Employer = "Apple" Then

    SFolder = "S:\Network Location\Company Folders\Cases\"
    EmployerTempLink = "S:\Network Location\Company Folders\Cases\Templates\"
    EmployerQstName = "Questionnaire.xlsx"
    EmployerDecName = "Declaration.pdf"
    POAlink = "S:\Network Location\Company Folders\Cases\"
    POAName = "Letter.pdf"
    Apple = True

ElseIf Employer = "Orange" Then

… (continues like this for 15 different companies)

Now we have 20 more companies to add that just makes this code that much more longer with this approach.

Secondly, every time a team member changes the file location/name of the network folders even slightly it breaks the code.

So I've been wondering if I must simply put these network address in a common excel sheet so the team members can keep that updated if there are any modifications required. Also, it makes the updating of names less complicated for non-programmers. The obvious fact remains that if anyone mistakenly deletes the excel file or changes things in an incorrect manner, then the code breaks again.

I've also thought that perhaps the above code can be made in to a function to prevent the same lines of code being repeated each time. But I do not have the skills to put that together.

Any advice will be much appreciated!

Thank you.

 

[reubendayal]

Hi All,

I have a macro we use to copy several document templates based on the company we are assisting. So for now it has worked just fine by using several IF statements based on each company. Here's an example:

If Employer = "Apple" Then

    SFolder = "S:\Network Location\Company Folders\Cases\"
    EmployerTempLink = "S:\Network Location\Company Folders\Cases\Templates\"
    EmployerQstName = "Questionnaire.xlsx"
    EmployerDecName = "Declaration.pdf"
    POAlink = "S:\Network Location\Company Folders\Cases\"
    POAName = "Letter.pdf"
    Apple = True

ElseIf Employer = "Orange" Then

… (continues like this for 15 different companies)

Now we have 20 more companies to add that just makes this code that much more longer with this approach.

Secondly, every time a team member changes the file location/name of the network folders even slightly it breaks the code.

So I've been wondering if I must simply put these network address in a common excel sheet so the team members can keep that updated if there are any modifications required. Also, it makes the updating of names less complicated for non-programmers. The obvious fact remains that if anyone mistakenly deletes the excel file or changes things in an incorrect manner, then the code breaks again.

I've also thought that perhaps the above code can be made in to a function to prevent the same lines of code being repeated each time. But I do not have the skills to put that together.

Any advice will be much appreciated!

Thank you.

Here's my idea on how I would use the excel sheet to store all the company and documents related location. First based on the 'Employer' name we receive via a user form, find corresponding Company name in the excel sheet. Let us say the column 'B' in the name of the employers (just as they are written in the network drive). Then in each corresponding column within the same row, provide the location data for templates - EmployerQstName, EmployerDecName, POAlink and POAName.

I hope this gives you a better picture of what I am trying to achieve.

thanks again

 

[AlphonseG]

Hi All,

I have a macro we use to copy several document templates based on the company we are assisting. So for now it has worked just fine by using several IF statements based on each company. Here's an example:

If Employer = "Apple" Then

    SFolder = "S:\Network Location\Company Folders\Cases\"
    EmployerTempLink = "S:\Network Location\Company Folders\Cases\Templates\"
    EmployerQstName = "Questionnaire.xlsx"
    EmployerDecName = "Declaration.pdf"
    POAlink = "S:\Network Location\Company Folders\Cases\"
    POAName = "Letter.pdf"
    Apple = True

ElseIf Employer = "Orange" Then

… (continues like this for 15 different companies)

Now we have 20 more companies to add that just makes this code that much more longer with this approach.

Secondly, every time a team member changes the file location/name of the network folders even slightly it breaks the code.

So I've been wondering if I must simply put these network address in a common excel sheet so the team members can keep that updated if there are any modifications required. Also, it makes the updating of names less complicated for non-programmers. The obvious fact remains that if anyone mistakenly deletes the excel file or changes things in an incorrect manner, then the code breaks again.

I've also thought that perhaps the above code can be made in to a function to prevent the same lines of code being repeated each time. But I do not have the skills to put that together.

Any advice will be much appreciated!

Thank you.

Seems to me you should be using a database management solution.

 

[reubendayal]

Seems to me you should be using a database management solution.

Thanks AlphonseG.

You're right this should rather be in a database management solution. But firstly the team I am working with are have very limited understanding and courage towards trying anything tech. Besides, I do not have the time and perhaps all required skills to rebuild the entire code in to a database management solution.

Therefore, solving the above issues within this code is what I am going with. So any further help on that will be highly appreciated.

 

[AlphonseG]

Of course, it is a business decision as to where your time is best spent. Whether it is spent performing the tasks that you are good at, or trying to patch something that should be done by someone with the proper skills and training. I have a client (over 20 years now) who had taken a couple of classes in MS Access. He decided that he could make more money running his business, than he would save by not using a professional developer.

 

[reubendayal]

Of course, it is a business decision as to where your time is best spent. Whether it is spent performing the tasks that you are good at, or trying to patch something that should be done by someone with the proper skills and training. I have a client (over 20 years now) who had taken a couple of classes in MS Access. He decided that he could make more money running his business, than he would save by not using a professional developer.

Dear AphonseG,

Honestly none of your responses have been of any help. If you’re frustrated with your customer situation it is unfair to be slamming others with such a prejudice!

Isnt it the idea of such forums to be a place where people help each other out as some know more about the subject than others?

 

[AlphonseG]

Dear AphonseG,

Honestly none of your responses have been of any help. If you’re frustrated with your customer situation it is unfair to be slamming others with such a prejudice!

Isnt it the idea of such forums to be a place where people help each other out as some know more about the subject than others?

Don't know where you are getting any idea of frustration, slamming or prejudice, as I expressed neither. Guess you didn't quite understand my point. I'm merely suggesting that you might be better off in the long run by going in a different direction.

 

[Diane Poremsky]

You're putting these values in variables, so it should be as condensed as possible (or very close) - the code that uses these variables can be in a function or separate macro or just lower in the macro.

If you start the code in outlook, you can't avoid a ton of If's - one per company, but you could convert it to an array - if the variables are in a CSV or they are filed by company name.

If you start from a contact or task, the values could be in custom fields or the body and the macro reads them.

If you start from a spreadsheet, you can read the values in the row.

I would tell people "Do not move files".... and if the POAlink is not always in the cases folder, move it in. If you need different files for each company, I would arrange by company name - either folders or filename have the company name. Then you can use this format -
EmployerQstName = Employer & "_Questionnaire.xlsx"
or
EmployerQstName = SFolder & Employer & "\Questionnaire.xlsx"

If sfolder is always the same - set that before the If starts so you only need to set it once. Also easier to update if the location changes.

If Employer = "Apple" Then
SFolder = "S:\Network Location\Company Folders\Cases\"
EmployerTempLink = SFolder & "Templates\" - always in sfolder?
EmployerQstName = "Questionnaire.xlsx"
EmployerDecName = "Declaration.pdf"
POAlink = "S:\Network Location\Company Folders\Cases\" - always in sFolder?
POAName = "Letter.pdf"
Apple = True
Goto codetofinish ' if using a function or separate macro, call it here
else if....
' next company
end if


codetofinish:
' do whatever with the variables

 

[reubendayal]

Thank you, Diane.

This is just amazing as I was thinking myself on the same lines of code:

EmployerQstName = Employer & "_Questionnaire.xlsx"
or
EmployerQstName = SFolder & Employer & "\Questionnaire.xlsx"

I was also planning on using the fixed If sfolder location. And also fix the locations for each company's template documents in the company's subfolder - for example - "S:\Network Location\Company Folders\" & "Employer Documents"

I had'nt thought or even know it was possible to use a contact or task, where the values could be in custom fields or the body and the macro reads them.

I've also told the team on several occasions to not rename/move/mess with the folder structure on the network drive. But a slip happens from time to time.

The key aspect is that the macro must start in Outlook. As it is an initiation email from the customer I use which is currently selected (or a selection of emails), and pull data from it and partly from a userform completed by the team member at the start of the macro, to create a new case. During this process the macro creates network and outlook folders for the case, copies over templates to the network folder after renaming them, and moves the email selection to the case folder, enters details in an access databased and one or more excel sheets based on the customer account, etc. Therefore, for the team it is easiest to use the macro from within Outlook.

One additional aim from creating the excel sheet is that I am trying to make it smart by adding a macro to grab the customer account path and outlook folder information in to the sheet. This will also counter the issue of the team changing a folder name. And that way it can be possible to find all relevant details in the same row of the customer. For example, from the company name, network folder, outlook folder, Invoicing, sheet name or location on the network folders, etc.

I am unsure of the code on finding the row reference in the excel that has the matching Company name in the Company column and select it. As then I should be able to use the target offset function to grab the other corresponding information on the company.

I hope I've described my intent with the excel sheet approach.

Thank you.