Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA code to searching for a folder | Excel Discussion (Misc queries) | |||
VBA - open folder | Excel Discussion (Misc queries) | |||
Need code to save file to new folder, erase from old folder | Excel Discussion (Misc queries) | |||
Opening A Folder Using VB Code | Excel Programming | |||
Open Folder | Excel Programming |