Save As Macro
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Save As Macro

  1. #1
    Guest

    Default

     
    I am in the process of recording a macro and would like at the very end for the Save As dialogue box to pop up and allow the user to enter a file name. I pasted the portion of the macro I would like to change below. If anyone knows how I could get rid of the current path and allow the user to create their own every time they the macro is run I would be greatly appreciative.

    Thanks
    Will

    '
    Workbooks.Open FileName:="C:findatupEXCEL.CSV"
    ActiveWorkbook.SaveAs FileName:="C:findatuptest_change.csv", FileFormat:= _
    xlCSV, CreateBackup:=False
    ActiveWindow.Close
    End Sub

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here you go:

    Code:
    Dim SaveFileName As String
    Workbooks.Open FileName:="C:findatupEXCEL.CSV"
    SaveFileName = Application.GetSaveAsFilename
    ActiveWorkbook.SaveAs FileName:=SaveFileName, FileFormat:= _
    xlCSV, CreateBackup:=False
    ActiveWindow.Close
    Regards,
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Barries code will certainly do the trick, all I would add is a line in case they Cancel.

    Dim SaveFileName As String
    Workbooks.Open Filename:="C:\findatup\EXCEL.CSV"
    SaveFileName = Application.GetSaveAsFilename
    If SaveFileName = "False" Then Exit Sub 'Cancelled
    ActiveWorkbook.SaveAs Filename:=SaveFileName, FileFormat:= _
    xlCSV, CreateBackup:=False
    ActiveWindow.Close

    Or you could use:

    Workbooks.Open Filename:="C:\findatup\EXCEL.CSV"
    Application.Dialogs(xlDialogSaveAs).Show ("Mine")

    The "Mine" is a default name to place in the Save as name box.



  4. #4
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Nice improvement Dave

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com