Hi,

I'd like to use ExecuteExcel4Macro to help me get data from closed workbooks
but I'm definitely a novice when it comes to VB. I have a folder with a lot
of workbooks I need to pull data from and the data is all in the same places,
meaning, for example, on the UPLOAD tab, Row 2, Cells A-AA, but I only need
data from some of the workbooks in the folder. I have a list of job names and
for every job name there is a workbook so I somehow need to do-while for the
list of job names that I can put in a column. For example, I have data I can
paste into Excel that looks like this:

QC Record : Job Name : Listing Count : Date
201002-18485-QC : HD-11311-TA031110 : 263 : 3/15/10
201003-15737-QC : SHT-100312 : 103 : 3/14/10
201002-18327-QC : 032110Mag : 246 : 3/17/10
201001-18510-QC: TSA-100321 : 411 : 3/15/10

For every job name there exists a file called, for example,
\\de-mt1\clients\QC Reports\Proofs Adjusted\HD-100311-TA031110 proof
adjusted.xls where HD-100311-TA031110 is the job name.

I need help with the VB in ExecuteExcel4Macro at two places: 1) where it
opens the files as I described above and 2) I'm not sure how to only pull the
cells I need data for.

Can you help?

---
Jen

I would suggest a different approach. See Ron de Bruin's ADO page, I think
the example file will cover what you are looking for

http://www.rondebruin.nl/ado.htm

Regards,
Peter T

"cmjat" wrote in message
...
Hi,

I'd like to use ExecuteExcel4Macro to help me get data from closed
workbooks
but I'm definitely a novice when it comes to VB. I have a folder with a
lot
of workbooks I need to pull data from and the data is all in the same
places,
meaning, for example, on the UPLOAD tab, Row 2, Cells A-AA, but I only
need
data from some of the workbooks in the folder. I have a list of job names
and
for every job name there is a workbook so I somehow need to do-while for
the
list of job names that I can put in a column. For example, I have data I
can
paste into Excel that looks like this:

QC Record : Job Name : Listing Count : Date
201002-18485-QC : HD-11311-TA031110 : 263 : 3/15/10
201003-15737-QC : SHT-100312 : 103 : 3/14/10
201002-18327-QC : 032110Mag : 246 : 3/17/10
201001-18510-QC: TSA-100321 : 411 : 3/15/10

For every job name there exists a file called, for example,
\\de-mt1\clients\QC Reports\Proofs Adjusted\HD-100311-TA031110 proof
adjusted.xls where HD-100311-TA031110 is the job name.

I need help with the VB in ExecuteExcel4Macro at two places: 1) where it
opens the files as I described above and 2) I'm not sure how to only pull
the
cells I need data for.

Can you help?

---
Jen

My understand is ADO doesn't work with mixed data types and all my data is
mixed.

Job Name
HD-11311-TA031110
SHT-100312
032110Mag
TSA-100321
--
Jen


"Peter T" wrote:

I would suggest a different approach. See Ron de Bruin's ADO page, I think
the example file will cover what you are looking for

http://www.rondebruin.nl/ado.htm

Regards,
Peter T

"cmjat" wrote in message
...
Hi,

I'd like to use ExecuteExcel4Macro to help me get data from closed
workbooks
but I'm definitely a novice when it comes to VB. I have a folder with a
lot
of workbooks I need to pull data from and the data is all in the same
places,
meaning, for example, on the UPLOAD tab, Row 2, Cells A-AA, but I only
need
data from some of the workbooks in the folder. I have a list of job names
and
for every job name there is a workbook so I somehow need to do-while for
the
list of job names that I can put in a column. For example, I have data I
can
paste into Excel that looks like this:

QC Record : Job Name : Listing Count : Date
201002-18485-QC : HD-11311-TA031110 : 263 : 3/15/10
201003-15737-QC : SHT-100312 : 103 : 3/14/10
201002-18327-QC : 032110Mag : 246 : 3/17/10
201001-18510-QC: TSA-100321 : 411 : 3/15/10

For every job name there exists a file called, for example,
\\de-mt1\clients\QC Reports\Proofs Adjusted\HD-100311-TA031110 proof
adjusted.xls where HD-100311-TA031110 is the job name.

I need help with the VB in ExecuteExcel4Macro at two places: 1) where it
opens the files as I described above and 2) I'm not sure how to only pull
the
cells I need data for.

Can you help?

---
Jen


.

I take it you didn't try it!

Regards,
Peter T

"cmjat" wrote in message
...
My understand is ADO doesn't work with mixed data types and all my data is
mixed.

Job Name
HD-11311-TA031110
SHT-100312
032110Mag
TSA-100321
--
Jen


"Peter T" wrote:

I would suggest a different approach. See Ron de Bruin's ADO page, I
think
the example file will cover what you are looking for

http://www.rondebruin.nl/ado.htm

Regards,
Peter T

"cmjat" wrote in message
...
Hi,

I'd like to use ExecuteExcel4Macro to help me get data from closed
workbooks
but I'm definitely a novice when it comes to VB. I have a folder with a
lot
of workbooks I need to pull data from and the data is all in the same
places,
meaning, for example, on the UPLOAD tab, Row 2, Cells A-AA, but I only
need
data from some of the workbooks in the folder. I have a list of job
names
and
for every job name there is a workbook so I somehow need to do-while
for
the
list of job names that I can put in a column. For example, I have data
I
can
paste into Excel that looks like this:

QC Record : Job Name : Listing Count : Date
201002-18485-QC : HD-11311-TA031110 : 263 : 3/15/10
201003-15737-QC : SHT-100312 : 103 : 3/14/10
201002-18327-QC : 032110Mag : 246 : 3/17/10
201001-18510-QC: TSA-100321 : 411 : 3/15/10

For every job name there exists a file called, for example,
\\de-mt1\clients\QC Reports\Proofs Adjusted\HD-100311-TA031110 proof
adjusted.xls where HD-100311-TA031110 is the job name.

I need help with the VB in ExecuteExcel4Macro at two places: 1) where
it
opens the files as I described above and 2) I'm not sure how to only
pull
the
cells I need data for.

Can you help?

---
Jen


.

You're right, I didn't but I did some more reading and I see the error of my
ways. Unfortunately this isn't high on the list of priorities and I've been
having trouble actually getting to the work.

I downloaded Ron de Bruin's merge sample and I'm wondering how, instead of
having the function Get_File_Names fill an array with the filenames if I can
preload this information since I will know the list of filename I will want
to pull data from and the folder will have more files than the subset of ones
I want.

Any ideas? I'm a VB novice so I can see what the code is generally doing and
make minor modifications but I don't think I could write anything meaningful
from scratch.

Thanks!
--
Jen


"Peter T" wrote:

I take it you didn't try it!

Regards,
Peter T

"cmjat" wrote in message
...
My understand is ADO doesn't work with mixed data types and all my data is
mixed.

Job Name
HD-11311-TA031110
SHT-100312
032110Mag
TSA-100321
--
Jen


"Peter T" wrote:

I would suggest a different approach. See Ron de Bruin's ADO page, I
think
the example file will cover what you are looking for

http://www.rondebruin.nl/ado.htm

Regards,
Peter T

"cmjat" wrote in message
...
Hi,

I'd like to use ExecuteExcel4Macro to help me get data from closed
workbooks
but I'm definitely a novice when it comes to VB. I have a folder with a
lot
of workbooks I need to pull data from and the data is all in the same
places,
meaning, for example, on the UPLOAD tab, Row 2, Cells A-AA, but I only
need
data from some of the workbooks in the folder. I have a list of job
names
and
for every job name there is a workbook so I somehow need to do-while
for
the
list of job names that I can put in a column. For example, I have data
I
can
paste into Excel that looks like this:

QC Record : Job Name : Listing Count : Date
201002-18485-QC : HD-11311-TA031110 : 263 : 3/15/10
201003-15737-QC : SHT-100312 : 103 : 3/14/10
201002-18327-QC : 032110Mag : 246 : 3/17/10
201001-18510-QC: TSA-100321 : 411 : 3/15/10

For every job name there exists a file called, for example,
\\de-mt1\clients\QC Reports\Proofs Adjusted\HD-100311-TA031110 proof
adjusted.xls where HD-100311-TA031110 is the job name.

I need help with the VB in ExecuteExcel4Macro at two places: 1) where
it
opens the files as I described above and 2) I'm not sure how to only
pull
the
cells I need data for.

Can you help?

---
Jen


.



.

If I follow the function Get_File_Names would appear to be a function in Ron
de Bruins addin RDBMerge.xla. But the password is locked so I guess you are
referring to some other file I am not aware of. Advise which file and link
you are referring to.

Regards,
Peter T


"cmjat" wrote in message
...
You're right, I didn't but I did some more reading and I see the error of
my
ways. Unfortunately this isn't high on the list of priorities and I've
been
having trouble actually getting to the work.

I downloaded Ron de Bruin's merge sample and I'm wondering how, instead of
having the function Get_File_Names fill an array with the filenames if I
can
preload this information since I will know the list of filename I will
want
to pull data from and the folder will have more files than the subset of
ones
I want.

Any ideas? I'm a VB novice so I can see what the code is generally doing
and
make minor modifications but I don't think I could write anything
meaningful
from scratch.

Thanks!
--
Jen


"Peter T" wrote:

I take it you didn't try it!

Regards,
Peter T

"cmjat" wrote in message
...
My understand is ADO doesn't work with mixed data types and all my data
is
mixed.

Job Name
HD-11311-TA031110
SHT-100312
032110Mag
TSA-100321
--
Jen


"Peter T" wrote:

I would suggest a different approach. See Ron de Bruin's ADO page, I
think
the example file will cover what you are looking for

http://www.rondebruin.nl/ado.htm

Regards,
Peter T

"cmjat" wrote in message
...
Hi,

I'd like to use ExecuteExcel4Macro to help me get data from closed
workbooks
but I'm definitely a novice when it comes to VB. I have a folder
with a
lot
of workbooks I need to pull data from and the data is all in the
same
places,
meaning, for example, on the UPLOAD tab, Row 2, Cells A-AA, but I
only
need
data from some of the workbooks in the folder. I have a list of job
names
and
for every job name there is a workbook so I somehow need to do-while
for
the
list of job names that I can put in a column. For example, I have
data
I
can
paste into Excel that looks like this:

QC Record : Job Name : Listing Count : Date
201002-18485-QC : HD-11311-TA031110 : 263 : 3/15/10
201003-15737-QC : SHT-100312 : 103 : 3/14/10
201002-18327-QC : 032110Mag : 246 : 3/17/10
201001-18510-QC: TSA-100321 : 411 : 3/15/10

For every job name there exists a file called, for example,
\\de-mt1\clients\QC Reports\Proofs Adjusted\HD-100311-TA031110 proof
adjusted.xls where HD-100311-TA031110 is the job name.

I need help with the VB in ExecuteExcel4Macro at two places: 1)
where
it
opens the files as I described above and 2) I'm not sure how to only
pull
the
cells I need data for.

Can you help?

---
Jen


.



.