Prev: DoubleClick to return SeriesIndex & PointIndex
Next: File name being changed
From: cmjat on 29 Mar 2010 09:44
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
From: Peter T on 29 Mar 2010 10:13
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" <cmjat(a)discussions.microsoft.com> wrote in message
news:A0540903-185F-42B6-96B1-A3B156AC3AC8(a)microsoft.com...
> 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


From: cmjat on 2 Apr 2010 09:54
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" <cmjat(a)discussions.microsoft.com> wrote in message
> news:A0540903-185F-42B6-96B1-A3B156AC3AC8(a)microsoft.com...
> > 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
>
>
> .
>
From: Peter T on 2 Apr 2010 10:48
I take it you didn't try it!

Regards,
Peter T

"cmjat" <cmjat(a)discussions.microsoft.com> wrote in message
news:ECA8DE98-F2F7-491A-82E3-9B4EB8889121(a)microsoft.com...
> 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" <cmjat(a)discussions.microsoft.com> wrote in message
>> news:A0540903-185F-42B6-96B1-A3B156AC3AC8(a)microsoft.com...
>> > 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
>>
>>
>> .
>>


From: cmjat on 5 Apr 2010 11:06
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" <cmjat(a)discussions.microsoft.com> wrote in message
> news:ECA8DE98-F2F7-491A-82E3-9B4EB8889121(a)microsoft.com...
> > 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" <cmjat(a)discussions.microsoft.com> wrote in message
> >> news:A0540903-185F-42B6-96B1-A3B156AC3AC8(a)microsoft.com...
> >> > 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
> >>
> >>
> >> .
> >>
>
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: DoubleClick to return SeriesIndex & PointIndex
Next: File name being changed