• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Application.Dialogs(xlDialogSaveAs).Show ActiveWorkbook.Path

M

Mr.Karr

Member
Hello

Can anyone help how to change the below code snippet to show save as dialogue box with default user desktop or temp folder ?

Code: 
Application.Dialogs(xlDialogSaveAs).Show ActiveWorkbook.Path

Many thanks in advance
 
To change the start path, you would change the portion of code after Show (currently set to start in active workbook's folder). The tricky part of your request is that the desktop's file path is variable.

C:\Users\<user name>\Desktop

You would need to know what the Window's user name was and fill in the correct name. IF the user has default settings, you might be able to guess like this:
Code: 
Sub ChangePath()
Dim xName As String
Dim xPath As String
'Assumes folder path is under same name as Windows login
xName = Environ("USERNAME")
xPath = "C:\Users\" & xName & "\Desktop"
Application.Dialogs(xlDialogSaveAs).Show xPath
End Sub
but it isn't 100% fool proof.
 
thanks @Luke M.
Can we redirect dialogue to a temp folder or something which is available in common with desktops. something like C:\ etc. Pls advise
 
Certainly, but you'll have the same issue. The temp folder is a subdirectoy of user folder, so you'd still need to know the name.

Or, if you know a folder will exist, then you can hard code it like:
Code: 
Sub ChangePath()
'If path is known
Dim xPath As String

xPath = "C:\temp"
Application.Dialogs(xlDialogSaveAs).Show xPath
End Sub
 
@Luke M @NARAYANK991:
Not sure if I need to create a separate post for this. Can you please help to get the filename to appear in the save as dialogue box. There is a challenging scenario as I use 2 different excel workbooks.

In elaborate: imagine I'm working with 2 different workbooks. With the use of macrobutton in the activeworkbook, I try to move data to another workbook (template) which is saved in a particular folder. Since that template has pop-ups and alters at the open-workbook module, the data moves with muting all those alerts.

Now the problem is; though I gave .initialFileName to take range from that template, it doesn't pull up the value from it.

Please advise if we can able to save activeworkbook's filename in memory and paste it in the dialogue box ? or any other suggestions please advise.
 
Hi Karthik ,

I am not able to visualize the problem ; you can wait for Luke or anyone else to reply , or you can take a concrete example , with file names , range names , anything else that will help explain ; preferably upload the relevant files.

Narayan
 
Hi Narayan,
Sure, I will help providing more information.
File1 - workbook being used which is an activeworkbook
File2 - a template which is saved in a particular folder.

The main intention is to move data from activeworkbook to the template. Once data moved successfully, save as dialogue open up over the template. I hope this is clear till here.

how to have the default file1 name to show up in save as dialogue ? Pls feel free to ask if any of this is unclear to you. I am getting head-ache of this since mrng.
Please advise!
 
Hi Karthik ,

How is the template file going to be saved as , as a template or as a workbook ? Or is the template actually a workbook ?

How is the template file being opened ?

If the template file is already open when the macro is run , will just saving the template file not save it in the location from where it was opened ?

Narayan
 
Hi Narayan,

Please have a look at the code here:
Code: 
Dim wbSource As Workbook, wbDest As Workbook
    Dim wsSource As Worksheet, wsDest As Worksheet
 
    Dim LR As Long
    Dim fPath As String
   
    On Error Resume Next
   
 
    'Where is the file of interest?
  fPath = "\\ServerAddress\Data Backup\KT\Update\File2.xlsm"

    Set wbSource = ThisWorkbook
 
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Set wbDest = Workbooks.Open(fPath)

 
    For Each wsSource In wbSource.Worksheets
        With wsSource
            Select Case .Name
            Case "Home", "Data", "Master", "Metrics", "IntelliSense", "Dashboard", "SmartBoard", _
            "YTDMetrics", "WeeklyReporting", "Pending Tasks", "Sheet7", "PPTWizard", "Share", _
            "UpdateTab", "Sheet2", "Validate", "Calendar", "Instructions", "Location", "DataFeed", _
            "FAQs", "Sheet1", "Version Control", "Reporting", "Welcome"
                'Do nothing, ignore these sheets
          Case Else
                'Check if sheet exists
              Set wsDest = Nothing
                On Error Resume Next
                Set wsDest = wbDest.Worksheets(.Name)
                On Error GoTo 0
                If Not wsDest Is Nothing Then
                  LR = .Range("A" & .Rows.Count).End(xlUp).Row + 1
                    .Range("A21:A" & LR).EntireRow.Copy wsDest.Range("A21")
                End If
            End Select
        End With
    Next wsSource
 
    'Save As will happen to the active workbook
  wbDest.Activate
    Application.Dialogs(xlDialogSaveAs).Show
    wbDest.Close
    Application.EnableEvents = True
    Application.ScreenUpdating = True

Template file is actually a .xlsm file. It would be saved the same way. What the above code does is; it will copy data from specific files using case select. And once done, it activates the xlDialogSaveAs.

Can we have the File1 name to be available somewhere with the copied sheets and pull from the template? But the problem is, it is not taking values from the range as we have events and screenupdating turned off.

Please advise
 
Hi Karthik ,

I do not know what File1 you are talking about.

The code is opening a file File2.xlsm from a specified path. This is designated the destination file.

The source file is the one where the code is stored.

The code is copying the specified worksheets data from the source file to the destination file ; thereafter it is saving the destination file. Why is a SaveAs required ? Are you changing the save path ?

Narayan
 
Hi Narayan,

File1 is activeworkbook which user is using. The macro enabled file from which the button is clicked. Then the macro runs and moves data from file1 to file2 (template).

yes, file1 is the source file where the code is saved.

Actually the template file is saved in a sharedrive to facilitate multiple users so we dont want them to make any changes with that file. So I kept save as so to give an option for the users to save it in their desktop.

Now I'm struck here, how to populate save as name the same as file1's name which is source workbook.
 
Hi Karthik ,

The problem is a logical one ; you cannot have 2 files open at the same time with the same name.

When you try to do a SaveAs of file2.xlsm changing its name to file1.xlsm , Excel will not allow this , even though you are trying to save file1.xslm to a different folder.

You either need to do a SaveAs under a different name ( say by prefixing the name with the text Copy of ) , or by doing a SaveAs in the .xlsx format if that is acceptable.

Narayan
 
Hi Karthik ,

See if this works :
Code: 
Public Sub SaveFile()
          Dim wbSource As Workbook, wbDest As Workbook
          Dim wsSource As Worksheet, wsDest As Worksheet
          Dim strDirectoryPath As String, strfilename As String
          Dim objShell As Object
        
          Dim LR As Long
          Dim fPath As String

          On Error Resume Next
'          Where is the file of interest?
          fPath = "\\ServerAddress\Data Backup\KT\Update\File2.xlsm"

          Set wbSource = ThisWorkbook
          Set wbDest = Workbooks.Open(fPath)
          strfilename = wbSource.Name
          Set objShell = CreateObject("WScript.Shell")
          strDirectoryPath = objShell.Specialfolders.Item("Desktop")
         
          Application.ScreenUpdating = False
          Application.EnableEvents = False
         
          For Each wsSource In wbSource.Worksheets
              With wsSource
                    Select Case .Name
                          Case "Home", "Data", "Master", "Metrics", "IntelliSense", "Dashboard", "SmartBoard", _
            "YTDMetrics", "WeeklyReporting", "Pending Tasks", "Sheet7", "PPTWizard", "Share", _
            "UpdateTab", "Sheet2", "Validate", "Calendar", "Instructions", "Location", "DataFeed", _
            "FAQs", "Sheet1", "Version Control", "Reporting", "Welcome"
'                              Do nothing, ignore these sheets
                          Case Else
'                              Check if sheet exists
                                Set wsDest = Nothing
                                On Error Resume Next
                                Set wsDest = wbDest.Worksheets(.Name)
                                On Error GoTo 0
                                If Not wsDest Is Nothing Then
                                  LR = .Range("A" & .Rows.Count).End(xlUp).Row + 1
                                  .Range("A21:A" & LR).EntireRow.Copy wsDest.Range("A21")
                                End If
                    End Select
              End With
          Next wsSource

'          Save As will happen to the active workbook
           wbDest.SaveAs strDirectoryPath & "\" & "Copy of " & strfilename, FileFormat:=52
          wbDest.Close
        
          Application.EnableEvents = True
          Application.ScreenUpdating = True
End Sub
Narayan
 
Hi Narayan,

It works like a breeze but there is only is userforms are showing up from the file2 which we have at the openWorkbook module. That's fine.

Is there a way to kill file1 and keep file2 open ?

May be Can I request you to modify the code to kill file2 and have the same name of file2 for file1 and keep the file1 open please ?
 
Hi Karthik ,

Let us stick to the source and destination nomenclature ; file1 and file2 is difficult to keep track of.

You are opening the destination workbook , which is your template file.

After copying specified ranges from the source workbook , which is the workbook which has the code , to the destination workbook , you wish to rename the modified template file to the name of the source file , and save it to the user's desktop. Is this correct ?

Since the code is in the source workbook , the source file cannot be closed first ; the destination file has to be saved first.

Narayan
 
Hi Narayan,

Yes, that's correct. is there a way to delete the source file once data copied from it and keep the template open ? or any other way ?

Please advise
 
Hi Karthik ,

I don't think that is possible ; the only thing I can think of is to have the code in the template , so that the source file is used only for copying the ranges to the template file ; thereafter , the source file can be closed , and the modified template file can be saved on the Desktop , with the same name as the source file.

Narayan
 
You must log in or register to reply here.
Back
Top