How to save new worksheet in the same dir the VB is being executed from I am hav

Question

How to save new worksheet in the same dir the VB is being executed from

I am having a problem when my VB get executed and creates the new workbooks, its saving in random places even through the XLSM is always in the same dir

how can I set this to always save to the same location in which the workbook that is executing the script is saved in.

the file name of the XLSX changes by revision number and the dir is not the same for all users so I guess I need the code to find the dir the workbook thatis executing the command is in, set it and then apply it to all saveAs files it runs

                End With
                GoTo Repeat1
            End If
        Else
            .Offset(i + 1, 8) = k
            WBNew.Sheets(1).Name = strConfigID
            WBNew.SaveAs strConfigID & " " & strCustName, AccessMode:=xlExclusive, ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges
            WBNew.Close (True)
            strConfigID = arrData(j, 18)
            strCustName = arrData(j, 3)

          
            GoTo NewWB
        End If
    Else
        .Offset(i + 1, 8) = k
        WBNew.Sheets(1).Name = strConfigID
        If Right(strCustName, 1) = "." Then

            strCustName = Left(strCustName, Len(strCustName) - 1)

        End If
      
        Columns().AutoFit
        WBNew.SaveAs strConfigID & " " & strCustName, AccessMode:=xlExclusive, ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges
        WBNew.Close (True)

Thanks for help !!

Explanation / Answer

Re: where to save

Add the following to the SaveAs method file name string...
   ThisWorkbook.Path & Application.PathSeparator

So it looks like...
   WBNew.SaveAs ThisWorkbook.Path & Application.PathSeparator & strConfigID & ...

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.