VB Script to copy in Excel Workbook 1

[mtepfer]

I need to know how to write a script that when the end user opens the excel spreadsheet, they will be prompted to select a source folder (in a specific directory)once selected the files in that folder will be copied to another static location.

 

[striveforexcelence]

can you just establish the static location for them and link the workbooks?

 

[mtepfer]

It's setup that the data is downloaded out of our entrerprise system into about 10 different excel spreadsheets, which are stored in a folder for each month (May June, July, etc)

The main workbook has the worksheets linked to each spreadsheet in a folder title Selected month. The user should be able to run the spreadsheet using whatever months data they want, that's why I am trying to get the selected month choosen copied into the selected month folder where the spreadsheet is linked to.

Hope that makes a little more sense

 

[mike1955]

It's a strange thing you are trying to do

It would be easier to use vba to edit the links to the destination folder/files based on the current month. The links can be overwritten with a concatinated string sequence in vba that makes up the destination file path.You could also put a list box in your main sheet and based on the user selection jump around the months at will.


mike

mike