Saving Active Workbook when using Macro from Add-In

Question

I created a macro that saves the value of an .xlsx file to a csv in a certain directory with the name of the csv = to the name of the Excel file which it was written from.

I wanted this macro to be available in any spreadsheet/workbook so I saved and added it as an add in.

I think I am having issues with ActiveWorkbook vs Thisworkbook.

The following code is the original that works as intended when not used as an add in:

Sub CSV()

Dim WS As Excel.Worksheet
Dim SaveToDirectory As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long

CurrentWorkbook = ThisWorkbook.FullName
CurrentFormat = ThisWorkbook.FileFormat

SaveToDirectory = "C:\SomeDirectory\"
For Each WS In ThisWorkbook.Worksheets
    Sheets(WS.Name).Copy
    ActiveWorkbook.SaveAs Filename:=SaveToDirectory & ThisWorkbook.Name & ".csv", FileFormat:=xlCSV
    ActiveWorkbook.Close savechanges:=False
    ThisWorkbook.Activate
Next

Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
Application.DisplayAlerts = False
End Sub

However if that code is used in the add in, the file saves with the name of the add in. So I changed the code and used ActiveWorkbook but it appears the value gets changed when it is time to save.

Sub CSV2()

On Error GoTo error_handler
Dim WS As Excel.Worksheet
Dim SaveToDirectory As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long

CurrentWorkbook = ActiveWorkbook.Name
CurrentFormat = ActiveWorkbook.FileFormat

SaveToDirectory = "C:\SomeDirectory\"
For Each WS In ActiveWorkbook.Worksheets
    Sheets(WS.Name).Copy
    ActiveWorkbook.SaveAs Filename:=SaveToDirectory & ThisWorkbook.Name & ".csv", FileFormat:=xlCSV
    ActiveWorkbook.Close savechanges:=False
    ThisWorkbook.Activate
Next

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
Application.DisplayAlerts = False

error_handler:
    MsgBox Err.Description

End Sub

I want to write my Excel file to csv. Save that CSV in the directory that is defined. With the name of the csv = the name of the file that the information is coming from. And to be able to do this in any workbook I open.

Answer 1

Try this code:

Sub CSV2()

 On Error GoTo error_handler
  Dim aWB As Workbook
  Dim WS As Excel.Worksheet
  Dim SaveToDirectory As String

  Dim CurrentWorkbook As String
  Dim CurrentFormat As Long
  Set aWB = ActiveWorkbook
  CurrentWorkbook = aWB.Name
  CurrentFormat = aWB.FileFormat

  SaveToDirectory = "C:\SomeDirectory\"
  For Each WS In aWB.Worksheets
    WS.Copy
    ActiveWorkbook.SaveAs Filename:=SaveToDirectory & aWB.Name & "_" & WS.Name & ".csv", FileFormat:=xlCSV
    ActiveWorkbook.Close savechanges:=False
    'ThisWorkbook.Activate
  Next

  Application.DisplayAlerts = False
  aWB.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
  Application.DisplayAlerts = False

Exit Sub
error_handler:
    MsgBox Err.Description

End Sub

I add ws.name after awb.name to prevent the same file name.