Hi all,

with some help (!) I managed to have a .doc file to open after
running a macro with a given input in a cell.

For instance:

cell b2 hs the value 101

Now after selecting the cell (B2) and running the macro.
The file 101_document will be opened.


However: the "_document" part is a random name.
My list of files looks a bit like this:

101_smith_retail
102_Johnson_hardware
103_Kelly_cycles

How can I open a file after input?

So the line: strFilename = "_document.doc"
should be more like: strFilename = random_name ????




Sub test()
'Dim MyFile As String

Dim wdapp As Object
strFilename = "_document.doc"
Set wdapp = CreateObject("Word.Application")
With wdapp
.Documents.Open Filename:="C:\" & ActiveCell.Value & strFilename
.Visible = True
End With
Set wdapp = Nothing
End Sub



I hope my description is clear enough for you to understand what i'm
looking for?


Thanks in advance for your help, Theo


--
Greetz, Just4fun

You cannot generate a random name string, it just doesn't make sense as
their is not a name string series as there is a number series. If you were
holding the names in a table somewhere, you could generate a random number
and then use that as an index into the table to get a name.

But why would you want to do this, surely it is highly unlikely that this
document will exist?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Just4fun" wrote in message
...
Hi all,

with some help (!) I managed to have a .doc file to open after
running a macro with a given input in a cell.

For instance:

cell b2 hs the value 101

Now after selecting the cell (B2) and running the macro.
The file 101_document will be opened.


However: the "_document" part is a random name.
My list of files looks a bit like this:

101_smith_retail
102_Johnson_hardware
103_Kelly_cycles

How can I open a file after input?

So the line: strFilename = "_document.doc"
should be more like: strFilename = random_name ????




Sub test()
'Dim MyFile As String

Dim wdapp As Object
strFilename = "_document.doc"
Set wdapp = CreateObject("Word.Application")
With wdapp
.Documents.Open Filename:="C:\" & ActiveCell.Value & strFilename
.Visible = True
End With
Set wdapp = Nothing
End Sub



I hope my description is clear enough for you to understand what i'm
looking for?


Thanks in advance for your help, Theo


--
Greetz, Just4fun

bob,

I think OP is writing a procedure for SPAMMING.
else why need random names like the one he's proposing?

just4fun... nah!

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Bob Phillips" wrote:

You cannot generate a random name string, it just doesn't make sense
as their is not a name string series as there is a number series. If
you were holding the names in a table somewhere, you could generate a
random number and then use that as an index into the table to get a
name.

But why would you want to do this, surely it is highly unlikely that
this document will exist?

dim sStr as String, sStr1 as String
sStr = Trim(Activecell.Text)
sStr1 = Dir("c:\Myfiles\" & sStr & "_*.xls)
if sStr1 < "" then
workbooks.Open "C:\MyFiles\" & sStr1
End if

I am not sure where you want to open a doc file, but you can't open it in
Excel. I used .xls as the extension, but perhaps this is a word question
and you are posting in the wrong group.

--
Regards,
Tom Ogilvy

"Just4fun" wrote in message
...
Hi all,

with some help (!) I managed to have a .doc file to open after
running a macro with a given input in a cell.

For instance:

cell b2 hs the value 101

Now after selecting the cell (B2) and running the macro.
The file 101_document will be opened.


However: the "_document" part is a random name.
My list of files looks a bit like this:

101_smith_retail
102_Johnson_hardware
103_Kelly_cycles

How can I open a file after input?

So the line: strFilename = "_document.doc"
should be more like: strFilename = random_name ????




Sub test()
'Dim MyFile As String

Dim wdapp As Object
strFilename = "_document.doc"
Set wdapp = CreateObject("Word.Application")
With wdapp
.Documents.Open Filename:="C:\" & ActiveCell.Value & strFilename
.Visible = True
End With
Set wdapp = Nothing
End Sub



I hope my description is clear enough for you to understand what i'm
looking for?


Thanks in advance for your help, Theo


--
Greetz, Just4fun

keepITcool wrote in
:

bob,

I think OP is writing a procedure for SPAMMING.
else why need random names like the one he's proposing?

just4fun... nah!

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Bob Phillips" wrote:

You cannot generate a random name string, it just doesn't make sense
as their is not a name string series as there is a number series. If
you were holding the names in a table somewhere, you could generate a
random number and then use that as an index into the table to get a
name.

But why would you want to do this, surely it is highly unlikely that
this document will exist?



Is what I am doing called "spamming" ?
(posting the same question in 2 newsgroups)


If so... sorry for that.

(forgive me for my noobnes)


Theo.


--

I think he meant the part after the three digits would not be always the
same - perhaps random was a poorly chosen descriptive term to indicate this.

--
Regards,
Tom Ogilvy

"keepITcool" wrote in message
...
bob,

I think OP is writing a procedure for SPAMMING.
else why need random names like the one he's proposing?

just4fun... nah!

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Bob Phillips" wrote:

You cannot generate a random name string, it just doesn't make sense
as their is not a name string series as there is a number series. If
you were holding the names in a table somewhere, you could generate a
random number and then use that as an index into the table to get a
name.

But why would you want to do this, surely it is highly unlikely that
this document will exist?

"Tom Ogilvy" wrote in
: Hi Tom,


you'r wrong on the fact that it's not possible to open a
..doc file from within Escel.....it can be done.

Thanks to Don and Juan I made it happen.






I am not sure where you want to open a doc file, but you can't open it
in Excel. I used .xls as the extension, but perhaps this is a word
question and you are posting in the wrong group.




--
Greetz, Just4fun

"Tom Ogilvy" wrote in
:

I think he meant the part after the three digits would not be always
the same - perhaps random was a poorly chosen descriptive term to
indicate this.



And right as you are Tom ;))


(did I already tell ya i'm kinda of a newebee ?)


--
Greetz, Just4fun

If the doc file is a word document as is usually implied by the doc
extension, then it can not be opened in excel. Please demonstrate that this
is incorrect.

Where did Don and Juan demonstrate this magical capability.

--
Regards,
Tom Ogilvy

"Just4fun" wrote in message
...
"Tom Ogilvy" wrote in
: Hi Tom,


you'r wrong on the fact that it's not possible to open a
.doc file from within Escel.....it can be done.

Thanks to Don and Juan I made it happen.






I am not sure where you want to open a doc file, but you can't open it
in Excel. I used .xls as the extension, but perhaps this is a word
question and you are posting in the wrong group.




--
Greetz, Just4fun

Based on the code you posted:

Dim wdapp As Object
strFilename = "_document.doc"
Set wdapp = CreateObject("Word.Application")
With wdapp
.Documents.Open Filename:="C:\my documents\" & ActiveCell.Value &
strFilename
.Visible = True
End With
Set wdapp = Nothing
End Sub


It is obvious that you are not opening the document in Excel. Using Excel
to manipulate word to open the document does not open it in Excel as you
have stated.

Also, your code does not answer the current question you ask. So I wonder
why you say Don and Juan have provided the answer.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
If the doc file is a word document as is usually implied by the doc
extension, then it can not be opened in excel. Please demonstrate that
this
is incorrect.

Where did Don and Juan demonstrate this magical capability.

--
Regards,
Tom Ogilvy

"Just4fun" wrote in message
...
"Tom Ogilvy" wrote in
: Hi Tom,


you'r wrong on the fact that it's not possible to open a
.doc file from within Escel.....it can be done.

Thanks to Don and Juan I made it happen.






I am not sure where you want to open a doc file, but you can't open it
in Excel. I used .xls as the extension, but perhaps this is a word
question and you are posting in the wrong group.




--
Greetz, Just4fun

Oke...

I will try to explain what:


While working in Excel.

A planner puts a value (f.i. 101) in cell B2
Where column B is "Customer requierment".
And these Customer req. are saved as:


101_smith_retail.doc
102_Johnson_hardware.doc
103_Kelly_cycles.doc


After that I select B2 and run my macro.
(see the macro below)

And this results in a word document (101_document.doc) to be opened.
(correct Tom, ....not IN excel, but as a normal WORD document.)

But: like the list (101, 102, 103...) shows, the last part of
each document name differs.


Maybe I use the wrong words to describe my exact wishes and so.
(but being Dutch, I do my best)





T.i.a. Theo.
- - - - - - - - - - - - - - - - - - -

Dim wdapp As Object
strFilename = "_document.doc"
Set wdapp = CreateObject("Word.Application")
With wdapp
.Documents.Open Filename:="C:\" & ActiveCell.Value & strFilename
.Visible = True
End With
Set wdapp = Nothing

if B2 contains 101 and you would want it to open 101_smith_retail.doc with
your existing code, then I gave you a way to do it. You just need to adapt
it to your existing code which should be very simple.

--
Regards,
Tom Ogilvy



"Just4fun" wrote in message
...
Oke...

I will try to explain what:


While working in Excel.

A planner puts a value (f.i. 101) in cell B2
Where column B is "Customer requierment".
And these Customer req. are saved as:


101_smith_retail.doc
102_Johnson_hardware.doc
103_Kelly_cycles.doc


After that I select B2 and run my macro.
(see the macro below)

And this results in a word document (101_document.doc) to be opened.
(correct Tom, ....not IN excel, but as a normal WORD document.)

But: like the list (101, 102, 103...) shows, the last part of
each document name differs.


Maybe I use the wrong words to describe my exact wishes and so.
(but being Dutch, I do my best)





T.i.a. Theo.
- - - - - - - - - - - - - - - - - - -

Dim wdapp As Object
strFilename = "_document.doc"
Set wdapp = CreateObject("Word.Application")
With wdapp
.Documents.Open Filename:="C:\" & ActiveCell.Value & strFilename
.Visible = True
End With
Set wdapp = Nothing

Tom,

I need to do something similar, not sure how to go about it.

What I need to do is in 1 cell input (or have determined) the number of items in a list.
Then with this list I want to open all of the files in the list (by using a Macro to loop through the appropriate number of times-the number of items in the list), the names in the list would be partial names (as in the word example above). I have been looking through the archives but dont seem to be able to find an example.

Thanks in advance for your help. Thanks also for providing such an excellent service to all of us without your expertese.

Mark

the method I provided assumes that the "key" digits would be unique - there
would not be a file

101_abc.doc
101_efg.doc

Not sure that is true in your case, but as a start

Assume the list starts in A2 of sheet1 of the workbook containing the code
(also assume that the list is in cells in the worksheet)

Dim rng as Range, cell as Range
Dim sStr as String, sStr1 as String
Dim sPath as String
Dim wkbk as Workbook
sPath = "C:\MyFolder1\Myfolder2\"
With worksheets("Sheet1")
set rng = .range(.cells(2,1),.cells(2,1).End(xldown))
End With
for each cell in rng
sStr = cell.Value
sStr1 = dir(sPath & sStr & "*.xls")
if sStr1 < "" then
set wkbk = Workbooks.Open sPath & sStr1
' work on the wkbk
wkbk.close Savechanges:=False ' or make it true
end if
Next

Post back with specifics where this doesn't meet you requirements and
perhaps it can be adapted.

--
Regards,
Tom Ogilvy



"Starting to Program" <Starting to wrote
in message ...
Tom,

I need to do something similar, not sure how to go about it.

What I need to do is in 1 cell input (or have determined) the number of
items in a list.
Then with this list I want to open all of the files in the list (by using
a Macro to loop through the appropriate number of times-the number of items
in the list), the names in the list would be partial names (as in the word
example above). I have been looking through the archives but dont seem to
be able to find an example.

Thanks in advance for your help. Thanks also for providing such an
excellent service to all of us without your expertese.

Mark

Tom,

Thanks for the lightning fast reply. Seems to be a problem with syntax on following line, not sure why...?

set wkbk = Workbooks.Open sPath & sStr1

Hope you can help.

Thanks again.

Mark

Try:

set wkbk = Workbooks.Open(sPath & sStr1)

Starting to Program wrote:

Tom,

Thanks for the lightning fast reply. Seems to be a problem with syntax on following line, not sure why...?

set wkbk = Workbooks.Open sPath & sStr1

Hope you can help.

Thanks again.

Mark

--

Dave Peterson

Thanks Dave + Tom

Macro now runs, it doesn't stop and I didn't see my file open, it just kept resetting kept looping through.

What I want is for it to open the files from the list and then stop.

Not sure why it keeps looping, any ideas?

Thanks again.

Mark

Dim rng as Range, cell as Range
Dim sStr as String, sStr1 as String
Dim sPath as String
Dim wkbk as Workbook
sPath = "C:\MyFolder1\Myfolder2\"
With worksheets("Sheet1")
set rng = .range(.cells(2,1),.cells(2,1).End(xldown))
End With
for each cell in rng
sStr = cell.Value
sStr1 = dir(sPath & sStr & "*.xls")
if sStr1 < "" then
set wkbk = Workbooks.Open( sPath & sStr1)
ans = msgbox( "Active workbook is " & wkbk.Fullname & vbNewline & _
"Stop Now???", vbYesNo)
if ans = vbYes then exit sub
' work on the wkbk
wkbk.close Savechanges:=False ' or make it true
end if
Next

When you say you want to work from a list, I gave you code to process the
list. Not sure what opening one file and stopping accomplishes. You always
want to work on the first file in the list? What exactly do you want the
code to do?

--
Regards,
Tom Ogilvy


"Starting to Program" wrote in
message ...
Thanks Dave + Tom

Macro now runs, it doesn't stop and I didn't see my file open, it just
kept resetting kept looping through.

What I want is for it to open the files from the list and then stop.

Not sure why it keeps looping, any ideas?

Thanks again.

Mark

Tom,

Sorry for not being specific. What I want to do is open the files from a list, each one contains links to a master file, these links would then update themselves. The user also has to input some info manually into each workbook.

These are then printed and saved. The list of files are different each time.

Was trying to work out how to save some time.

Thanks for your efforts, am going to try the new code now.

Mark

Tom,

Almost there, the only problem I now have is that if once the list contains only one file it loads the first file from the directory its pointed at, and continues to try to load this file until I say no.

Do you know why this would be? This was obviously what was happening before when it was looping through when I was testing it.

I am just curious about this and can live with it, as my list will almost always contain more than one value, if I could fix this problem thats great otherwise no problem.

Have a great day and thanks again.

Mark

Is the information small enough to query for it in a userform. Macros don't
really support stopping and letting the user work in the workbook and then
continuing. the macro could be written to open the workbook in the
activecell and then quit. Or you could make a floating toolbar that had a
couple of buttons. One to initialize (identify the list) and open the first
file, one to print the activeworkbook, close it and open the next one in the
list. Do you want something like that? Do you know how to manually make a
custom toolbar with two buttons and assign macros to it?

--
Regards,
Tom Ogilvy


"Starting to Program" wrote in
message ...
Tom,

Sorry for not being specific. What I want to do is open the files from a
list, each one contains links to a master file, these links would then
update themselves. The user also has to input some info manually into each
workbook.

These are then printed and saved. The list of files are different each
time.

Was trying to work out how to save some time.

Thanks for your efforts, am going to try the new code now.

Mark

Thanks a millon for giving your time to me especially on a Saturday

Hope you have a good weekend.

Mark

It works just like I wanted, opens up all of the files, and I can just close each one as it has been updated.

Brilliant work Tom

Have a good weekend, I know I will now thats done!

Thanks again

Mark

I can't believe that is what you want. I was trying to show you what the
macro does. Having it reopen other workbooks, especially if they are linked
is not what you want. Can we make the workbook to open, always the active
Cell. So you pick the first cell with a workbook name and run the macro,
then it will open that workbook and activate the next cell, ready for you
to run it again?

Public Sub ProcessFiles()
Dim sPath as String, sName as String
sPath = "C:\Myfolder\MyFolder1|"
if Activeworkbook.name < thisworkbook.Name then
Activesheet.Printout
Activeworkbook.close Savechanges:=True
end if
thisworkbook.Activate
Worksheets("List").Activate
if Selection.Count 1 then
selection(1).Select
End if
if activecell.Column < 1 then
Range("A2").Select
end if
if not isempty(activecell) then
sName = ActiveCell.Value
activeCell.offset(1,0).Select
workbooks.open sPath & sName
else
Msgbox "Done"
End if
End sub

Assumes first name is in A2 on a sheet named list in the workbook containing
the code and the list is on the activeworksheet when you first run the
macro. So

Run the macro
This will open the workbook.

You can make your changes, then run the macro

It will print the activeworksheet (the one where you made your changes),
then close and save it, and open the next file from the list

Keep making your changes and running the macro. It will tell you when you
have reached a blank cell.


--
Regards,
Tom Ogilvy


"Starting to Program" wrote in
message ...

Tom,

Almost there, the only problem I now have is that if once the list
contains only one file it loads the first file from the directory its
pointed at, and continues to try to load this file until I say no.

Do you know why this would be? This was obviously what was happening
before when it was looping through when I was testing it.

I am just curious about this and can live with it, as my list will almost
always contain more than one value, if I could fix this problem thats great
otherwise no problem.

Have a great day and thanks again.

Mark