1

I am looking for a VBA solution for doing simultaneous task such as rename active file, choose folder where to save the renamed file and delete old version of file. "Save as" dialogue allow the first two part of this requirement but does not delete the ex. file.

A good solution of this issue is posted by Oliver Salzburg who is also member of this forum and posted here. However that code works for Microsoft Word only I tried for Microsoft excel with little modification (ActiveDocument to ActiveWorkbook) aimed for excel but still does not work in Excel. The Oliver Salzburg VBA code with minor modification as follows here

   Sub MacroXL()
' Store original name
Dim sOriginalName As String
sOriginalName = ActiveWorkbook.FullName

' Save As
Dim sFilename As String, fDialog As FileDialog, ret As Long
Set fDialog = Application.FileDialog(msoFileDialogSaveAs)
ret = fDialog.Show
If ret <> 0 Then
    sFilename = fDialog.SelectedItems(1)
Else
    Exit Sub
End If
Set fDialog = Nothing

' Don't replace the original file
If sFilename = sOriginalName Then Exit Sub

 ActiveWorkbook.SaveAs2 Filename:=sFilename, FileFormat:= _
    wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _
    :=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
    :=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
    SaveAsAOCELetter:=False, CompatibilityMode:=14

' Delete original
Kill sOriginalName
End Sub

While debugging error shows as attached:

enter image description here

In this location:

enter image description here

  ActiveWorkbook.SaveAs Filename:=sFilename, FileFormat:= _
    wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _
    :=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
    :=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
    SaveAsAOCELetter:=False, CompatibilityMode:=14

Hope @Oliver Salzburg or someone with VBA skill would be able fix the above minor problem. Thank in advance.

9
  • 1
    Please note that Super User is not a script writing service. If you tell us what you have tried so far (including any scripts you are using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?.
    – DavidPostill
    May 11, 2016 at 18:19
  • So you ran some code from the internet and it didn't work. Did you try fixing it? May 11, 2016 at 18:52
  • @ Raystafarian. Another member of superuser (this group). To give credit where its due, I explicitly mentioned his username as well link where it posted. Did I miss something? May 11, 2016 at 19:03
  • @DavidPostill I update the question and efforts made so far. I think my question is very similar to other typical user questions posted here. I did my research of the issue, looked for similar solution in previous questions, identified the similar one, tried it for my requirements. Since it didn't work as expected even modification for excel I made request to same this forum if someone could able to do minor fix or suggestion. I think that's how this forum works. To give credit where its due I clearly linked previous similar solution and username. May 11, 2016 at 19:31
  • "While debugging error shows in this location" what error exactly?
    – DavidPostill
    May 11, 2016 at 19:33

1 Answer 1

0

In this:

ActiveWorkbook.SaveAs Filename:=sFilename, FileFormat:= _
    wdFormatXMLDocument, [etc etc etc]

wdFormatXMLDocument is a Word constant. It's only defined in Word. I don't know about the other constants, but try recording a macro while you save an Excel file with the options you want.

This might do the job:

ActiveWorkbook.SaveAs Filename:=sFilename, _
    FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
3
  • ActivePresentation.SaveAs Filename:=sFilename, FileFormat:=ppSaveAsDefault (ppSaveAsDefault is a PowerPoint constant, a Long = 11) May 12, 2016 at 14:37
  • And more generally, if you want to automate any of the Office apps and need to know the particulars, go into the VBA IDE for that app and press F2 for the object browser and/or let Intellisense give you hints. In Excel or Word, you can also record a macro to get a few ideas. PPT doesn't have a macro recorder any longer. May 12, 2016 at 14:39
  • Thanks Steve for helpful hints in addition to answer of this question May 12, 2016 at 15:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .