none
Macro to name a file RRS feed

  • Question

  • I have a file that exports as a TXT file.  I need to save the file to C:\SamsRept and would like the macro to prompt me for a name and update the save as type to .xlsx (we are using 2010).

    I've looked at several posts and they seem to be different variations and I can't get this to work.  I would appreciate some help please.

    Thursday, April 4, 2013 10:33 PM

Answers

  • Try:

    Sub fExample()
        Dim fft As XlFileFormat
        
        fft = Application.DefaultSaveFormat
        Application.DefaultSaveFormat = xlOpenXMLWorkbook
        Application.Dialogs(xlDialogSaveAs).Show
        Application.DefaultSaveFormat = fft
    End Sub


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    Thursday, April 4, 2013 11:20 PM
  • In new OS you cant save file in main system folder. You should create subfolder like c:\somefolder\your_file.ext

    To check existing file you can use function:

    Public Function FileExists(FilePath As String) As Boolean
    On Error GoTo blad
    If Len(FilePath) = 0 Then Exit Function
    FileExists = Len(Dir(FilePath, vbDirectory Or vbHidden Or vbSystem)) > 0
    Exit Function
    blad:
    End Function

    If function return True then you can set new name

    To open and save, I always using "open for file" method.


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Friday, April 5, 2013 8:10 AM
    Answerer

All replies

  • Try:

    Sub fExample()
        Dim fft As XlFileFormat
        
        fft = Application.DefaultSaveFormat
        Application.DefaultSaveFormat = xlOpenXMLWorkbook
        Application.Dialogs(xlDialogSaveAs).Show
        Application.DefaultSaveFormat = fft
    End Sub


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    Thursday, April 4, 2013 11:20 PM
  • In new OS you cant save file in main system folder. You should create subfolder like c:\somefolder\your_file.ext

    To check existing file you can use function:

    Public Function FileExists(FilePath As String) As Boolean
    On Error GoTo blad
    If Len(FilePath) = 0 Then Exit Function
    FileExists = Len(Dir(FilePath, vbDirectory Or vbHidden Or vbSystem)) > 0
    Exit Function
    blad:
    End Function

    If function return True then you can set new name

    To open and save, I always using "open for file" method.


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Friday, April 5, 2013 8:10 AM
    Answerer