Open Folder - Ogilvy code
 

In Excel 2002:

I found the code below from a Tom Ogilvy post. I want to attach a macro to
a toolbar that will open the directory of my choosing. The code errors out
on ActiveWorkbook.Save Filename:=fName. If I remove that line, the macro
will bring up the Open File dialog box, but will not actually open the file
I select.

One other question - since it will be in an Excel app, how do I limit the
Open File dialog box so that it only shows .xls files?

Public Sub OpenFile()
'Save the current folder path
ProperPath = CurDir
'Change the folder path
ChDrive "C"
ChDir "C:\My Path\My Folder"
'Display the open dialog box
fName = Application.GetOpenFilename
ActiveWorkbook.Save Filename:=fName ' << causes error "wrong number of
arguments or invalid property assignment"
'Change the default folder
ChDrive ProperPath
ChDir ProperPath
End Sub

TIA

Open Folder - Ogilvy code
 

Try:
ActiveWorkbook.SaveAs Filename:=fName

Take a look at .saveas in VBA's help and you'll find some other parms you may
want to specify.



wrote:

In Excel 2002:

I found the code below from a Tom Ogilvy post. I want to attach a macro to
a toolbar that will open the directory of my choosing. The code errors out
on ActiveWorkbook.Save Filename:=fName. If I remove that line, the macro
will bring up the Open File dialog box, but will not actually open the file
I select.

One other question - since it will be in an Excel app, how do I limit the
Open File dialog box so that it only shows .xls files?

Public Sub OpenFile()
'Save the current folder path
ProperPath = CurDir
'Change the folder path
ChDrive "C"
ChDir "C:\My Path\My Folder"
'Display the open dialog box
fName = Application.GetOpenFilename
ActiveWorkbook.Save Filename:=fName ' << causes error "wrong number of
arguments or invalid property assignment"
'Change the default folder
ChDrive ProperPath
ChDir ProperPath
End Sub

TIA

--

Dave Peterson

Open Folder - Ogilvy code
 

Hi,

[snip]
will not actually open the file I select.

[snip]
One other question - since it will be in an Excel app, how do I limit the
Open File dialog box so that it only shows .xls files?


To open (rather than save) the file, perhaps replace:

fName = Application.GetOpenFilename
ActiveWorkbook.Save Filename:=fName

with:

fName = Application.GetOpenFilename _
(fileFilter:="Excel Files (*.xls), *.xls")
Workbooks.Open Filename:=fName


---
Regards,
Norman

Open Folder - Ogilvy code
 

I must have been having a bad day if I wrote that. The others have given
you plenty of information, but to put it all together:

Public Sub OpenFile()
'Save the current folder path
ProperPath = CurDir
'Change the folder path
ChDrive "C"
ChDir "C:\My Path\My Folder"
'Display the open dialog box
fName = Application.GetOpenFilename(fileFilter:="Excel Files (*.xls),
*.xls")
set wkbk = Workbooks.Open(fName)
'Change the default folder
ChDrive ProperPath
ChDir ProperPath
End Sub

Do as SaveAs on a file

Public Sub SaveAsFile()
Dim fName as String
Dim ProperPath as String
'Save the current folder path
ProperPath = CurDir
'Change the folder path
ChDrive "C"
ChDir "C:\My Path\My Folder"
'Display the open dialog box
fName = Application.GetSaveAsFilename(fileFilter:="Excel Files (*.xls),
*.xls")
if fName < "False" then
' silently overwrite any existing file with this name
Application.DisplayAlerts = False
Activeworkbook.SaveAs Filename:=fName
Application.DisplayAlerts = True
end if
'Change the default folder
ChDrive ProperPath
ChDir ProperPath
End Sub

Both of those worked for me.

--
Regards,
Tom Ogilvy

wrote in message
...
In Excel 2002:

I found the code below from a Tom Ogilvy post. I want to attach a macro
to
a toolbar that will open the directory of my choosing. The code errors
out
on ActiveWorkbook.Save Filename:=fName. If I remove that line, the macro
will bring up the Open File dialog box, but will not actually open the
file
I select.

One other question - since it will be in an Excel app, how do I limit the
Open File dialog box so that it only shows .xls files?

Public Sub OpenFile()
'Save the current folder path
ProperPath = CurDir
'Change the folder path
ChDrive "C"
ChDir "C:\My Path\My Folder"
'Display the open dialog box
fName = Application.GetOpenFilename
ActiveWorkbook.Save Filename:=fName ' << causes error "wrong number of
arguments or invalid property assignment"
'Change the default folder
ChDrive ProperPath
ChDir ProperPath
End Sub

TIA

Open Folder - Ogilvy code
 

Thanks guys! : )





"Tom Ogilvy" wrote in message
...
I must have been having a bad day if I wrote that. The others have given
you plenty of information, but to put it all together:

Public Sub OpenFile()
'Save the current folder path
ProperPath = CurDir
'Change the folder path
ChDrive "C"
ChDir "C:\My Path\My Folder"
'Display the open dialog box
fName = Application.GetOpenFilename(fileFilter:="Excel Files (*.xls),
*.xls")
set wkbk = Workbooks.Open(fName)
'Change the default folder
ChDrive ProperPath
ChDir ProperPath
End Sub

Do as SaveAs on a file

Public Sub SaveAsFile()
Dim fName as String
Dim ProperPath as String
'Save the current folder path
ProperPath = CurDir
'Change the folder path
ChDrive "C"
ChDir "C:\My Path\My Folder"
'Display the open dialog box
fName = Application.GetSaveAsFilename(fileFilter:="Excel Files (*.xls),
*.xls")
if fName < "False" then
' silently overwrite any existing file with this name
Application.DisplayAlerts = False
Activeworkbook.SaveAs Filename:=fName
Application.DisplayAlerts = True
end if
'Change the default folder
ChDrive ProperPath
ChDir ProperPath
End Sub

Both of those worked for me.

--
Regards,
Tom Ogilvy

wrote in message
...
In Excel 2002:

I found the code below from a Tom Ogilvy post. I want to attach a macro
to
a toolbar that will open the directory of my choosing. The code errors
out
on ActiveWorkbook.Save Filename:=fName. If I remove that line, the
macro
will bring up the Open File dialog box, but will not actually open the
file
I select.

One other question - since it will be in an Excel app, how do I limit
the
Open File dialog box so that it only shows .xls files?

Public Sub OpenFile()
'Save the current folder path
ProperPath = CurDir
'Change the folder path
ChDrive "C"
ChDir "C:\My Path\My Folder"
'Display the open dialog box
fName = Application.GetOpenFilename
ActiveWorkbook.Save Filename:=fName ' << causes error "wrong number
of
arguments or invalid property assignment"
'Change the default folder
ChDrive ProperPath
ChDir ProperPath
End Sub

TIA

Open Folder - Ogilvy code
 

And since I don't actually read the text of the posts--just the code, thanks for
changing my .saveas to .open <vbg.



Tom Ogilvy wrote:

I must have been having a bad day if I wrote that. The others have given
you plenty of information, but to put it all together:

Public Sub OpenFile()
'Save the current folder path
ProperPath = CurDir
'Change the folder path
ChDrive "C"
ChDir "C:\My Path\My Folder"
'Display the open dialog box
fName = Application.GetOpenFilename(fileFilter:="Excel Files (*.xls),
*.xls")
set wkbk = Workbooks.Open(fName)
'Change the default folder
ChDrive ProperPath
ChDir ProperPath
End Sub

Do as SaveAs on a file

Public Sub SaveAsFile()
Dim fName as String
Dim ProperPath as String
'Save the current folder path
ProperPath = CurDir
'Change the folder path
ChDrive "C"
ChDir "C:\My Path\My Folder"
'Display the open dialog box
fName = Application.GetSaveAsFilename(fileFilter:="Excel Files (*.xls),
*.xls")
if fName < "False" then
' silently overwrite any existing file with this name
Application.DisplayAlerts = False
Activeworkbook.SaveAs Filename:=fName
Application.DisplayAlerts = True
end if
'Change the default folder
ChDrive ProperPath
ChDir ProperPath
End Sub

Both of those worked for me.

--
Regards,
Tom Ogilvy

wrote in message
...
In Excel 2002:

I found the code below from a Tom Ogilvy post. I want to attach a macro
to
a toolbar that will open the directory of my choosing. The code errors
out
on ActiveWorkbook.Save Filename:=fName. If I remove that line, the macro
will bring up the Open File dialog box, but will not actually open the
file
I select.

One other question - since it will be in an Excel app, how do I limit the
Open File dialog box so that it only shows .xls files?

Public Sub OpenFile()
'Save the current folder path
ProperPath = CurDir
'Change the folder path
ChDrive "C"
ChDir "C:\My Path\My Folder"
'Display the open dialog box
fName = Application.GetOpenFilename
ActiveWorkbook.Save Filename:=fName ' << causes error "wrong number of
arguments or invalid property assignment"
'Change the default folder
ChDrive ProperPath
ChDir ProperPath
End Sub

TIA



--

Dave Peterson