Path of separate workbook

Question

I wrote a macro as an AddIn so that it is available in any of the workbooks I open. This macro will automatically save an Excel file as a CSV file. When I open a new workbook (newwb.xlsx) and apply this AddIn, I want my code to automatically find the path where I saved this newwb.xlsx and save it in the same location.

I found online that Application.ActiveWorkbook.Path can be used to locate path itself and Application.ActiveWorkbook.FullName for the path with the workbook name. However, it only returns the path name of the AddIn file and not the path name of newwb.xlsx.

How do I get the file path of the new workbook file?

Here is my code:

'Declare the data type of the variables
Dim wks As Worksheet
Dim lastCol As Integer
Dim lastRow As Long
Dim iCol As Integer
Dim iRow As Long
Dim sFilename As String
Dim cellValue As String
Dim MyTextFile

'Set wks to the current active worksheet
Set wks = ActiveWorkbook.ActiveSheet

'Set the location of the csv file to a variable
sFilename = Application.ActiveWorkbook.FullName & "\newwb.csv"

'Within the current active worksheet, identify the last interested row and column of data
'Any values such as 'a', '1' or '%' are considered as values. Spaces (Spacebars) are not considered as values.
With wks
    With .Cells(1, 1).CurrentRegion
        lastCol = .Columns.Count
        lastRow = .Rows.Count
    End With

    'Delete extra rows with blank spaces
    For iRow = 1 To lastRow
        For iCol = 1 To lastCol
            cellValue = wks.Cells(iRow, iCol)
        Next iCol

        If Trim(cellValue) = "" Then
            wks.Cells(iRow, iCol).EntireRow.Clear
            wks.Cells(iRow, iCol).EntireColumn.Clear
        End If
    Next iRow

   'Delete extra rows and columns with formats
    .Cells(lastRow + 1, 1).Resize(Rows.Count - lastRow, 1).EntireRow.Clear
    .Cells(1, lastCol + 1).Resize(1, Columns.Count - lastCol).EntireColumn.Clear
    .UsedRange.Select
End With

'Save as .CSV file in the specific location stated earlier
'If there are errors in the code when Users presses 'No' for the conversion, set wks to nothing and end the process
On Error GoTo err_handler
wks.SaveAs Filename:=sFilename, FileFormat:=xlCSV

'System to/not display alerts to notify Users that they are replacing an existing file.
Application.DisplayAlerts = True

'Notify users that the .CSV file has been saved
MsgBox sFilename & " saved"

'Opens the CSV file in a specifc location in Notepad
MyTextFile = Shell("C:\Windows\notepad.exe ActiveWorkbook.Path & /S /K  ""\newwb.csv""", vbNormalFocus)


err_handler:
'Set Wks to its default value
Set wks = Nothing

End Sub

Edit:

Application.ActiveWorkbook.Path works at the line:

sFilename = Application.ActiveWorkbook.Path & "\newwb.csv"

but there is an error saying that the system cannot find the path in this line:

MyTextFile = Shell("C:\Windows\notepad.exe Application.ActiveWorkbook.Path & /S /K  ""\newwb.csv""", vbNormalFocus)

Answer 1

ActiveWorknook.Fullname works correctly for me when used from an XLA. Note that your code

sFilename = Application.ActiveWorkbook.FullName & "\newwb.csv"

does not use just the path to the active workbook but the path and name of the active workbook appended with \newwb.csv

Answer 2

I have found the way to run the code smoothly, in courtesy of How do you run a .exe with parameters using vba's shell()? . Hope this will help anyone in need, and I would like to appreciate my gratitude to those who have tried to help. Thanks!!

Final Code:

Option Explicit
Sub CreateCSV()

'Declare the data type of the variables
Dim wks As Worksheet
Dim lastCol As Integer
Dim lastRow As Long
Dim iCol As Integer
Dim iRow As Long
Dim sFilename As String
Dim cellValue As String
Dim MyTextFile
Dim strProgramName As String

'Set wks to the current active worksheet
Set wks = ActiveWorkbook.ActiveSheet

'Set the location of the csv file to a variable
sFilename = Application.ActiveWorkbook.Path & "\testing.csv"

'Within the current active worksheet, identify the last interested row and column of data
'Any values such as 'a', '1' or '%' are considered as values. Spaces (Spacebars) are not considered as values.
With wks
    With .Cells(1, 1).CurrentRegion
        lastCol = .Columns.Count
        lastRow = .Rows.Count
    End With

    'Delete extra rows with blank spaces
    For iRow = 1 To lastRow
        For iCol = 1 To lastCol
            cellValue = wks.Cells(iRow, iCol)
        Next iCol

        If Trim(cellValue) = "" Then
            wks.Cells(iRow, iCol).EntireRow.Clear
            wks.Cells(iRow, iCol).EntireColumn.Clear
        End If
    Next iRow

   'Delete extra rows and columns with formats
    .Cells(lastRow + 1, 1).Resize(Rows.Count - lastRow, 1).EntireRow.Clear
    .Cells(1, lastCol + 1).Resize(1, Columns.Count - lastCol).EntireColumn.Clear
    .UsedRange.Select
End With

'Save as .CSV file in the specific location stated earlier
'If there are errors in the code when Users presses 'No' for the conversion, set wks to nothing and end the process
On Error GoTo err_handler
wks.SaveAs Filename:=sFilename, FileFormat:=xlCSV

'System to/not display alerts to notify Users that they are replacing an existing file.
Application.DisplayAlerts = True

'Notify users that the .CSV file has been saved
MsgBox sFilename & " saved"

'Opens the CSV file in a specifc location in Notepad
strProgramName = "C:\Windows\notepad.exe"
MyTextFile = Shell("""" & strProgramName & """ """ & sFilename & """", vbNormalFocus)

err_handler:
'Set Wks to its default value
Set wks = Nothing

End Sub