Opening a file through input of first 3 digits

[Just4fun]

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

 

[Bob Phillips]

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)

 

[keepITcool]

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 >

 

[Tom Ogilvy]

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.

 

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

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.


--

 

[Tom Ogilvy]

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.

 

[Just4fun]

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.

 

[Just4fun]

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 ?)

 

[Tom Ogilvy]

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.

 

[Tom Ogilvy]

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.

 

[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

 

[Tom Ogilvy]

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.

 

[Guest]

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 Ogilvy]

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

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.

 

[Guest]

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]

Try:

set wkbk = Workbooks.Open(sPath & sStr1)

Tom,

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


Hope you can help.

Thanks again.

Mark

 

[Guest]

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 Ogilvy]

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

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.

 

[Guest]

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

 

[Guest]

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