CODEWITHSUNDEEP
excelvbafilenames
user3120554
user3120554

Reputation: 681

Apply VBA code in ribbon button to renamed Excel file

I have a ribbon button which once clicked will open a dialogue box to let me attach a file to my spreadsheet. I recorded a macro and edited the code with the help of Google. It works.

The filename regularly gets updated when changes are made. Once renamed the code breaks.

If I understand, the code is linked to both the filename and the worksheet name. The worksheet won't change. Is there any way to make the code independent of the filename?

The Excel file is on a shared drive that others can view/edit, so it would be nice to make a file attaching button, that anyone can use.

The code is in Excel on the code sheet for the worksheet tab:

Sub AttachEmail()

Set myFile = Application.FileDialog(msoFileDialogOpen)
With myFile
    .Title = "Choose File"
    .AllowMultiSelect = False
    If .Show <> -1 Then
        Exit Sub
    End If
    FileSelected = .SelectedItems(1)
End With

ActiveSheet.OLEObjects.Add(Filename:=FileSelected, Link:=False, DisplayAsIcon:=False).Select
End Sub

It's a macro enabled workbook. There's a version in the file name "...v1a" and people rename this v1b, v1c, v2a.

If I close it, rename it, then open it. I click the button to "Enable content". When I use my button I get an error message

Sorry we couldn't find <path+file name>. Is it possible it was moved, renamed, or deleted?

Is there a way I can use this ribbon button without having to manually remove the macro and reattach it every time I rename the file?

Upvotes: 1

Views: 2634

Answers (2)

Salix
Salix

Reputation: 1384

Okay I figured it out but the search for it wasn't fun so since this was the first link to pop up, I'll answer here and maybe help others to figure it out faster.

The problem is that excel save the macro action as 'file_path/file_name.xlms!macro_name'. So the solution is to export the ribbon customization by going to 'customize ribbon' then 'import/export' then 'export all customization'. Then open that with a text editor. Optional: make a new xml file so you can format it in a readable way. Then, for each of your macro, find the mso:tab and mso:group it's in and change

<mso:button
  idQ="x1:file_path/file_name.xmls!ThisWorkbook.macro_name_0"
  label="Macro Label" imageMso="ImageMso"
  onAction="file_path/file_name.xmls!ThisWorkbook.macro_name"
  visible="true" />

for

<mso:button
  idQ="x1:ThisWorkbook.macro_name_0"
  label="Macro Label" imageMso="ImageMso"
  onAction="ThisWorkbook.macro_name"
  visible="true" />

Note: 'ThisWorkbook.' may or may not be there depending on where you saved your macro. I had saved it in "ThisWorkbook' and not a module or a sheet.

Upvotes: 0

Comintern
Comintern

Reputation: 22185

From the documentation for the FileDialog object:

Each host application can only create a single instance of the FileDialog object. Therefore, many of the properties of the FileDialog object persist even when you create multiple FileDialog objects. Therefore, make sure that you set all of the properties appropriately for your purpose before you display the dialog box.

I suspect that if your Excel instance isn't closing, one of those properties is persisting (and giving your error the second time you use it).

The most likely candidate based on the message you're receiving is the InitialFileName (my emphasis):

If you specify an invalid path, the last-used path is used. A message warns users when an invalid path is used.

I'd add the following line of code to ensure that you always will always get a valid value:

With myFile
    .Title = "Choose File"
    .AllowMultiSelect = False
    'Add this.
    .InitialFileName = ActiveWorkbook.Path  'Or ThisWorkbook.Path

Note that if the workbook hasn't already been saved, this will default to the user's documents folder.

Upvotes: 1

Related Questions