Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    Ontario, Canada
    Posts
    57
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I very frequently open a csv file, manipulate it a bit, and the save it under the same name, in a different directory, as an xls file. I want to have a macro that automatically does the saveas bit.

    I have tried the following:
    Code:
    Sub closecsv()
        fname = Application.GetSaveAsFilename(filefilter:="Microsoft Office Excel Workbook (*.xls), *.xls")
        If fname <> False Then
            ActiveWorkbook.SaveAs Filename:=fname, FileFormat:=xlNormal
        End If
    End Sub
    but it doesn't do exactly what I want. It comes back with the saveas dialog box, but the filename is shown as "abcde.csv" (note quotation marks and csv). The Saveas Type is correctly shown as Microsoft Office Excel Workbook (*.xls)

    This means I now have to go and remove the quotation marks and the .csv , and this leaves me with more keystrokes than doing it manually in the first place. What am I missing?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try this version:

    Code:
    Sub CloseCSV()
     Dim fName
     Dim strName As String
     strName = Replace(ActiveWorkbook.Name, ".csv", ".xls")
     fName = Application.GetSaveAsFilename(InitialFileName:=strName, _
      FileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls")
     If fName <> False Then
      ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlNormal
     End If
    End Sub

  3. #3
    Star Lounger
    Join Date
    Mar 2001
    Location
    Ontario, Canada
    Posts
    57
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Hans. That worked great.

Posting Permissions

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