macro free save sub

[rickblunt]

Hello,
I am using the sub below to send out a single sheet to a new workbook on our network drive, in effect making a "back-up" copy for auditors to review. It works great but I always get the pop-up asking me if I want to make it macro free or enabled. Ideally it would be macro free, but I can live with either option, I just do not want the pop-up to appear anymore. Going through the macro recorder I cannot seem to figure out what syntax I need to default this to macro-free. Can anyone guide me on this one?

Also, our company folder protocol is date_file name, but as you can see on the sub I have it as filename_date, I am tanking on flipping those around as well. Putting the date stamp syntax in front did not work, or I used the wrong syntax. Any help on that would be appreciated as well - many thanks.

Sub ExportReport()
'
' ExportReport Macro
'
  Dim wb As Workbook
  
  Sheets("Emergency Lighting Log").Copy
  Set wb = ActiveWorkbook
  With wb
    .SaveAs "N:\Facilities\DeptData\EH&S\Compliance and EHS\Facilities Inspections\Emergency Lighting\Emergency Lighting Report  " & Format(Now, "yy_mmdd") & ".xlsx"
    '.Close False
  End With
End Sub

 

[JoeMo]

Add the file format to your saveAs:

.SaveAs "N:\Facilities\DeptData\EH&S\Compliance and EHS\Facilities Inspections\Emergency Lighting\Emergency Lighting Report  " & Format(Now, "yy_mmdd") & ".xlsx" FileFormat:= 51

 

[rickblunt]

Thanks JoeMo for the quick reply, Here is my new code but I am still getting the pop-up, did I put it in the wrong place or did I leave out a character or two?

Sub ExportReport()
'
' ExportReport Macro
'
  Dim wb As Workbook
  
  Sheets("Emergency Lighting Log").Copy
  Set wb = ActiveWorkbook
  With wb
    .SaveAs "N:\Facilities\DeptData\EH&S\Compliance and EHS\Facilities Inspections\Emergency Lighting\Emergency Lighting Report  " & Format(Now, "yy_mmdd") & ".xlsx", FileFormat:=51
    
    .Close True
  End With
End Sub

 

[JoeMo]

Try:

With wb
    Application.DisplayAlerts = False
    .SaveAs _
        "N:\Facilities\DeptData\EH&S\Compliance and EHS\Facilities Inspections\Emergency Lighting\Emergency Lighting Report  " _
        & Format(Now, "yy_mmdd") & ".xlsx", FileFormat:=51
    .Close True
    Application.DisplayAlerts = True
  End With

 

[rickblunt]

Thanks JoeMo, for the continuing help. The applications event sounds great, I have never used that before; I will add that to my book of knowledge. I have changed my code to the following but I am getting an object error on the "Applications.DisplayAlerts = False" event. When I disable this step the code runs fine, so I guess the sub doesn't know what the object is, wouldn't that be Excel? Do I need to dim the application or something? Not sure why the sub does not know what the object is....

(I do always try to figure these things out before I reply back, but I am still learning...)

Sub ExportReport()

  Dim wb As Workbook
  
  Sheets("Emergency Lighting Log").Copy
  Set wb = ActiveWorkbook
  With wb
    Applications.DisplayAlerts = False
    .SaveAs _
         "N:\Facilities\DeptData\EH&S\Compliance and EHS\Facilities Inspections\Emergency Lighting\Emergency Lighting Report  " _
          & Format(Now, "yy_mmdd") & ".xlsx", FileFormat:=51
   .Close True
   Applications.DisplayAlerts = True
  End With
End Sub

 

[JoeMo]

Thanks JoeMo, for the continuing help. The applications event sounds great, I have never used that before; I will add that to my book of knowledge. I have changed my code to the following but I am getting an object error on the "Applications.DisplayAlerts = False" event. When I disable this step the code runs fine, so I guess the sub doesn't know what the object is, wouldn't that be Excel? Do I need to dim the application or something? Not sure why the sub does not know what the object is....

(I do always try to figure these things out before I reply back, but I am still learning...)

Sub ExportReport()

  Dim wb As Workbook
  
  Sheets("Emergency Lighting Log").Copy
  Set wb = ActiveWorkbook
  With wb
    Applications.DisplayAlerts = False
    .SaveAs _
         "N:\Facilities\DeptData\EH&S\Compliance and EHS\Facilities Inspections\Emergency Lighting\Emergency Lighting Report  " _
          & Format(Now, "yy_mmdd") & ".xlsx", FileFormat:=51
   .Close True
   Applications.DisplayAlerts = True
  End With
End Sub

It's Application.DisplayAlerts not Applications.DispalyAlerts.

In case you are not aware, the best way to incorporate code that's been posted is to copy the code directly from your browser and paste it where required. Saves typing and avoids typos.

 

[rickblunt]

Thanks JoeMo, it works fine. I almost always copy/paste the code in to sub, but then I sometimes manually re-type it in so that I can look a the syntax more closely and get muscle memory going. I am really trying to understand all of the little characters and learn what they are for, in this case I inadvertently added the "s"; and I did not pick it back up when I was trying to figure out what I had typed wrong. It's been a long couple of weeks...

Sorry for the inconvenience and I am extremely thankful for your assistance - Rick

 

[JoeMo]

Thanks JoeMo, it works fine. I almost always copy/paste the code in to sub, but then I sometimes manually re-type it in so that I can look a the syntax more closely and get muscle memory going. I am really trying to understand all of the little characters and learn what they are for, in this case I inadvertently added the "s"; and I did not pick it back up when I was trying to figure out what I had typed wrong. It's been a long couple of weeks...

Sorry for the inconvenience and I am extremely thankful for your assistance - Rick

You are welcome. It's never an inconvenience to help.