Macro error when file not found

[Launchnet]

I have the following Excel macro:

Sub NewExcelWithWorkbook()
Dim oXL As Object
Dim oWB As Object

Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set oWB = oXL.Workbooks.Open("C:\documents and settings\default\my
documents\classhandouts.xls")
End Sub

This path works on my PC, but does not work on my daughters as her folder for
\my documents\ is in the path C:\Documents and Settings\User\My Documents

1st question . . . Is there different paths used by Microsoft, or has someone
simply moved things around ?

2nd question . . . When my macro encounters an error, is there a way of
automatically retrying the second path ?

3rd question . . . If Microsoft does use different paths to \my documents\
how do I then solve this problem ?

I am sure someone in your group can help me as they have in the past.

Thanks Much
Matt@Launchnet

 

[Barb Reinhardt]

Try something like this

Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
set oWB = Nothing
On Error Resume Next
Set oWB = oXL.Workbooks.Open("C:\documents and settings\default\my
documents\classhandouts.xls")
if not oWB is nothing then
'Code if no error
else
'Code if error
end if

HTH
Barb Reinhardt

 

[Nayan]

Matt,

Try following code.

Add a reference to "microsoft scripting run time" from tools-reference in
code editor

Hope this is what you are looking for


Nayan
----------------------------------------------------------------------------------
Sub NewExcelWithWorkbook()

Dim oXL As Object
Dim oWB As Object
Dim oFS As New FileSystemObject
Dim sPath As String
On Error GoTo eErrorHandler
sPath = "C:\documents and settings\default\my documents\classhandouts.xls"

Set oXL = CreateObject("Excel.Application")
oXL.Visible = True

If oFS.FileExists(sPath) Then
Set oWB = oXL.Workbooks.Open(sPath)
Else
MsgBox "The path of classhandouts.xls is not valid. Please browse to
classhandouts.xls" & vbCrLf & _
"Click OK and a file browser will be displayed", vbInformation
sPath = Application.GetOpenFilename(filefilter:="Excel Files,*.xls")
Set oWB = oXL.Workbooks.Open(sPath)
End If

CleanUp:
oXL.Quit
If Not oXL Is Nothing Then Set oXL = Nothing

Exit Sub
eErrorHandler:
MsgBox Err.Description
GoTo CleanUp

End Sub

 

[Jon Peltier]

You need to find the My Documents directory, then see if the file is there,
then proceed.

Add this function to the module:

Function MyDocDirectory() As String
Dim WSHShell As Object
Dim ThePath As String
Set WSHShell = CreateObject("WScript.Shell")
ThePath = WSHShell.SpecialFolders("MyDocuments")
MyDocDirectory = ThePath
Set WSHShell = Nothing
End Function


Add these declarations to your procedure:

Dim sPath as String
Dim sFileName as String
Dim sFullName as string

Find the directory using the function above and check for file using Dir()

SFileName = "classhandouts.xls"
SPath = MyDocDirectory
sFullName = sPath & "\" & sFileName

If Len(Dir(sFullName)) = 0 then
MsgBox "File " & sFileName & " was not found in " & sPath
Exit Sub
End If

proceed from here through your procedure. You probably want to do this check
before creating the new instance of Excel.

If you want something real advanced, you can then offer the user the
opportunity to browse for the file after the message box instead of exiting.
This is most useful, because a semi-sophisticated user will not simply dump
all of their files into My Documents, but will probably have a hierarchy of
directories, e.g.,

...\My Documents\School\Math Class\classhandouts.xls

This helps keep the schoolwork separate from the MP3 files.

Dim iMsgAnswer as Long
Dim sMessage as String
If Len(Dir(sFullName)) = 0 then
sMessage = "File " & sFileName & " was not found in " & sPath
sMessage = sMessage & vbNewLine & vbNewLine
sMessage = sMessage & "Do you want to browse for it?"
iMsgAnswer = MsgBox (sMessage, vbQuestion + vbOKCancel)
If iMsgAnswer = vbCancel Then Exit Sub
sFullName = CStr(Application.GetOpenFileName("Excel Workbooks
(*.xls),*.xls"))
If sFullName = "False" then Exit Sub
If Len(Dir(sFullName)) = 0 then Exit Sub
End If

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
774-275-0064
208-485-0691 fax
(e-mail address removed)
http://PeltierTech.com/
_______

 

[Launchnet via OfficeKB.com]

Attn: Jon Peltier . . .Nayan

I will address this to Jon, but Nayan please understand it is to both of you.

So far, I have found 3 different paths to "My Documents" and I am now sure
there are many more.

Here is the code I used from your example. It works fine on my PC which has
"My Documents" located at: C:\documents and settings\default\my
documents\computerclasshandouts.xls
... But Naturally, it doesn't work if "My Documents" is in any other path,
such as: C:\My Documents
... Or, C:\.....\.........\My Documents.
Needless to say, if "My Documents" is located in any other path it will not
work.



1st a QUESTION: Shouldn't the ("MyDocuments") used in the Function, at the
end of ThePath = . . . have a space between the two words?

Function MyDocDirectory() As String
Dim WSHShell As Object
Dim ThePath As String
Set WSHShell = CreateObject("WScript.Shell")
ThePath = WSHShell.SpecialFolders("MyDocuments")
MyDocDirectory = ThePath
Set WSHShell = Nothing
End Function

Before we go further, I think I had better explain the usage of this Macro.
I have developed a Menu System that allows my clients to go anyplace to open
a file or link to another Excel page(s) in My Menu Workbook. This also
includes links to any website etc, etc, etc.

What I need this particular Macro to do is open a new instance of Excel along
with any existing Excel Workbook separate from My Menu. The user will copy
the Master Macro, rename the macro and add the name of their workbook to the
macro, replacing \classhandouts.xls.

When this macro Button is clicked, Excel opens in a new instance along with
the named workbook.

The user can have as many macros as needed to open different workbooks that
they frequently use.
I think as a general rule, 25 to 35 files will be the max that they use and
regardless of how many workbooks they have, they all can be in the "My
Documents" folder, as the user will have no need of searching for any file
they want to use. Again, there will be some people that this isn't practical
for, but I have talked with many users and with my many years of working with
Excel, this approach will save most people considerable time. Naturally, my
problem is that I am not a good programmer.



You wrote this note: You need to find the "My Documents" directory and then
see if the file is there . . . then proceed.


I agree with approach, but somehow the path to "My Documents" directory is
needed so that the routine then looks in the appropriate path which ends with
"My Documents" to open the file specified. My users will have access to this
routine and will modify the document name. See suggestions on: sFileName
below.

Here is what I tried from Jon's suggestion


Sub NewExcelWithWorkbook()
Dim sPath As String
Dim sFileName As String
Dim sFullName As String
Dim oXL As Object
Dim oWB As Object

sFileName = "classhandouts.xls" {EX: .....or..... "markshobbies.xls" ....
..or....."timeshare2.xls"}
sPath = MyDocDirectory
sFullName = sPath & "\" & sFileName

If Len(Dir(sFullName)) = 0 Then
MsgBox "File " & sFileName & " was not found in " & sPath
Exit Sub
End If

Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set oWB = oXL.Workbooks.Open("C:\documents and settings\default\my
documents\computerclasshandouts.xls")
End Sub

It only works when using the path described just above here.

Although I am not capable of writing the code, I will give my thoughts only
for the possibility of helping.

I think that 1st the "My Documents" directory has to be located along with
it's path.
This path would be stored in sPath.
The workbook to be opened is stored in sFileName.
Since the path and file name are now known, the procedure should beable to
open it. ??????

I bow to your far superior knowledge of programming over myself. Please help
me if you will.

I will be more than happy to respond to any questions you may have.

Regards
Matt@Launchnet

You need to find the My Documents directory, then see if the file is there,
then proceed.

Add this function to the module:

Function MyDocDirectory() As String
Dim WSHShell As Object
Dim ThePath As String
Set WSHShell = CreateObject("WScript.Shell")
ThePath = WSHShell.SpecialFolders("MyDocuments")
MyDocDirectory = ThePath
Set WSHShell = Nothing
End Function

Add these declarations to your procedure:

Dim sPath as String
Dim sFileName as String
Dim sFullName as string

Find the directory using the function above and check for file using Dir()

SFileName = "classhandouts.xls"
SPath = MyDocDirectory
sFullName = sPath & "\" & sFileName

If Len(Dir(sFullName)) = 0 then
MsgBox "File " & sFileName & " was not found in " & sPath
Exit Sub
End If

proceed from here through your procedure. You probably want to do this check
before creating the new instance of Excel.

If you want something real advanced, you can then offer the user the
opportunity to browse for the file after the message box instead of exiting.
This is most useful, because a semi-sophisticated user will not simply dump
all of their files into My Documents, but will probably have a hierarchy of
directories, e.g.,

...\My Documents\School\Math Class\classhandouts.xls

This helps keep the schoolwork separate from the MP3 files.

Dim iMsgAnswer as Long
Dim sMessage as String
If Len(Dir(sFullName)) = 0 then
sMessage = "File " & sFileName & " was not found in " & sPath
sMessage = sMessage & vbNewLine & vbNewLine
sMessage = sMessage & "Do you want to browse for it?"
iMsgAnswer = MsgBox (sMessage, vbQuestion + vbOKCancel)
If iMsgAnswer = vbCancel Then Exit Sub
sFullName = CStr(Application.GetOpenFileName("Excel Workbooks
(*.xls),*.xls"))
If sFullName = "False" then Exit Sub
If Len(Dir(sFullName)) = 0 then Exit Sub
End If

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
774-275-0064
208-485-0691 fax
(e-mail address removed)
http://PeltierTech.com/
_______

I have the following Excel macro:

[quoted text clipped - 26 lines]

Thanks Much
Matt@Launchnet

 

[NickHK]

Whilst a user can create a folder anywhere in their file system called "My
Documents", I assume that is not the folder you are looking for.
Windows considers the "My Documents" folder as a "Special Folder", hence the
use of that term in John's code.
It is conceivable that the user has renamed this to something else and it
may be located elsewhere in different windows version, but John's code will
always return the correct folder that Windows considers it to be.

What are these 3 different paths that you have found and what does John's
code return ?

Each user account for your computer will have its own set of Windows
folders, plus the same for "Default User" and "SYSTEM".
What you are looking for, I guess, is what may be returned by:
?Environ("UserProfile") & "\My Documents"

but may not for the reasons above.
So, basically, let Windows tell you where it is instead of guessing.

NickHK

Attn: Jon Peltier . . .Nayan

I will address this to Jon, but Nayan please understand it is to both of you.

So far, I have found 3 different paths to "My Documents" and I am now sure
there are many more.

Here is the code I used from your example. It works fine on my PC which has
"My Documents" located at: C:\documents and settings\default\my
documents\computerclasshandouts.xls
.. But Naturally, it doesn't work if "My Documents" is in any other path,
such as: C:\My Documents
.. Or, C:\.....\.........\My Documents.
Needless to say, if "My Documents" is located in any other path it will not
work.



1st a QUESTION: Shouldn't the ("MyDocuments") used in the Function, at the
end of ThePath = . . . have a space between the two words?

Function MyDocDirectory() As String
Dim WSHShell As Object
Dim ThePath As String
Set WSHShell = CreateObject("WScript.Shell")
ThePath = WSHShell.SpecialFolders("MyDocuments")
MyDocDirectory = ThePath
Set WSHShell = Nothing
End Function

Before we go further, I think I had better explain the usage of this Macro.
I have developed a Menu System that allows my clients to go anyplace to open
a file or link to another Excel page(s) in My Menu Workbook. This also
includes links to any website etc, etc, etc.

What I need this particular Macro to do is open a new instance of Excel along
with any existing Excel Workbook separate from My Menu. The user will copy
the Master Macro, rename the macro and add the name of their workbook to the
macro, replacing \classhandouts.xls.

When this macro Button is clicked, Excel opens in a new instance along with
the named workbook.

The user can have as many macros as needed to open different workbooks that
they frequently use.
I think as a general rule, 25 to 35 files will be the max that they use and
regardless of how many workbooks they have, they all can be in the "My
Documents" folder, as the user will have no need of searching for any file
they want to use. Again, there will be some people that this isn't practical
for, but I have talked with many users and with my many years of working with
Excel, this approach will save most people considerable time. Naturally, my
problem is that I am not a good programmer.



You wrote this note: You need to find the "My Documents" directory and then
see if the file is there . . . then proceed.


I agree with approach, but somehow the path to "My Documents" directory is
needed so that the routine then looks in the appropriate path which ends with
"My Documents" to open the file specified. My users will have access to this
routine and will modify the document name. See suggestions on: sFileName
below.

Here is what I tried from Jon's suggestion


Sub NewExcelWithWorkbook()
Dim sPath As String
Dim sFileName As String
Dim sFullName As String
Dim oXL As Object
Dim oWB As Object

sFileName = "classhandouts.xls" {EX: .....or..... "markshobbies.xls" .....
or....."timeshare2.xls"}
sPath = MyDocDirectory
sFullName = sPath & "\" & sFileName

If Len(Dir(sFullName)) = 0 Then
MsgBox "File " & sFileName & " was not found in " & sPath
Exit Sub
End If

Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set oWB = oXL.Workbooks.Open("C:\documents and settings\default\my
documents\computerclasshandouts.xls")
End Sub

It only works when using the path described just above here.

Although I am not capable of writing the code, I will give my thoughts only
for the possibility of helping.

I think that 1st the "My Documents" directory has to be located along with
it's path.
This path would be stored in sPath.
The workbook to be opened is stored in sFileName.
Since the path and file name are now known, the procedure should beable to
open it. ??????

I bow to your far superior knowledge of programming over myself. Please help
me if you will.

I will be more than happy to respond to any questions you may have.

Regards
Matt@Launchnet

You need to find the My Documents directory, then see if the file is there,
then proceed.

Add this function to the module:

Function MyDocDirectory() As String
Dim WSHShell As Object
Dim ThePath As String
Set WSHShell = CreateObject("WScript.Shell")
ThePath = WSHShell.SpecialFolders("MyDocuments")
MyDocDirectory = ThePath
Set WSHShell = Nothing
End Function

Add these declarations to your procedure:

Dim sPath as String
Dim sFileName as String
Dim sFullName as string

Find the directory using the function above and check for file using Dir()

SFileName = "classhandouts.xls"
SPath = MyDocDirectory
sFullName = sPath & "\" & sFileName

If Len(Dir(sFullName)) = 0 then
MsgBox "File " & sFileName & " was not found in " & sPath
Exit Sub
End If

proceed from here through your procedure. You probably want to do this check
before creating the new instance of Excel.

If you want something real advanced, you can then offer the user the
opportunity to browse for the file after the message box instead of exiting.
This is most useful, because a semi-sophisticated user will not simply dump
all of their files into My Documents, but will probably have a hierarchy of
directories, e.g.,

...\My Documents\School\Math Class\classhandouts.xls

This helps keep the schoolwork separate from the MP3 files.

Dim iMsgAnswer as Long
Dim sMessage as String
If Len(Dir(sFullName)) = 0 then
sMessage = "File " & sFileName & " was not found in " & sPath
sMessage = sMessage & vbNewLine & vbNewLine
sMessage = sMessage & "Do you want to browse for it?"
iMsgAnswer = MsgBox (sMessage, vbQuestion + vbOKCancel)
If iMsgAnswer = vbCancel Then Exit Sub
sFullName = CStr(Application.GetOpenFileName("Excel Workbooks
(*.xls),*.xls"))
If sFullName = "False" then Exit Sub
If Len(Dir(sFullName)) = 0 then Exit Sub
End If

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
774-275-0064
208-485-0691 fax
(e-mail address removed)
http://PeltierTech.com/
_______

I have the following Excel macro:

[quoted text clipped - 26 lines]

Thanks Much
Matt@Launchnet

 

[NickHK]

Also, another reason not to make assumptions with such folders is that if
the username has been changed since the accounts creation, the names will
not match:
e.g. The Windows folder MyDocuments returned for the username "NotUser1" may
be something like :

C:\Documents and Settings\User1\Not My Documents

NickHK

Attn: Jon Peltier . . .Nayan

I will address this to Jon, but Nayan please understand it is to both of you.

So far, I have found 3 different paths to "My Documents" and I am now sure
there are many more.

Here is the code I used from your example. It works fine on my PC which has
"My Documents" located at: C:\documents and settings\default\my
documents\computerclasshandouts.xls
.. But Naturally, it doesn't work if "My Documents" is in any other path,
such as: C:\My Documents
.. Or, C:\.....\.........\My Documents.
Needless to say, if "My Documents" is located in any other path it will not
work.



1st a QUESTION: Shouldn't the ("MyDocuments") used in the Function, at the
end of ThePath = . . . have a space between the two words?

Function MyDocDirectory() As String
Dim WSHShell As Object
Dim ThePath As String
Set WSHShell = CreateObject("WScript.Shell")
ThePath = WSHShell.SpecialFolders("MyDocuments")
MyDocDirectory = ThePath
Set WSHShell = Nothing
End Function

Before we go further, I think I had better explain the usage of this Macro.
I have developed a Menu System that allows my clients to go anyplace to open
a file or link to another Excel page(s) in My Menu Workbook. This also
includes links to any website etc, etc, etc.

What I need this particular Macro to do is open a new instance of Excel along
with any existing Excel Workbook separate from My Menu. The user will copy
the Master Macro, rename the macro and add the name of their workbook to the
macro, replacing \classhandouts.xls.

When this macro Button is clicked, Excel opens in a new instance along with
the named workbook.

The user can have as many macros as needed to open different workbooks that
they frequently use.
I think as a general rule, 25 to 35 files will be the max that they use and
regardless of how many workbooks they have, they all can be in the "My
Documents" folder, as the user will have no need of searching for any file
they want to use. Again, there will be some people that this isn't practical
for, but I have talked with many users and with my many years of working with
Excel, this approach will save most people considerable time. Naturally, my
problem is that I am not a good programmer.



You wrote this note: You need to find the "My Documents" directory and then
see if the file is there . . . then proceed.


I agree with approach, but somehow the path to "My Documents" directory is
needed so that the routine then looks in the appropriate path which ends with
"My Documents" to open the file specified. My users will have access to this
routine and will modify the document name. See suggestions on: sFileName
below.

Here is what I tried from Jon's suggestion


Sub NewExcelWithWorkbook()
Dim sPath As String
Dim sFileName As String
Dim sFullName As String
Dim oXL As Object
Dim oWB As Object

sFileName = "classhandouts.xls" {EX: .....or..... "markshobbies.xls" .....
or....."timeshare2.xls"}
sPath = MyDocDirectory
sFullName = sPath & "\" & sFileName

If Len(Dir(sFullName)) = 0 Then
MsgBox "File " & sFileName & " was not found in " & sPath
Exit Sub
End If

Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set oWB = oXL.Workbooks.Open("C:\documents and settings\default\my
documents\computerclasshandouts.xls")
End Sub

It only works when using the path described just above here.

Although I am not capable of writing the code, I will give my thoughts only
for the possibility of helping.

I think that 1st the "My Documents" directory has to be located along with
it's path.
This path would be stored in sPath.
The workbook to be opened is stored in sFileName.
Since the path and file name are now known, the procedure should beable to
open it. ??????

I bow to your far superior knowledge of programming over myself. Please help
me if you will.

I will be more than happy to respond to any questions you may have.

Regards
Matt@Launchnet

You need to find the My Documents directory, then see if the file is there,
then proceed.

Add this function to the module:

Function MyDocDirectory() As String
Dim WSHShell As Object
Dim ThePath As String
Set WSHShell = CreateObject("WScript.Shell")
ThePath = WSHShell.SpecialFolders("MyDocuments")
MyDocDirectory = ThePath
Set WSHShell = Nothing
End Function

Add these declarations to your procedure:

Dim sPath as String
Dim sFileName as String
Dim sFullName as string

Find the directory using the function above and check for file using Dir()

SFileName = "classhandouts.xls"
SPath = MyDocDirectory
sFullName = sPath & "\" & sFileName

If Len(Dir(sFullName)) = 0 then
MsgBox "File " & sFileName & " was not found in " & sPath
Exit Sub
End If

proceed from here through your procedure. You probably want to do this check
before creating the new instance of Excel.

If you want something real advanced, you can then offer the user the
opportunity to browse for the file after the message box instead of exiting.
This is most useful, because a semi-sophisticated user will not simply dump
all of their files into My Documents, but will probably have a hierarchy of
directories, e.g.,

...\My Documents\School\Math Class\classhandouts.xls

This helps keep the schoolwork separate from the MP3 files.

Dim iMsgAnswer as Long
Dim sMessage as String
If Len(Dir(sFullName)) = 0 then
sMessage = "File " & sFileName & " was not found in " & sPath
sMessage = sMessage & vbNewLine & vbNewLine
sMessage = sMessage & "Do you want to browse for it?"
iMsgAnswer = MsgBox (sMessage, vbQuestion + vbOKCancel)
If iMsgAnswer = vbCancel Then Exit Sub
sFullName = CStr(Application.GetOpenFileName("Excel Workbooks
(*.xls),*.xls"))
If sFullName = "False" then Exit Sub
If Len(Dir(sFullName)) = 0 then Exit Sub
End If

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
774-275-0064
208-485-0691 fax
(e-mail address removed)
http://PeltierTech.com/
_______

I have the following Excel macro:

[quoted text clipped - 26 lines]

Thanks Much
Matt@Launchnet

 

[Launchnet via OfficeKB.com]

Hi NickHK

I think I understand what you are saying. I agree, but I do have control
over part of this. I have informed the users that they must keep the files
in "My Documents" folder. Secondly, the file name is entered by them into
the routine.

One more idea that could be considered. I have control over Column A of the
Main Menu of the Workbook. Column A is very narrow, approximately 1/8". It
would even be better if the file name were to be typed into Cell 15, another
file name into Cell 16 and so on down the sheet. The Macro for each file
name is on the same row as the file it is to open in column B. Then the
program could get the file name from Cell 15 and automatically get that file
name from the "My Documents" folder. If this were possible, I could write 30
or so Macros in column B starting with Cell B15 and down. Each macro asking
for the file name to the left in column A. This way, the user would never
have to deal with changing the macros. I think.

Sure hope this makes sence.

Matt@Launchnet

Also, another reason not to make assumptions with such folders is that if
the username has been changed since the accounts creation, the names will
not match:
e.g. The Windows folder MyDocuments returned for the username "NotUser1" may
be something like :

C:\Documents and Settings\User1\Not My Documents

NickHK

Attn: Jon Peltier . . .Nayan

[quoted text clipped - 174 lines]

 

[NickHK]

I thought the problem was getting the current users' folder given by
<SpecialFolders.MyDocuments>.
As such, John has shown you code to get this, so you know where to
read/write on all systems.
Once you have that, it's up to you what you do:
- Dir(ThatPath & "*.xls")
- Application.GetOpenFilename
- Workbook.Open(ThatPath & "YourFile.xls")

NickHK

Hi NickHK

I think I understand what you are saying. I agree, but I do have control
over part of this. I have informed the users that they must keep the files
in "My Documents" folder. Secondly, the file name is entered by them into
the routine.

One more idea that could be considered. I have control over Column A of the
Main Menu of the Workbook. Column A is very narrow, approximately 1/8". It
would even be better if the file name were to be typed into Cell 15, another
file name into Cell 16 and so on down the sheet. The Macro for each file
name is on the same row as the file it is to open in column B. Then the
program could get the file name from Cell 15 and automatically get that file
name from the "My Documents" folder. If this were possible, I could write 30
or so Macros in column B starting with Cell B15 and down. Each macro asking
for the file name to the left in column A. This way, the user would never
have to deal with changing the macros. I think.

Sure hope this makes sence.

Matt@Launchnet

Also, another reason not to make assumptions with such folders is that if
the username has been changed since the accounts creation, the names will
not match:
e.g. The Windows folder MyDocuments returned for the username "NotUser1" may
be something like :

C:\Documents and Settings\User1\Not My Documents

NickHK

Attn: Jon Peltier . . .Nayan

[quoted text clipped - 174 lines]

Thanks Much
Matt@Launchnet

 

[Jon Peltier]

Nick -

Thanks for helping with your description of My Documents and Special
Folders.

Matt -

I generally distribute a setup file to place workbooks into a defined
directory so my program can find them. If the user decides s/he knows
better, I have code similar to that I posted which allows the user to find
their hiding place. I then store this path into a settings file; in fact I
generally save the whole file path and name, in case they've renamed the
file. It only inconveniences the user once for each time they redesign their
directory structure, which isn't too bad.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

Hi NickHK

I think I understand what you are saying. I agree, but I do have control
over part of this. I have informed the users that they must keep the
files
in "My Documents" folder. Secondly, the file name is entered by them into
the routine.

One more idea that could be considered. I have control over Column A of
the
Main Menu of the Workbook. Column A is very narrow, approximately 1/8".
It
would even be better if the file name were to be typed into Cell 15,
another
file name into Cell 16 and so on down the sheet. The Macro for each file
name is on the same row as the file it is to open in column B. Then the
program could get the file name from Cell 15 and automatically get that
file
name from the "My Documents" folder. If this were possible, I could write
30
or so Macros in column B starting with Cell B15 and down. Each macro
asking
for the file name to the left in column A. This way, the user would never
have to deal with changing the macros. I think.

Sure hope this makes sence.

Matt@Launchnet

Also, another reason not to make assumptions with such folders is that if
the username has been changed since the accounts creation, the names will
not match:
e.g. The Windows folder MyDocuments returned for the username "NotUser1"
may
be something like :

C:\Documents and Settings\User1\Not My Documents

NickHK

Attn: Jon Peltier . . .Nayan

[quoted text clipped - 174 lines]

Thanks Much
Matt@Launchnet

--
Please take a look at www.openoursite.com Click on: "Keywords" and then
Click on "Matt's Story" and if you are a man, you should be very happy
that
you read my story. God Bless for everyones help.

 

[Launchnet via OfficeKB.com]

Thanks everybody . . .
With all your suggestions, I have come up with what I think is a very good
way of doing this if the code can be written.

1st . . . Example

Row Col A - Holds Path & File Name Col
B holds the Macro Buttons
15 c:\my documents\classhandouts1.xls Macro
Button (All use same macro)
16 c:\............\........\artworkphase2.xls
Macro Button (All use same macro)
etc c:\AnyPathTo File
Macro Button (All use same macro)

I have the user type in the complete path with file name in column A, which
is only 1/8" wide.
I have set the alignment formatting to reduce to fit cell as I don't
necessarily want it displayed
I have copied a Macro Button to each cell in Column B across from column A.
I want to use as many of the same macros as possible, simply by copying and
adding 1,2,3,4,etc.
The operator changes the Button Displayed Name to whatever they want on each
Button
Button can be any width for easier reading.

I want to have the operator click on the Button across from let's say A16
which is located in col. B
The Macro 1st goes to cell A16, which holds the complete path to open the
included file name.
SO FAR, I can do this much.
Next, the Macro needs to read cell A16 as a variable ( I think ),
Then, the Macro needs to open a new instance of Excel along with the Path &
File Name in A16
NOTHING TO IT ..... For you that is. Forget it if I have to write
the code.

This is a very clean way of running this application and I don't care where
their path is or where "My Documents" directory is, as the user does the set
up and also adds more files later on to open more files, which includes
copying one of the macros, re-naming it and changing the Button Top Name.

What do you think.

Matt@Launchnet

Nick -

Thanks for helping with your description of My Documents and Special
Folders.

Matt -

I generally distribute a setup file to place workbooks into a defined
directory so my program can find them. If the user decides s/he knows
better, I have code similar to that I posted which allows the user to find
their hiding place. I then store this path into a settings file; in fact I
generally save the whole file path and name, in case they've renamed the
file. It only inconveniences the user once for each time they redesign their
directory structure, which isn't too bad.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

Hi NickHK

[quoted text clipped - 41 lines]

--
Please take a look at www.openoursite.com Click on: "Keywords" and then
Click on "Matt's Story" and if you are a man, you should be very happy that
you read my story. God Bless for everyones help.

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200707/1

 

[Launchnet via OfficeKB.com]

Sorry, I should have previewed before I sent the above reply. You will have
to do a little spacing to see how the columns are set up.

Thanks Again Matt@Launchnet

Thanks everybody . . .
With all your suggestions, I have come up with what I think is a very good
way of doing this if the code can be written.

1st . . . Example

Row Col A - Holds Path & File Name Col
B holds the Macro Buttons
15 c:\my documents\classhandouts1.xls Macro
Button (All use same macro)
16 c:\............\........\artworkphase2.xls
Macro Button (All use same macro)
etc c:\AnyPathTo File
Macro Button (All use same macro)

I have the user type in the complete path with file name in column A, which
is only 1/8" wide.
I have set the alignment formatting to reduce to fit cell as I don't
necessarily want it displayed
I have copied a Macro Button to each cell in Column B across from column A.
I want to use as many of the same macros as possible, simply by copying and
adding 1,2,3,4,etc.
The operator changes the Button Displayed Name to whatever they want on each
Button
Button can be any width for easier reading.

I want to have the operator click on the Button across from let's say A16
which is located in col. B
The Macro 1st goes to cell A16, which holds the complete path to open the
included file name.
SO FAR, I can do this much.
Next, the Macro needs to read cell A16 as a variable ( I think ),
Then, the Macro needs to open a new instance of Excel along with the Path &
File Name in A16
NOTHING TO IT ..... For you that is. Forget it if I have to write
the code.

This is a very clean way of running this application and I don't care where
their path is or where "My Documents" directory is, as the user does the set
up and also adds more files later on to open more files, which includes
copying one of the macros, re-naming it and changing the Button Top Name.

What do you think.

Matt@Launchnet

Nick -

[quoted text clipped - 23 lines]