dynamic reference to data in multiple closed workbooks

[cmjat]

Hi, I'm working with a job list (generated out of a different program) and
data in associated closed workbooks. For example, the job list has names like
HD-100311-TA031110, SHT-100312, 032110Mag and I can generate a list of these
jobs along with other information I need. For every job there's an associated
workbook and, without opening the workbook I want to pull, for example,
'[\\de-mt1\clients\QC Reports\Proofs Adjusted\HD-100311-TA031110 proof
adjusted.xls]'Upload'!A$2 where the only information that will change is the
job name. I'm novice enough not to know how to do this even if the workbook
is open using the INDIRECT function.

My current workbook has two tabs, one where I paste the job list and other
details straight from my export. The second tab then references the first
tab, then I want to pull data from the closed workbooks. (I have to make sure
things like the listing counts match, for example.) The bulk of what I need
to do is get data from these closed workbooks. The number of jobs is very
dynamic and will probably be at least 50 most of the time.

In reading postings I found references to Harlan Grove's PULL function a
supposedly faster one from Wilson So called IndirectEx but both are way over
my head. Figuring this out will save tons of manual effort every week so it's
worth me trying to figure it out. Can you help?

 

[John Bundy]

I didn't really follow all of that, especially where the job list comes from.
But you said the data from closed workbooks is the most important part, so
here is the code to get all data from a specific sheet in a closed workbook
and dump it in a sheet in the current workbook. Create a reference
(Tools->References) to Microsoft ActiveX Data Objects 2.x Library (whichever
you have, mine is 2.8).

Sub getClosedWBdata()
Dim ConString As String
Dim strSQL As String
Dim DBPATH As String
Dim recordset As New ADODB.recordset
'set the file path here
DBPATH = "D:\Data\mydata\My Documents\house.xls"
ConString = "Provider=Microsoft.jet.oledb.4.0;" & _
"Data Source=" & DBPATH & ";" & _
"extended Properties=Excel 8.0;"
'This is where you get the data, Sheet1 is the name of the sheet
'the $ is required. If the sheet was named data, you would use [Data$]
strSQL = "SELECT * FROM [Sheet1$]"
Set recordset = New ADODB.recordset
'the calls are all 1 line
Call recordset.Open(strSQL, ConString, adOpenForwardOnly, adLockReadOnly,
CommandTypeEnum.adCmdText)
'this is where the data is dumped
Call Sheets("Sheet1").Range("A2").CopyFromRecordset(recordset)

Set recordset = Nothing
End Sub

 

[cmjat]

Hi John,

Sorry, I think I didn't explain the problem well. I'm new to this company
and currently there are two functions to one specific operations area - one
is to do the work and the second is to QC the work and the QCs are pretty
extensive. Currently the work queue is listed by job name in salesforce so
that's where the new jobs are entered, a pool of people work on the jobs and
then report the work is done. Once a job is done another entire group of
people QC the work the first group did. Basically the queue of job is ever
changing so the first group just keeps looking for new jobs to work on and
the QC team keeps looking for jobs to QC. The QCs are recorded 100% in Excel
- one job per workbook. Each workbook has at least 5 worksheets but all the
information I need is exactly the same place on a couple tabs, always the
named the same thing. Then my boss has been manually consolidating the QC
results by looking in salesforce to see what jobs were done (the job names
are unique so no two will ever be repeated), opens every associated workbook,
basically copies and pastes the QC results in a CSV file which he then
uploads to salesforce. Once all the QCs are done for the week, he then
exports the data out again so he can chart it. It's clear in the long term
they're trying to do more and more in salesforce but for the time being it's
a completely time intensive process so what I'm proposing is to have Excel do
more of the heavy lifting. I can export the list of jobs for the previous
week from salesforce and I want to drop that data into Excel then have it
programmatically go pull the data from the fields I need in the associated
workbooks so I can simply create a macro to create the CSV file which can
then be uploaded.

If I lost you again the important thing is I have a list that looks
something like

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
....

Since I know the job name I know there's a file that exists in a folder
called \\de-mt1\clients\QC Reports\Proofs Adjusted\. The file will be called,
for example, \\de-mt1\clients\QC Reports\Proofs Adjusted\HD-100311-TA031110
proof adjusted.xls where HD-100311-TA031110 is the only piece of information
that will ever change and it will always be the job name. From the "Upload"
tab I need to start grabbing the data from cell A2, B2, C2, D2, and so on.

List of job names is 100% dynamic and the list varies in length from
probably 50 to hundreds in their peak season.

Is that more helpful?
--
Jen

I didn't really follow all of that, especially where the job list comes from.
But you said the data from closed workbooks is the most important part, so
here is the code to get all data from a specific sheet in a closed workbook
and dump it in a sheet in the current workbook. Create a reference
(Tools->References) to Microsoft ActiveX Data Objects 2.x Library (whichever
you have, mine is 2.8).

Sub getClosedWBdata()
Dim ConString As String
Dim strSQL As String
Dim DBPATH As String
Dim recordset As New ADODB.recordset
'set the file path here
DBPATH = "D:\Data\mydata\My Documents\house.xls"
ConString = "Provider=Microsoft.jet.oledb.4.0;" & _
"Data Source=" & DBPATH & ";" & _
"extended Properties=Excel 8.0;"
'This is where you get the data, Sheet1 is the name of the sheet
'the $ is required. If the sheet was named data, you would use [Data$]
strSQL = "SELECT * FROM [Sheet1$]"
Set recordset = New ADODB.recordset
'the calls are all 1 line
Call recordset.Open(strSQL, ConString, adOpenForwardOnly, adLockReadOnly,
CommandTypeEnum.adCmdText)
'this is where the data is dumped
Call Sheets("Sheet1").Range("A2").CopyFromRecordset(recordset)

Set recordset = Nothing
End Sub
--
-John http://www.jmbundy.blogspot.com/
Please rate when your question is answered to help us and others know what
is helpful.

Hi, I'm working with a job list (generated out of a different program) and
data in associated closed workbooks. For example, the job list has names like
HD-100311-TA031110, SHT-100312, 032110Mag and I can generate a list of these
jobs along with other information I need. For every job there's an associated
workbook and, without opening the workbook I want to pull, for example,
'[\\de-mt1\clients\QC Reports\Proofs Adjusted\HD-100311-TA031110 proof
adjusted.xls]'Upload'!A$2 where the only information that will change is the
job name. I'm novice enough not to know how to do this even if the workbook
is open using the INDIRECT function.

My current workbook has two tabs, one where I paste the job list and other
details straight from my export. The second tab then references the first
tab, then I want to pull data from the closed workbooks. (I have to make sure
things like the listing counts match, for example.) The bulk of what I need
to do is get data from these closed workbooks. The number of jobs is very
dynamic and will probably be at least 50 most of the time.

In reading postings I found references to Harlan Grove's PULL function a
supposedly faster one from Wilson So called IndirectEx but both are way over
my head. Figuring this out will save tons of manual effort every week so it's
worth me trying to figure it out. Can you help?