Open and save a file including an asterisk using a macro in an excel report

When I do automatic things, I like to open and/or to save my report in an automatic way too.

 

When I use the macro ?

To open and/or to save my report automatically.

 

How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report

 

How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report

 

How is/are the macro(s) ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.

To open the file in the same folder:


' change namefile by the name of your file
' using wildcard "\name*.xlsx"
Workbooks.Open(ThisWorkbook.Path & "\namefile.xlsx")

To open the file in another folder:


' change path by the full path where to find the file and change namefile by the name of your file
Workbooks.Open("\\path\namefile.xlsx")

With a wildcard, to use only if the file is already open so the macro will include in its code:


Sub test()
Dim i As Workbook
For Each i In Workbooks
  ' change namefile by the name of your file
  If i.Name Like "*namefile.xlsx" Then
    i.Activate
    Exit For
  End If
Next i
End Sub

The wildcard, alias star/asterisk, is useful if the name of your file changes every time but some parts of the name should have some commons. For instance, imagine that your file has a number at the beginning (10-namefile.xlsx) and this number changes every month, so you will use this code. The same thing but for a sheet name:


Sub test()
Dim i As Workbook
Dim sht As Worksheet
' change namefile
Set i = Workbooks("namefile.xlsx")
For Each sht In i.Worksheets
  ' change namesheet and A2:C20
  If sht.Name Like "namesheet*" Then
    Range("A2:C20").Copy
  End If
Next sht
End Sub

To combine both wildcards, first do the file section then the sheet section or vise versa. To save the file in the same folder:


' instead of this, I can use ActiveWorkbook.Save
ThisWorkbook.Path & "\"

To save the file in another folder:


' to save with another name, change ActiveWorkbook.Name by "namefile.xlsx"
ActiveWorkbook.SaveCopyAs "\\path\" + ActiveWorkbook.Name

Interesting Topics