Word searching files, with data from a cell in excel 
Author Message
 Word searching files, with data from a cell in excel

I am writing a script for word or excel, it can be either or, to search for
.doc in a folder. I want to use info from a cell that exists and is
constanly updated in a cell. in each cell block is a number that matches the
file name in the folder, the only thing is that when it goes to excel it
does not contain the .doc. When I use the filter, I want to be able to run
the script and pull a ll documents matching the cell criteria, Does any one
know how to do this?


Sun, 06 Jul 2003 23:35:04 GMT  
 Word searching files, with data from a cell in excel
Hi Steve,

Could you please post back with the relevant part of the code you're using?
I'm not sure if I understand correctly what it is you want to do.

Hope this helps,
regards,
Astrid

So that all can benefit from the discussion,
please post all followups to the newsgroup.

Visit the MVP Word FAQ site at http://www.mvps.org/word/
For direct access to all Microsoft newsgroups:



Quote:
> I am writing a script for word or excel, it can be either or, to search
for
> .doc in a folder. I want to use info from a cell that exists and is
> constanly updated in a cell. in each cell block is a number that matches
the
> file name in the folder, the only thing is that when it goes to excel it
> does not contain the .doc. When I use the filter, I want to be able to run
> the script and pull a ll documents matching the cell criteria, Does any
one
> know how to do this?



Tue, 08 Jul 2003 04:44:52 GMT  
 Word searching files, with data from a cell in excel
Hi Steve,

Sorry, but despite your below description, the situation is
still unclear.
The situation should be fairly easy to deal with but ...

Quote:
> a cell that exists

What do you mean with cell exists?

Quote:
> constanly updated in a cell.

You mean: overwritten constantly ?

Quote:
> each cell block

Do you mean, contiguous cellranges in Excel?

Quote:
> the only thing is that when it goes to excel it
> does not contain the .doc.

Sorry, but can not help you if you clarify ...

Krgrds,
Perry



Quote:
> I am writing a script for word or excel, it can be either or, to search
for
> .doc in a folder. I want to use info from a cell that exists and is
> constanly updated in a cell. in each cell block is a number that matches
the
> file name in the folder, the only thing is that when it goes to excel it
> does not contain the .doc. When I use the filter, I want to be able to run
> the script and pull a ll documents matching the cell criteria, Does any
one
> know how to do this?



Tue, 08 Jul 2003 05:30:42 GMT  
 Word searching files, with data from a cell in excel
I am running a sequal, the info pulls data of an .mdb file. It then drops
all that info to excel. it refreshes every 2 min. The info in column a is
the job number. Now in the Directory I am searching is filled with files
that are named the "jobnnumber .doc" ex. 2567.doc. Now I set up excel to
filter and so on. So now If I want to view a specific account # jobs, I
select filter, pick the account #, and then it will show me all the jobs for
that account. Now I need to take all the .doc that go with that account and
count the lines and charactors in each account. So what I need excel to do
is ad the .doc to all the jobnumbers, then go and search the directory for
their match and put it in an external directory to be dropped into my
counting software.


Quote:
> Hi Steve,

> Sorry, but despite your below description, the situation is
> still unclear.
> The situation should be fairly easy to deal with but ...

> > a cell that exists
> What do you mean with cell exists?

> > constanly updated in a cell.
> You mean: overwritten constantly ?

> > each cell block
> Do you mean, contiguous cellranges in Excel?

> > the only thing is that when it goes to excel it
> > does not contain the .doc.

> Sorry, but can not help you if you clarify ...

> Krgrds,
> Perry



> > I am writing a script for word or excel, it can be either or, to search
> for
> > .doc in a folder. I want to use info from a cell that exists and is
> > constanly updated in a cell. in each cell block is a number that matches
> the
> > file name in the folder, the only thing is that when it goes to excel it
> > does not contain the .doc. When I use the filter, I want to be able to
run
> > the script and pull a ll documents matching the cell criteria, Does any
> one
> > know how to do this?



Fri, 11 Jul 2003 23:07:39 GMT  
 Word searching files, with data from a cell in excel
Hi Steve,

Before advising, just a little (re)confirmation needed here:

Column(A) contains Job#s
Based on these Job#s, Excel should find the associating
Word documents (belonging to this account).
If you confirm, I will show a snippet.

Quote:
> Now I need to take all the .doc that go with that account and
> count the lines and charactors in each account. So what I need excel to do
> is ad the .doc to all the jobnumbers, then go and search the directory for
> their match and put it in an external directory to be dropped into my
> counting software.

This part gives me a headache though, could you (sorry for inconvenience)
elaborate a bit more.

Quote:
> count the lines and charactors in each account

Do you mean, the number of Word documents found, regarding a particular
account?

Do you mean: Excel has to make an entry in same worksheet but in
another column of existing Word documents belonging accounts?

If so, what should be put in an external directory? The Word documents?
Or the Excel worksheet?

Sorry again Steven for these additional questions, but it's better raising
them beforehand, as opposed to doing work in vain.

Krgrds,
Perry



Quote:
> I am running a sequal, the info pulls data of an .mdb file. It then drops
> all that info to excel. it refreshes every 2 min. The info in column a is
> the job number. Now in the Directory I am searching is filled with files
> that are named the "jobnnumber .doc" ex. 2567.doc. Now I set up excel to
> filter and so on. So now If I want to view a specific account # jobs, I
> select filter, pick the account #, and then it will show me all the jobs
for
> that account. Now I need to take all the .doc that go with that account
and
> count the lines and charactors in each account. So what I need excel to do
> is ad the .doc to all the jobnumbers, then go and search the directory for
> their match and put it in an external directory to be dropped into my
> counting software.



> > Hi Steve,

> > Sorry, but despite your below description, the situation is
> > still unclear.
> > The situation should be fairly easy to deal with but ...

> > > a cell that exists
> > What do you mean with cell exists?

> > > constanly updated in a cell.
> > You mean: overwritten constantly ?

> > > each cell block
> > Do you mean, contiguous cellranges in Excel?

> > > the only thing is that when it goes to excel it
> > > does not contain the .doc.

> > Sorry, but can not help you if you clarify ...

> > Krgrds,
> > Perry



> > > I am writing a script for word or excel, it can be either or, to
search
> > for
> > > .doc in a folder. I want to use info from a cell that exists and is
> > > constanly updated in a cell. in each cell block is a number that
matches
> > the
> > > file name in the folder, the only thing is that when it goes to excel
it
> > > does not contain the .doc. When I use the filter, I want to be able to
> run
> > > the script and pull a ll documents matching the cell criteria, Does
any
> > one
> > > know how to do this?



Fri, 11 Jul 2003 23:38:59 GMT  
 Word searching files, with data from a cell in excel
Yes that is what i mean. Thanks Perry. This will be a great accomplishment
for me.

Quote:
> Hi Steve,

> Before advising, just a little (re)confirmation needed here:

> Column(A) contains Job#s
> Based on these Job#s, Excel should find the associating
> Word documents (belonging to this account).
> If you confirm, I will show a snippet.

> > Now I need to take all the .doc that go with that account and
> > count the lines and charactors in each account. So what I need excel to
do
> > is ad the .doc to all the jobnumbers, then go and search the directory
for
> > their match and put it in an external directory to be dropped into my
> > counting software.
> This part gives me a headache though, could you (sorry for inconvenience)
> elaborate a bit more.

> > count the lines and charactors in each account
> Do you mean, the number of Word documents found, regarding a particular
> account?

> Do you mean: Excel has to make an entry in same worksheet but in
> another column of existing Word documents belonging accounts?

> If so, what should be put in an external directory? The Word documents?
> Or the Excel worksheet?

> Sorry again Steven for these additional questions, but it's better raising
> them beforehand, as opposed to doing work in vain.

> Krgrds,
> Perry



> > I am running a sequal, the info pulls data of an .mdb file. It then
drops
> > all that info to excel. it refreshes every 2 min. The info in column a
is
> > the job number. Now in the Directory I am searching is filled with files
> > that are named the "jobnnumber .doc" ex. 2567.doc. Now I set up excel to
> > filter and so on. So now If I want to view a specific account # jobs, I
> > select filter, pick the account #, and then it will show me all the jobs
> for
> > that account. Now I need to take all the .doc that go with that account
> and
> > count the lines and charactors in each account. So what I need excel to
do
> > is ad the .doc to all the jobnumbers, then go and search the directory
for
> > their match and put it in an external directory to be dropped into my
> > counting software.



> > > Hi Steve,

> > > Sorry, but despite your below description, the situation is
> > > still unclear.
> > > The situation should be fairly easy to deal with but ...

> > > > a cell that exists
> > > What do you mean with cell exists?

> > > > constanly updated in a cell.
> > > You mean: overwritten constantly ?

> > > > each cell block
> > > Do you mean, contiguous cellranges in Excel?

> > > > the only thing is that when it goes to excel it
> > > > does not contain the .doc.

> > > Sorry, but can not help you if you clarify ...

> > > Krgrds,
> > > Perry



> > > > I am writing a script for word or excel, it can be either or, to
> search
> > > for
> > > > .doc in a folder. I want to use info from a cell that exists and is
> > > > constanly updated in a cell. in each cell block is a number that
> matches
> > > the
> > > > file name in the folder, the only thing is that when it goes to
excel
> it
> > > > does not contain the .doc. When I use the filter, I want to be able
to
> > run
> > > > the script and pull a ll documents matching the cell criteria, Does
> any
> > > one
> > > > know how to do this?



Sat, 12 Jul 2003 00:49:17 GMT  
 Word searching files, with data from a cell in excel

Hi Steve,

Look below, for the copying of (not opened) files there's
a nifty DOS Move command equivalent.

Quote:
> Yes that is what i mean.

I take it, this is a confirmation on all questions that I raised
in last posting ...
Anyhow, I'm taking that for granted.

We'll take it one step at a time here.
Few remarks upfront:
- Word as client; Excel as Server in this bit of Automation

Here's a snippet, that reads Jobs.xls in F:\data\test
in which in Column A the job numbers are entered.
The Word documents in F:\data\test will be
entered in the cell, in column (b) next to the corresponding Job#.

Set a reference to the Excel object lib in Word VBE.
'****************
Sub Tester()
  Dim xl As Excel.Application
  Dim wb As Excel.Workbook
  Dim CurCel As Excel.Range
  Dim LastRow As Long
  Set xl = CreateObject("Excel.Application")
  Set wb = xl.Workbooks.Open("f:\data\test\jobs.xls")
  xl.Application.Visible = False
  LastRow = wb.ActiveSheet.Cells(1, 1).End(xlDown).Row

    For x = 2 To LastRow
      Set CurCel = wb.ActiveSheet.Cells(x, 1)
      CurCel.offset(0, 1) = GetNumberOfDocs(CurCel.Value)
    Next

  wb.Close 1
  xl.Quit
  Set wb = Nothing
  Set xl = Nothing  
End Sub

Function GetNumberOfDocs(JobNumber As String) As Long
  With Application.FileSearch
    .LookIn = "f:\data\test"
    .FileName = JobNumber & ".doc"
    .Execute
    MoveDocuments JobNumber & ".doc"
    GetNumberOfDocs = .FoundFiles.Count
  End With
End Function

********* DOS Command Move equivalent *********************

Quote:
> If so, what should be put in an external directory? The Word documents?

To move the Word documents to an external directory, use following

Private Sub MoveDocuments(SourceDocName As String)
  Name "f:\Data\test\" & SourceDocName As "f:\Data\" & SourceDocName
End Sub

The Name method, is the equivalent to the old DOS Move command.

I think the above will get you going and it will certainly give you ideas
to work on/out.

Krgrds,
Perry


Quote:
> Yes that is what i mean. Thanks Perry. This will be a great accomplishment
> for me.


> > Hi Steve,

> > Before advising, just a little (re)confirmation needed here:

> > Column(A) contains Job#s
> > Based on these Job#s, Excel should find the associating
> > Word documents (belonging to this account).
> > If you confirm, I will show a snippet.

> > > Now I need to take all the .doc that go with that account and
> > > count the lines and charactors in each account. So what I need excel to
> do
> > > is ad the .doc to all the jobnumbers, then go and search the directory
> for
> > > their match and put it in an external directory to be dropped into my
> > > counting software.
> > This part gives me a headache though, could you (sorry for inconvenience)
> > elaborate a bit more.

> > > count the lines and charactors in each account
> > Do you mean, the number of Word documents found, regarding a particular
> > account?

> > Do you mean: Excel has to make an entry in same worksheet but in
> > another column of existing Word documents belonging accounts?

> > If so, what should be put in an external directory? The Word documents?
> > Or the Excel worksheet?

> > Sorry again Steven for these additional questions, but it's better raising
> > them beforehand, as opposed to doing work in vain.

> > Krgrds,
> > Perry



> > > I am running a sequal, the info pulls data of an .mdb file. It then
> drops
> > > all that info to excel. it refreshes every 2 min. The info in column a
> is
> > > the job number. Now in the Directory I am searching is filled with files
> > > that are named the "jobnnumber .doc" ex. 2567.doc. Now I set up excel to
> > > filter and so on. So now If I want to view a specific account # jobs, I
> > > select filter, pick the account #, and then it will show me all the jobs
> > for
> > > that account. Now I need to take all the .doc that go with that account
> > and
> > > count the lines and charactors in each account. So what I need excel to
> do
> > > is ad the .doc to all the jobnumbers, then go and search the directory
> for
> > > their match and put it in an external directory to be dropped into my
> > > counting software.



> > > > Hi Steve,

> > > > Sorry, but despite your below description, the situation is
> > > > still unclear.
> > > > The situation should be fairly easy to deal with but ...

> > > > > a cell that exists
> > > > What do you mean with cell exists?

> > > > > constanly updated in a cell.
> > > > You mean: overwritten constantly ?

> > > > > each cell block
> > > > Do you mean, contiguous cellranges in Excel?

> > > > > the only thing is that when it goes to excel it
> > > > > does not contain the .doc.

> > > > Sorry, but can not help you if you clarify ...

> > > > Krgrds,
> > > > Perry



> > > > > I am writing a script for word or excel, it can be either or, to
> > search
> > > > for
> > > > > .doc in a folder. I want to use info from a cell that exists and is
> > > > > constanly updated in a cell. in each cell block is a number that
> > matches
> > > > the
> > > > > file name in the folder, the only thing is that when it goes to
> excel
> > it
> > > > > does not contain the .doc. When I use the filter, I want to be able
> to
> > > run
> > > > > the script and pull a ll documents matching the cell criteria, Does
> > any
> > > > one
> > > > > know how to do this?



Sat, 12 Jul 2003 05:33:46 GMT  
 Word searching files, with data from a cell in excel
Hi Steve,

Already answered, see Blow

**************************************

Look below, for the copying of (not opened) files there's
a nifty DOS Move command equivalent.

Quote:
> Yes that is what i mean.

I take it, this is a confirmation on all questions that I raised
in last posting ...
Anyhow, I'm taking that for granted.

We'll take it one step at a time here.
Few remarks upfront:
- Word as client; Excel as Server in this bit of Automation

Here's a snippet, that reads Jobs.xls in F:\data\test
in which in Column A the job numbers are entered.
The Word documents in F:\data\test will be
entered in the cell, in column (b) next to the corresponding Job#.

Set a reference to the Excel object lib in Word VBE.
'****************
Sub Tester()
  Dim xl As Excel.Application
  Dim wb As Excel.Workbook
  Dim CurCel As Excel.Range
  Dim LastRow As Long
  Set xl = CreateObject("Excel.Application")
  Set wb = xl.Workbooks.Open("f:\data\test\jobs.xls")
  xl.Application.Visible = False
  LastRow = wb.ActiveSheet.Cells(1, 1).End(xlDown).Row

    For x = 2 To LastRow
      Set CurCel = wb.ActiveSheet.Cells(x, 1)
      CurCel.offset(0, 1) = GetNumberOfDocs(CurCel.Value)
    Next

  wb.Close 1
  xl.Quit
  Set wb = Nothing
  Set xl = Nothing
End Sub

Function GetNumberOfDocs(JobNumber As String) As Long
  With Application.FileSearch
    .LookIn = "f:\data\test"
    .FileName = JobNumber & ".doc"
    .Execute
    MoveDocuments JobNumber & ".doc"
    GetNumberOfDocs = .FoundFiles.Count
  End With
End Function

********* DOS Command Move equivalent *********************

Quote:
> If so, what should be put in an external directory? The Word documents?

To move the Word documents to an external directory, use following

Private Sub MoveDocuments(SourceDocName As String)
  Name "f:\Data\test\" & SourceDocName As "f:\Data\" & SourceDocName
End Sub

The Name method, is the equivalent to the old DOS Move command.

I think the above will get you going and it will certainly give you ideas
to work on/out.

Krgrds,
Perry



Sat, 12 Jul 2003 05:56:04 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. Word macro extracting data from Excel cell

2. Common data file between word and excel

3. Export Korean Data to MS word or Excel file

4. Getting values of Excel cells from word VBA

5. Insert Excel cell into Word

6. Linking many excel cells to many word documents?

7. Copy a selection of cells from excel to word, using a button

8. Text Width of Cell Data in Excel/VBA?

9. Excel Read Cell data into Array

10. Getting Cell data from excel into a variable

11. How to read Excel cells data types without Variant types

12. link dbgrid data to specific Excel cells

 

 
Powered by phpBB® Forum Software