A
asxastro
I have a MASTER sheet which contains a table of records, which I want
a marco to essentially open a targeted external 'read only'
spreadsheet, copy specific cell data and paste the specific cell data
into a FINAL table located in another worksheet.
For each record information found in the table in the MASTER worksheet.
(see MASTER SHEET table)
1. Locate, open and validate if the 'READ ONLY' MS spreadsheet
'path_name' and 'worksheet' name.
FALSE Scenario
If either the 'path_name' or 'worksheet' does NOT exist, then use the
letter 'N' on the 'Extracted_Y_N' column, closed the targeted
spreadsheet and move onto the next record.
TRUE Scenario
If the 'path_name' and 'worksheet' DOES exist, then use the letter 'Y'
on the 'Extracted_Y_N' column.
Next, copy from the MASTER sheet the 'path_name' parameter from the
record and insert in column A in the FINAL sheet. This cell data will
be copied down by X number of times contingent on what number is on
Column 'F' from the MASTER table record.
Next, using the record details 'colm1', 'colm2', 'row_start' and
'records_to_copy' parameters in the record found in the MASTER file
table, the macro will copy the specific data from the open targeted
spreadsheet and place it into the 'Final' worksheet in columns B and
C.
Next, when all specific data has been copied to the FINALworksheet,
the Targeted spreadsheet will be closed.
2. Go to the next record in the MASTER table and Loop point 1 again.
Repeat this process until all records in the MASTER table records have
all been investigated or performed.
**********************************************************************************************************
'MASTER SHEET' table
path_name worksheet colm1 colm2 row_
Records_to_ Extracted
start copy_downwards _Y_N
-----------------------------------------------------------------------------------------------------------------------------------
C:\flder,A&B\J&C_A.xls sht&aa1 B D 8
4 Y
C:\flder,B&N\J&C_F.xls sht2 A C 5
7 Y
C:\abc\abc.xls sht2 A D
2 2 N
C:\flder,B&N\J&C_M.xls sht1 D G 8
5 Y
and many more records…
**********************************************************************************************************
FINAL SHEET (End result of what the 'FINAL' sheet would look like
this)
path_name Colm1_F Colm2_F
----------------------------------------------------------------------------------------
C:\flder,A&B\J&C_A.xls a a1
C:\flder,A&B\J&C_A.xls a1
C:\flder,A&B\J&C_A.xls
C:\flder,A&B\J&C_A.xls a a1
C:\flder,B&N\J&C_F.xls c
C:\flder,B&N\J&C_F.xls c c1
C:\flder,B&N\J&C_F.xls c c1
C:\flder,B&N\J&C_F.xls
C:\flder,B&N\J&C_F.xls c c1
C:\flder,B&N\J&C_F.xls c c1
C:\flder,B&N\J&C_F.xls c c1
C:\flder,B&N\J&C_F.xls c c1
C:\flder,B&N\J&C_M.xls c c
C:\flder,B&N\J&C_M.xls c c
C:\flder,B&N\J&C_M.xls s s
C:\flder,B&N\J&C_M.xls
C:\flder,B&N\J&C_M.xls s s
**********************************************************************************************************
1st TARGETED External Spreadsheet, worksheet name and targeted cells
to be copied.
-------------------------------------------------------------------
path_name : C:\flder,A&B\J&C_A.xls
Worksheet : sheet&aa1
Column B Column D
Row 8 a a1
Row 9 a1
Row 10
Row 11 a a1
2nd TARGETED External Spreadsheet, worksheet name and targeted cells
to be copied.
------------------------------------------------------------------
path_name : C:\flder,B&N\J&C_F.xls
Worksheet : sheet2
Column A Column C
Row 5 c
Row 6 c c1
Row 7 c c1
Row 8
Row 9 c c1
Row 10 c c1
Row 11 c c1
Row 12 c c1
-------------------------------------------------------------------
3rd TARGETED External Spreadsheet the path and worksheet name was Not
Valid.
-------------------------------------------------------------------
4th TARGETED External Spreadsheet, worksheet name and targeted cells
to be copied.
path_name : C:\flder,B&N\J&C_M.xls
Worksheet : sheet1
Column D Column G
Row 8 c c
Row 9 c c
Row 10 s s
Row 11
Row 12 s s
-------------------------------------------------------------------
Apreciate any help on this task, I'm a complete beginner in Excel VBA
programming and no sure if this task is doable.
MANY THANKS...
a marco to essentially open a targeted external 'read only'
spreadsheet, copy specific cell data and paste the specific cell data
into a FINAL table located in another worksheet.
For each record information found in the table in the MASTER worksheet.
(see MASTER SHEET table)
1. Locate, open and validate if the 'READ ONLY' MS spreadsheet
'path_name' and 'worksheet' name.
FALSE Scenario
If either the 'path_name' or 'worksheet' does NOT exist, then use the
letter 'N' on the 'Extracted_Y_N' column, closed the targeted
spreadsheet and move onto the next record.
TRUE Scenario
If the 'path_name' and 'worksheet' DOES exist, then use the letter 'Y'
on the 'Extracted_Y_N' column.
Next, copy from the MASTER sheet the 'path_name' parameter from the
record and insert in column A in the FINAL sheet. This cell data will
be copied down by X number of times contingent on what number is on
Column 'F' from the MASTER table record.
Next, using the record details 'colm1', 'colm2', 'row_start' and
'records_to_copy' parameters in the record found in the MASTER file
table, the macro will copy the specific data from the open targeted
spreadsheet and place it into the 'Final' worksheet in columns B and
C.
Next, when all specific data has been copied to the FINALworksheet,
the Targeted spreadsheet will be closed.
2. Go to the next record in the MASTER table and Loop point 1 again.
Repeat this process until all records in the MASTER table records have
all been investigated or performed.
**********************************************************************************************************
'MASTER SHEET' table
path_name worksheet colm1 colm2 row_
Records_to_ Extracted
start copy_downwards _Y_N
-----------------------------------------------------------------------------------------------------------------------------------
C:\flder,A&B\J&C_A.xls sht&aa1 B D 8
4 Y
C:\flder,B&N\J&C_F.xls sht2 A C 5
7 Y
C:\abc\abc.xls sht2 A D
2 2 N
C:\flder,B&N\J&C_M.xls sht1 D G 8
5 Y
and many more records…
**********************************************************************************************************
FINAL SHEET (End result of what the 'FINAL' sheet would look like
this)
path_name Colm1_F Colm2_F
----------------------------------------------------------------------------------------
C:\flder,A&B\J&C_A.xls a a1
C:\flder,A&B\J&C_A.xls a1
C:\flder,A&B\J&C_A.xls
C:\flder,A&B\J&C_A.xls a a1
C:\flder,B&N\J&C_F.xls c
C:\flder,B&N\J&C_F.xls c c1
C:\flder,B&N\J&C_F.xls c c1
C:\flder,B&N\J&C_F.xls
C:\flder,B&N\J&C_F.xls c c1
C:\flder,B&N\J&C_F.xls c c1
C:\flder,B&N\J&C_F.xls c c1
C:\flder,B&N\J&C_F.xls c c1
C:\flder,B&N\J&C_M.xls c c
C:\flder,B&N\J&C_M.xls c c
C:\flder,B&N\J&C_M.xls s s
C:\flder,B&N\J&C_M.xls
C:\flder,B&N\J&C_M.xls s s
**********************************************************************************************************
1st TARGETED External Spreadsheet, worksheet name and targeted cells
to be copied.
-------------------------------------------------------------------
path_name : C:\flder,A&B\J&C_A.xls
Worksheet : sheet&aa1
Column B Column D
Row 8 a a1
Row 9 a1
Row 10
Row 11 a a1
2nd TARGETED External Spreadsheet, worksheet name and targeted cells
to be copied.
------------------------------------------------------------------
path_name : C:\flder,B&N\J&C_F.xls
Worksheet : sheet2
Column A Column C
Row 5 c
Row 6 c c1
Row 7 c c1
Row 8
Row 9 c c1
Row 10 c c1
Row 11 c c1
Row 12 c c1
-------------------------------------------------------------------
3rd TARGETED External Spreadsheet the path and worksheet name was Not
Valid.
-------------------------------------------------------------------
4th TARGETED External Spreadsheet, worksheet name and targeted cells
to be copied.
path_name : C:\flder,B&N\J&C_M.xls
Worksheet : sheet1
Column D Column G
Row 8 c c
Row 9 c c
Row 10 s s
Row 11
Row 12 s s
-------------------------------------------------------------------
Apreciate any help on this task, I'm a complete beginner in Excel VBA
programming and no sure if this task is doable.
MANY THANKS...