Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-01-2014, 04:19 AM
Smithy02468 Smithy02468 is offline Saves as dialog in Excel Windows 7 64bit Saves as dialog in Excel Office 2010 64bit
Novice
Saves as dialog in Excel
 
Join Date: Oct 2014
Posts: 9
Smithy02468 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 10-02-2014, 12:04 AM
macropod's Avatar
macropod macropod is online now Saves as dialog in Excel Windows 7 64bit Saves as dialog in Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
PS: When posting code, please use the code tags. They're on the 'Go Advanced' tab at the bottom of this screen.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 10-03-2014, 06:18 AM
Smithy02468 Smithy02468 is offline Saves as dialog in Excel Windows 7 64bit Saves as dialog in Excel Office 2010 64bit
Novice
Saves as dialog in Excel
 
Join Date: Oct 2014
Posts: 9
Smithy02468 is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 10-03-2014, 02:08 PM
macropod's Avatar
macropod macropod is online now Saves as dialog in Excel Windows 7 64bit Saves as dialog in Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #5  
Old 10-13-2014, 07:54 AM
Smithy02468 Smithy02468 is offline Saves as dialog in Excel Windows 7 64bit Saves as dialog in Excel Office 2010 64bit
Novice
Saves as dialog in Excel
 
Join Date: Oct 2014
Posts: 9
Smithy02468 is on a distinguished road
Default

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
Reply With Quote
Reply

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
Saves as dialog in Excel Method to update saves to all copies aultduell Word 1 11-24-2006 12:50 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:17 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft