J
James8309
Hi everyone,
I was wondering if anyone can help with my problem.
I have a workbook containing three worksheets; 'sheet1', 'sheet2' and
'sheet3'. And;
1. On 'sheet1' I have name of the months from B5 to AO5
- i.e. Jan - 05 to Apr - 08
2. 'sheet1' Cell A1 contains the name of the workbook "ABC123"
3. From 'sheet1" cell A6 till A25. I have codes in combination of
number and alphabet structure.
- i.e. OTO35, VOF05 etc ( 3 letters and 2 number at the end )
4. On my directory 'C:\Documents\Database' Under the Database folder
I have many different kinds of representative name folders such as
"ABC123", "DEF456", "ABB768" and so on.
5. Inside of this each name folder, It has 4 different year folders
called "2005", "2006", "2007" and "2008". Inside of these year
folders, I have excel files name in month as this "Jan 05", "Feb 05"
all the way to "Dec 05" and so on. It is same with other year folders
except the last two number digit from the name where in folder "2007",
it will have excel file name "Jan 07" ~"Dec 07".
6. On 'sheet1' cell B6 which will be underneath cell B5(Jan-05), I
need to open 'C:\Documents\Database'\ABC123\2005\Jan 05.xls' file then
do the sumif on cell A6 which contains the code "OTO35".
===> Here is the problem that I can't solve. What I can do with my
small brain in VBA is to actually tell it to open up the file under
directory 'C:\Documents\Database'\ABC123\2005\Jan 05.xls' then do
sumif of A6 on Jan 05.xls Range A:J and sum data column J, then put it
on B6.
- Is there any possible way to program in VBA in such way that when
msgbox pops up at the very start and I just type in the file name
"ABC123" or refer to cell A1 or whatever, Open up the correct file on
correct month then do a sumif automatically?
i.e.
A. if file name is ABC123 for this report then go to path 'C:\Documents
\Database'\ABC123\
if file name is DEF456 then go to path 'C:\Documents
\Database'\DEF456\
B. Then, if it is Jan-05 go to correct year folder under then open up
correct xls file. i.e. 'C:\Documents\Database'\ABC123\2005\Jan 05.xls
C. Do a sumif of A6 from Jan 05 file from Range A:J and sum data on
column J.
D. Repeat this step from Jan - 05 to Apr - 08 (B5 to AO5) then getting
results cells underneath.
I am pretty sure there will be alot better and efficient way of doing
this.
If anyone can help or give me an advice how this can be done, that
would be wonderful!
Thanks guys!!
I was wondering if anyone can help with my problem.
I have a workbook containing three worksheets; 'sheet1', 'sheet2' and
'sheet3'. And;
1. On 'sheet1' I have name of the months from B5 to AO5
- i.e. Jan - 05 to Apr - 08
2. 'sheet1' Cell A1 contains the name of the workbook "ABC123"
3. From 'sheet1" cell A6 till A25. I have codes in combination of
number and alphabet structure.
- i.e. OTO35, VOF05 etc ( 3 letters and 2 number at the end )
4. On my directory 'C:\Documents\Database' Under the Database folder
I have many different kinds of representative name folders such as
"ABC123", "DEF456", "ABB768" and so on.
5. Inside of this each name folder, It has 4 different year folders
called "2005", "2006", "2007" and "2008". Inside of these year
folders, I have excel files name in month as this "Jan 05", "Feb 05"
all the way to "Dec 05" and so on. It is same with other year folders
except the last two number digit from the name where in folder "2007",
it will have excel file name "Jan 07" ~"Dec 07".
6. On 'sheet1' cell B6 which will be underneath cell B5(Jan-05), I
need to open 'C:\Documents\Database'\ABC123\2005\Jan 05.xls' file then
do the sumif on cell A6 which contains the code "OTO35".
===> Here is the problem that I can't solve. What I can do with my
small brain in VBA is to actually tell it to open up the file under
directory 'C:\Documents\Database'\ABC123\2005\Jan 05.xls' then do
sumif of A6 on Jan 05.xls Range A:J and sum data column J, then put it
on B6.
- Is there any possible way to program in VBA in such way that when
msgbox pops up at the very start and I just type in the file name
"ABC123" or refer to cell A1 or whatever, Open up the correct file on
correct month then do a sumif automatically?
i.e.
A. if file name is ABC123 for this report then go to path 'C:\Documents
\Database'\ABC123\
if file name is DEF456 then go to path 'C:\Documents
\Database'\DEF456\
B. Then, if it is Jan-05 go to correct year folder under then open up
correct xls file. i.e. 'C:\Documents\Database'\ABC123\2005\Jan 05.xls
C. Do a sumif of A6 from Jan 05 file from Range A:J and sum data on
column J.
D. Repeat this step from Jan - 05 to Apr - 08 (B5 to AO5) then getting
results cells underneath.
I am pretty sure there will be alot better and efficient way of doing
this.
If anyone can help or give me an advice how this can be done, that
would be wonderful!
Thanks guys!!