#1
|
|||
|
|||
Saves as dialog in Excel
I am trying to set up a spreadsheet where it copies a worksheet to another workbook and brings up the save as dialog box in a specific folder on the network,
the code i am using is as below. Sheets("Target_Dates").Select Sheets("Target_Dates").Copy With Application.Dialogs(xlDialogSaveAs) .InitialFileName = "N:\BES-DATA\traffman\traffic\" If Not .Show Then MsgBox "User cancelled without saving." Exit Sub End If End With For some reason it does not work and produces an error message "run time error 438 Object does not support this property or methiod" Any help or suggestion gretly appreciated. Colin |
#2
|
||||
|
||||
InitialFileName Property applies to a file dialog box, not to a Save As dialog box.
Try something along the lines of: Code:
Dim StrFold As String If ActiveWorkbook.Path = "" Then StrFold = Application.DefaultFilePath Else StrFold = ActiveWorkbook.Path End If ChDir "N:\BES-DATA\traffman\traffic\" With Application.Dialogs(xlDialogSaveAs) If Not .Show = -1 Then MsgBox "User cancelled without saving." End If End With ChDir StrFold
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Paul,
This looks workable however it doesn't seem to register the location i want it to use, i.e. "N:\BES-DATA\traffman\traffic\" maybe misunderstood. I wanted it to default to this directory to save the file to rather than have to lcick through all the various folders to get to this. Thanks again for any help you can give. Regards Colin |
#4
|
||||
|
||||
For me, it opens the Save As dialogue pointing to whatever folder I've specified via ChDir. So I know that aspect works. If you're not getting the correct results, that suggests your folder name is not N:\BES-DATA\traffman\traffic\.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
I stand corrected, somewhere along the line i seem to have done something wrong, i recopied the code and inserted again and it worked!
thanks again Colin |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Power Point saves in GREYSCALE! | Bluebirdie | PowerPoint | 2 | 03-14-2012 11:17 AM |
Format Autoshape Dialog Box Crashes Excel | BjornP | Excel | 0 | 03-01-2012 01:58 PM |
OFFICE(Word,Excel..)-crash/close when DIALOG BOX launched of insert image,open file | buggingme | Office | 1 | 05-30-2010 12:18 AM |
Outlook Form that Saves Data to Excel | dcbrown | Outlook | 0 | 12-29-2008 06:25 PM |
Method to update saves to all copies | aultduell | Word | 1 | 11-24-2006 12:50 PM |