[FONT=Times New Roman, serif]Hello Excel Experts
I am a SAS user and not very familiar with VB. But I have to perform some tasks on VB as I think it will be the fastest way of doing it instead of using a DDE (dynamic data exchange process in SAS).
The Steps I need to perform such as copy paste, replace aren’t too hard to figure out. But here is the kicker I need this code to run through a loop. There are about 1000 or so workbooks in a folder, all named in a certain naming convention. I need to the code to [FONT=Times New Roman, serif]copy a sheet from one Workbook put it into a new workbook and replace cell values with the file name that the loop finds so that the cell will reference the files found in the loop and then save it under another naming convention. I know this is very complicated, so any help is greatly appreciated...[/FONT]
Here is exactly what I need to get done
I have a Workbook called “GenericImportSheet” and a sheet within that is called “ImportSheet”. This sheet has generic cell references, e.g. [FONT=Arial, sans-serif]XXX!$D$10[/FONT]
I basically need the code to replace XXX with the file path and file name "='G:\AE\TEST\[Filename the LOOP finds in the folder location.XLS]Sheetname of that file'"
Step 1: Start the loop. Go into location 'G:\AE\TEST\ - there are going to be about 1000 files in here.
Step 2: I need to create a new Workbook and put the “ImportSheet” from the [FONT=Times New Roman, serif]“GenericImportSheet” workbook [/FONT]in to it and delete all the other sheets (i.e. Sheets 1,2,3)
Step 2: I need to rename the Worksheet as “Import_SHEETNAME of each of files the loop finds” (i.e. Import_ AGR Section I)
Step 3: I need to replace XXX in the new workbook with ='G:\AE\TEST\[File name of the file the LOOP finds.XLS] and Sheetname' – this would make the reference valid (e.g. ='G:\AE\TEST\[AGR Section I.XLS]AGR Section I'
Step 4: Save this new workbook in a new location with the same name as the sheet (i.e. Import_ AGR Section I)
Step 5: close this workbook and go to the next file – repeat steps.
I know this is super complicated. But I am going to have about 1000 or maybe more workbooks to deal with and I don’t want to do this manually. I know there should be some way to do this. Any help will be great. If only part of this loop can be performed that’s ok too. That will save me a lot of time and effort.
thank you in advance to one and all
[/FONT]
I am a SAS user and not very familiar with VB. But I have to perform some tasks on VB as I think it will be the fastest way of doing it instead of using a DDE (dynamic data exchange process in SAS).
The Steps I need to perform such as copy paste, replace aren’t too hard to figure out. But here is the kicker I need this code to run through a loop. There are about 1000 or so workbooks in a folder, all named in a certain naming convention. I need to the code to [FONT=Times New Roman, serif]copy a sheet from one Workbook put it into a new workbook and replace cell values with the file name that the loop finds so that the cell will reference the files found in the loop and then save it under another naming convention. I know this is very complicated, so any help is greatly appreciated...[/FONT]
Here is exactly what I need to get done
I have a Workbook called “GenericImportSheet” and a sheet within that is called “ImportSheet”. This sheet has generic cell references, e.g. [FONT=Arial, sans-serif]XXX!$D$10[/FONT]
I basically need the code to replace XXX with the file path and file name "='G:\AE\TEST\[Filename the LOOP finds in the folder location.XLS]Sheetname of that file'"
Step 1: Start the loop. Go into location 'G:\AE\TEST\ - there are going to be about 1000 files in here.
Step 2: I need to create a new Workbook and put the “ImportSheet” from the [FONT=Times New Roman, serif]“GenericImportSheet” workbook [/FONT]in to it and delete all the other sheets (i.e. Sheets 1,2,3)
Step 2: I need to rename the Worksheet as “Import_SHEETNAME of each of files the loop finds” (i.e. Import_ AGR Section I)
Step 3: I need to replace XXX in the new workbook with ='G:\AE\TEST\[File name of the file the LOOP finds.XLS] and Sheetname' – this would make the reference valid (e.g. ='G:\AE\TEST\[AGR Section I.XLS]AGR Section I'
Step 4: Save this new workbook in a new location with the same name as the sheet (i.e. Import_ AGR Section I)
Step 5: close this workbook and go to the next file – repeat steps.
I know this is super complicated. But I am going to have about 1000 or maybe more workbooks to deal with and I don’t want to do this manually. I know there should be some way to do this. Any help will be great. If only part of this loop can be performed that’s ok too. That will save me a lot of time and effort.
thank you in advance to one and all
[/FONT]
