dynamic references to content in closed Excel workbooks

[cmjat]

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

 

[Tim Williams]

Compiled but not tested....

Tim

'*********************************************
Option Explicit

Const RPTS_ROOT As String = "\\de-mt1\clients\QC Reports\Proofs Adjusted\"

Sub GetReportinfo()

Dim shtListing As Excel.Worksheet
Dim wbReport As Excel.Workbook
Dim shtUpld As Excel.Worksheet
Dim rw As Long, jobName As String
Dim rptFile As String

'listing of jobs (cols A-D), job name in colB
' info from report files will be added a new columns
Set shtListing = ThisWorkbook.Sheets("Jobs List")
rw = 2 'first row of jobs listing

'do while have a job name
Do While Len(shtListing.Cells(rw, 2).Value) > 0

jobName = shtListing.Cells(rw, 2).Value
rptFile = QCReportPath(jobName)

'is the file where it's expected to be ?
If Dir(rptFile) <> "" Then

Set wbReport = Workbooks.Open(rptFile)
'reference the source worksheet
Set shtUpld = wbReport.Sheets("Upload")
'xfer info from report to listing sheet
With shtListing.Rows(rw)
.Cells(5).Value = shtUpld.Range("A2").Value
.Cells(6).Value = shtUpld.Range("B2").Value
.Cells(7).Value = shtUpld.Range("C2").Value
.Cells(8).Value = shtUpld.Range("D2").Value
'etc etc
End With
wbReport.Close False 'don't save changes
'flag OK
shtListing.Cells(rw, 2).Font.Color = vbGreen
Else
'flag file not found
shtListing.Cells(rw, 2).Font.Color = vbRed
End If

rw = rw + 1 'next listing row

Loop


End Sub

'Return the path to a report file given the job name
Function QCReportPath(jobName As String) As String
QCReportPath = RPTS_ROOT & jobName & _
" proof adjusted.xls"
End Function
'******************************************

 

[Héctor Miguel]

hi, Jen !

1) it's not so clear if you have done this part (or you are looking for a way to do)...
2) however, AFAICS, you have it solved as per your comment...
(but it is not clear if this is a *.csv or an *.xls file ?)

3) also, if you know what you say to know (again, as per your comment)...
4) there are many ways to get data from a closed file
(ADO, ms-query, excecuteexcel4macro, the function "pull" from HG, etc.)
also, you could try Ron de Bruin's "Copy/Paste/Merge examples" section from here...
http://www.rondebruin.nl/tips.htm

if any doubt (or further infomation)... would you please comment ?
regards,
hector.

__ 1 __

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.

__ 2 __

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

__ 3 __

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.

__ 4 __

 

[cmjat]

Hi Hector,

1. I can do this.
2. It's in an Excel file.
3/4. This is my problem area - I know very little VB so here's where I'm
struggling. First, I need to make sure the VB code doesn't open every file in
the directory, just looks up the data from the list I've exported. Basically
do-while for the list of inputs. Also, I need some help on actual
implementation. I was thinking I needed to create a column where I took the
job name and created the filename string so the VB knew where to go get the
data but I'm getting the impression from the reading I'm do that I don't need
to do that. Can you help with how I might have to modify ExecuteExcel4Macro
to make this work? Can you talk me through what the code in the existing
do-while is actually doing?

Thanks