1

I am trying to automatically save a .xls file in a hardcoded location, in the .xlsx file format. I want the SaveAs dialog to be showing the hardcoded location, and the file name that has been coded in the "File Name:" field . This is so that all I need to do is click on the Save button.

However, the SaveAs dialog always end up showing C Drive, when I want to save my file in the H Drive.

The following are my codes: 

Option Explicit

Sub externalRatingChangeFile()

    'Declare the data type of the variables
    Dim wks As Worksheet
    Dim sFilename As String

    'Set wks to the current active worksheet
    Set wks = ActiveWorkbook.ActiveSheet

    'Set the location to save the file to a variable
    sFilename = "H:\testing file"

    'Save as .xlsx file in the specific location stated earlier
    'If there are errors in the code, set wks to nothing and end the process
    On Error GoTo err_handler
    ChDrive sFilename
    ChDir sFilename
    Application.Dialogs(xlDialogSaveAs).Show (sFilename & "\TestingFile - " & Format(Date, "YYYYMMDD") & ".xlsx")

    'System to/not display alerts to notify Users that they are replacing an existing file.
    Application.DisplayAlerts = True

    err_handler:
    'Set Wks to its default value
    Set wks = Nothing

End Sub
Improve this question
2
  • 2
    Are you trying to save the workbook or worksheet? The Dialogs(xlDialogSaveAs) will only start in an initial folder when the workbbook has not previously been saved. Commented Apr 6, 2016 at 2:52
  • I'm trying to save the workbook. The workbook is exported from an online source and has not been previously saved in any folders. Is that the reason why the SaveAs dialog keeps showing me the C Drive instead? Commented Apr 6, 2016 at 2:56

2 Answers 2

Reset to default
3

Instead of showing the Save As Dialog box, just save directly to the folder.

   Application.DisplayAlerts = False
   wks.SaveAs (sFilename + "\TestingFile - " + Format(Date, "YYYYMMDD") + ".xlsx")
   Application.DisplayAlerts = True

or 

   Application.DisplayAlerts = False
   wks.SaveCopyAs (sFilename + "\TestingFile - " + Format(Date, "YYYYMMDD") + ".xlsx")
   Application.DisplayAlerts = True

Lastly you could create your own Dialog Box to make sure you are saving in the correct location:

'Result = 2 is Cancel
'Result = 1 is Ok
result = MsgBox("Would You Like To Save in the Following Location: " + "H:\Test File....", vbOKCancel, "Save As")
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Hi Tyler, I want to be able to see that I am saving the file in the correct location with the correct file name before I save. Is there another way that I can show the dialog box?
You could use a msgbox instead. This could act as a dialog box.
@Jeeped has a solution below that should work for you.
2

While I prefer the Application.GetSaveAsFilename method (see this), setting the initial folder on a xlDialogSaveAs should be no problem providing that the original workbook has not been previously saved.

Sub externalRatingChangeFile()
    Dim bSaved As Boolean
    Dim xlsxFileFormat As XlFileFormat

    'Declare the data type of the variables
    Dim wks As Worksheet
    Dim sFilename As String

    'Set wks to the current active worksheet
    Set wks = ActiveWorkbook.ActiveSheet

    'Set the location to save the file to a variable
    sFilename = "H:\testing file"
    xlsxFileFormat = XlFileFormat.xlOpenXMLWorkbook

    'Save as .xlsx file in the specific location stated earlier
    On Error GoTo err_handler
    bSaved = Application.Dialogs(xlDialogSaveAs).Show(Arg1:=sFilename & "\TestingFile - " & Format(Date, "YYYYMMDD"), _
                                                      arg2:=xlsxFileFormat)

    'System to/not display alerts to notify Users that they are replacing an existing file.
    Application.DisplayAlerts = True

err_handler:
    'Set Wks to its default value
    Set wks = Nothing

End Sub
Improve this answer

3 Comments

@JJ2015 does your folder path exist. This method worked for me, but requires that the path actually exist prior to saving. Otherwise it will revert to the default path.
I actually ran that to my T: drive (a SD card inserted into my laptop) and it came out fine so long as it was a new workbook. Are you sure that the H:\testing file folder exists? If it doesn't then yes, it will default back to C:.
@TylerFurrer & Jeeped, it work's now! I had that error again because I realised that I was testing from a file that's saved on my desktop. But now it's all good. Thank you both so much!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.