Opening a excel woorkbook from VBA
 

Hii...

My VBA code generates a new excel sheet based on a existing excel
template and saves the new excel sheet in a location desired by the
user.

The path where the excel sheet in saved is contained in the variable
"fName"

Now,

i try to open the excel application directly using,

Shell "C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE " & FName,
vbMaximizedFocus

As long as the location is c:\xxx.xls or C:\test\xxx.xls (i.e) without
any spaces.. i dont have any problems.

But when the user saves it in his "my documents", the FName would be,

"C:\Documents and Settings\user\Desktop\xxx.xls"

now, it considers C:\Documents.xls as seperate path, and.xls as a
seperate path and Settings\user\Desktop\xxx.xls as a seperate path.

how do i resolve this....???

Opening a excel woorkbook from VBA
 

Hi,

try:

fname = "C:\Documents and Settings\user\Desktop\xxx.xls"
fname = chr(34) & fname & chr(34)

--
Greetings from Bavaria, Germany

Helmut Weber

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Opening a excel woorkbook from VBA
 

Generally i would have said you cannot access the file if the user has
saved it as his "My Documents" without his login or password, i don't
know the answer to your problem but have a workround that may help!

Put this in the ThisWorkBook module it will prevent anyone from saving
the file as any other name i.e they may only click save!

Code:
--------------------
Private Sub Workbook_BeforeSave (ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = True Then Cancel = True
End Sub
--------------------
or this code (which isnt mine, it was kindly donated) prevents the user
from using save as or to a different filename!

Code:
--------------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim strName As String
If (SaveAsUI = True) Or (ThisWorkbook.Name = "MyWorkBook.xls")' 'myworkbook is your workbooks name
Then
Do
strName = Application.GetSaveAsFilename
Loop Until Right(strName, Len(strName) - InStrRev(strName, "\")) _
< "metric chart preview.xls"
Application.EnableEvents = False
If UCase(Left(strName, 5)) < "FALSE" Then
ThisWorkbook.SaveAs strName
End If
Cancel = True
Application.EnableEvents = True
End If
End Sub
--------------------
Hope this helps,

Regards,
Simon


--
Simon Lloyd

Opening a excel woorkbook from VBA
 

Thanks for your inputs...

Actually,i get the file name from the user and the save as location
from the user using..

FName = xlApp.Application.GetSaveAsFilename(sFileName & Format(date,
"yyyymmdd") & "_" & Format _
(Time, "hh.mm.ss") & ".xls", "Excel Workbook (*.xls), *.xls", ,
"Please choose the location to save the report")

If FName = False Then
MsgBox "Please choose a valid location to save the report"
GoTo Retry
End If

It is in this place where the user gets the option to choose, where to
save the excel.. if he chooses to save it in his desktop.. then the
problem i had mentioned earlier araises..



Simon Lloyd wrote:

Generally i would have said you cannot access the file if the user has
saved it as his "My Documents" without his login or password, i don't
know the answer to your problem but have a workround that may help!

Put this in the ThisWorkBook module it will prevent anyone from saving
the file as any other name i.e they may only click save!

Code:
--------------------
Private Sub Workbook_BeforeSave (ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = True Then Cancel = True
End Sub
--------------------
or this code (which isnt mine, it was kindly donated) prevents the user
from using save as or to a different filename!

Code:
--------------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim strName As String
If (SaveAsUI = True) Or (ThisWorkbook.Name = "MyWorkBook.xls")' 'myworkbook is your workbooks name
Then
Do
strName = Application.GetSaveAsFilename
Loop Until Right(strName, Len(strName) - InStrRev(strName, "\")) _
< "metric chart preview.xls"
Application.EnableEvents = False
If UCase(Left(strName, 5)) < "FALSE" Then
ThisWorkbook.SaveAs strName
End If
Cancel = True
Application.EnableEvents = True
End If
End Sub
--------------------
Hope this helps,

Regards,
Simon


--
Simon Lloyd